Auto Populate Access Form using simple VBA code by setting a variable

Question

I was recently given the task of creating a form that will autofill with the information from a table. The information the form autofills is selected using a primary key called ModID. I have a combo box that has a List of the ModIDs that are listed as Active.

SELECT ModID
FROM P_Review
WHERE Status = "Active"

Simple enough. I then have VBA code running on the event After Update. So after the value for the combo box is select or changed it will run this VBA code.

Option Compare Database
Option Explicit

Private Sub selectModID_AfterUpdate()
    'Find the record that matches the control.
On Error GoTo ProcError
    Dim rs As Object
    Set rs = Me.RecordsetClone
    With rs
            .FindFirst "ModID=" & Me.selectModID
            If Not .NoMatch Then
                Me.Bookmark = .Bookmark
            Else
    DoCmd.RunCommand acCmdRecordsGoToNew
    Me!localModID = Me.selectModID.Column(0)

            End If
    End With

ExitProc:
    Exit Sub
ProcError:
    MsgBox "Error: " & Err.Number & ". " & Err.Description
    Resume ExitProc

End Sub

The code runs fine (I get no errors when I debug or run).

Now for the access text box. I would like to populate certain fields based off the variable localModID. I have a dlookup in a text box to find the information in the table P_Review.

=DLookUp("Threshold","P_Review","ModID =" & [localModID])

So the DlookUp should find the value for the column threshold, in the table P_Review, where the ModID in P_Review equals the localModID set in the VBA code. But when I go to form view and select a ModID I get the Error 3070: The Microsoft Access database engine does not recognize as a valid field name or expression. I did copy this code from another database we are already using but it fails in this new instance.

RBSt RBSt · Accepted Answer · 2013-04-10T14:12:40

Private Sub ModID_AfterUpdate()
    Dim rs As Object

   Set rs = Me.RecordsetClone
   With rs
      .FindFirst "ModID='" & Me.ModID & "'"
   If Not .NoMatch Then
      Me.Bookmark = .Bookmark

   Else
      DoCmd.GoToRecord , , acNewRec
      Me!ModID = Me.ModID
   End If
   End With

End Sub

This is the answer to question. I used this code to auto update.

Auto Populate Access Form using simple VBA code by setting a variable

2 Answers