Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question

3 Answers

up vote 0 down vote accepted 
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.

share|improve this answer
up vote 0 down vote

Consider this example copied from an Immediate window session.

localModID = 4
? DLookup("some_text", "tblFoo", "id = " & [localModID])
' that triggered, "Compile error: External name not defined"

' but it works without brackets around the variable name
? DLookup("some_text", "tblFoo", "id = " & localModID)
bar

So change your DLookup to this ...

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

I think it will work or at least you may get a different error message.

In a comment, you showed a working DLookup which indicates your ModID field is text (not numeric) data type. In that case you must add quotes around localModID when you build the DLookup in that code.

=DLookUp("Threshold","P_Review","ModID ='" & localModID & "'")
share|improve this answer
Without the brackets I get an error message that says, "Syntax Error (missing operator) in query expression 'ModID ='. I believe this is because the expression cannot find the variable without the brackets. This works though DLookUp("Threshold","P_Review","ModID = 'MOD104'") So I believe it has something to do with the variable type. Should I define localModID in a special way? – RBSt Apr 4 at 18:07
But you already know it doesn't work with the brackets. You need to determine why the variable is not found at run time when the DLookup is evaluated. From break (debug) mode, see what these 2 statements give you in the Immediate window: Debug.Print "'" & localModID & "'" and Debug.Print TypeName(localModID) – HansUp Apr 4 at 18:14
Based on your revised comment, it seems ModID is text. I updated the answer to deal with that. – HansUp Apr 4 at 18:19
I changed it to what you said and now it has a #Name? error. This is what the two statements you gave me returned in the immediate window. localModID = MOD104 Debug.Print "'" & localModID & "'" '' Debug.Print TypeName(localModID) Empty – RBSt Apr 4 at 18:59
1  
Yes I was assuming this was the name of a textbox (probably with visible=False) on your form. If you are using your DLOOKUP as the control source for another textbox on your form then it's going to have to have access to that localModID control and I don't think a variable from VBA will even work. You could perhaps make a VBA function that returned that value and then embed MyFunction() in your dlookup, but anyhow if the Me!localModID = Me.selectModID.Column(0) isn't generating an error then it seems like there is a control somewhere you aren't seeing. – Dan Metheus Apr 5 at 0:47
show 7 more comments
up vote 0 down vote

Try 

Forms!<whatever_this_form_name_is>![localModID]

in your DLOOKUP

share|improve this answer
Sorry. I forgot to mention it in my question, but I tried this and it returns the same error message. – RBSt Apr 4 at 15:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.