Compile Error: Sub or Function Not Defined

[CapriPHR]

Hello all.

I am trying to use DLookup to auto-populate fields in an otherwise simple database. I am using Access 2010. I have created three different databases and the code still does not work. I think there may be glitches in this particular copy of the program. Please evaluate the details below and tell me what you think.

Table 1 has fields: Product ID, CustomerName, CustomerAddr, and ComplaintLevel.

Table 2 has fields: Resolution ID, CustomerName, CustomerAddr, Product ID, ComplaintLevel, ResolutionDate.

I want DLookup to populate Product ID field of Table 2 once CustomerName has been selected. CustomerName options are provided via a combo box that pulls from Table 1.

Here is my code:

Private Sub CustomerName_AfterUpdate()
Product ID = DLookup("Product ID", "Table 1", "CustomerName=" & CustomerName)
End Sub

I get a Compile Error. What do you suppose is at issue here?

[Stewart Ross]

Never blame glitches on the program itself; although there are known bugs with Access, you have to rule out errors you have introduced first.

Firstly, if you want to assign values to a control called Product ID you must enclose the reference to it in brackets, [like this].

Secondly, if you are referring to a string literal value within a where clause (the third parameter supplied to the DLookup), you must enclose the value supplied in single quotes, like this:

Me![Product ID] = DLookup("Product ID", "Table 1", "CustomerName = '" & Me!CustomerName & "'")

The compile error you mention resulted from the incorrect reference to the field Product ID. VBA would interpret this as a variable called Product followed by another called ID with no operator between them.

The lack of the single quotes in the Where clause would cause a run-time error when Access tries to interpret the text in the customer name as some kind of missing variable instead of passing the string to the DLookup.

As I mentioned, don't blame the program until you rule your own actions out first!

-Stewart

[CapriPHR]

Thank you for the feedback, Stewart. I wrote the code that way because I was instructed by other resources to use that format. Writing the code as you have indicated did not fix the problem. I didn't get another error message, but nothing happened at all once I selected CustomerName.

I adjusted the formatting of fields and VB code as follows and now it returns a value for ProductID, but not the one associated with the specified CustomerName:

Private Sub CustomerName_AfterUpdate()
ProductID = DLookup("ProductID", "Table1", "CustomerName = '" & Me!CustomerName & "'")
End Sub

My previous resources did not stress the importance of quotation marks, so thanks for that feedback!

[CapriPHR]

Here is the VB code that finally worked:

Private Sub CustomerName_AfterUpdate()
Me![ProductID] = DLookup("ProductID", "Table1", "CustomerName = '" & Me!CustomerName & "'")
Me![CustomerAddr] = DLookup("CustomerAddr", "Table1", "CustomerName = '" & Me!CustomerName & "'")
Me![ComplaintLevel] = DLookup("ComplaintLevel", "Table1", "CustomerName = '" & Me!CustomerName & "'")
End Sub

Thanks again for your feedback!