By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,442 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Compile Error: Sub or Function Not Defined

P: 3
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub CustomerName_AfterUpdate()
  2. Product ID = DLookup("Product ID", "Table 1", "CustomerName=" & CustomerName)
  3. End Sub
I get a Compile Error. What do you suppose is at issue here?
Jan 5 '12 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. 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!

Jan 5 '12 #2

P: 3
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub CustomerName_AfterUpdate()
  2. ProductID = DLookup("ProductID", "Table1", "CustomerName = '" & Me!CustomerName & "'")
  3. End Sub
My previous resources did not stress the importance of quotation marks, so thanks for that feedback!
Jan 5 '12 #3

P: 3
Here is the VB code that finally worked:
Expand|Select|Wrap|Line Numbers
  1. Private Sub CustomerName_AfterUpdate()
  2. Me![ProductID] = DLookup("ProductID", "Table1", "CustomerName = '" & Me!CustomerName & "'")
  3. Me![CustomerAddr] = DLookup("CustomerAddr", "Table1", "CustomerName = '" & Me!CustomerName & "'")
  4. Me![ComplaintLevel] = DLookup("ComplaintLevel", "Table1", "CustomerName = '" & Me!CustomerName & "'")
  5. End Sub
Thanks again for your feedback!
Jan 5 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.