DLookupPlus - Microsoft Access Forum - Computer Learning Zone


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

DLookupPlus

Brian Puppa

3 years ago

I am trying to follow along with the MS Access Groups video and the extended cut video. I have a very similar database to the example in which I want to display a list of interests separated by a comma for each customer on a form.

I think I have all the functioning pieces such as the query which calls on the junction table, etc. I get to the 18:47 time of the extended video and I follow along to enter the =DLookupPlus function into the text box, but I get lost how to hook that into the actual DLookupPlus visual basic code from the code vault.

I looked at the Intro to VBA video for help but it talks about right clicking that text box to Build Event and select Code Builder. If I do that the window it brings up is listed under Microsoft Access Class Objects in the VBA window not under Modules as it is in Richard's sample template. If I right click in the VBA window and insert Module and paste the DLookupPlus code there, it looks more like Richard's sample. But if I open the form, I get Compile Error user-defined type not defined and it is highlighting "db As Database" from the DLookupPlus code. So I am just lost as to how to get it all working together. Any help pointing me in the right direction would be appreciated.

Kevin Robertson @Reply

3 years ago

At the bottom of the DLookupPlus function tin the Code Vault Richard states the correct syntax. The syntax is the same as the standard DLookup function.

User-defined type not defined probably means you have a missing reference.
Since db As Database is highlighted this immediately tells me what reference is missing.

Go to Tools -- References
Add this reference

Microsoft Office 16.0 Access Database Engine Object Library

Brian Puppa OP @Reply

3 years ago

Thanks Kevin! Adding the reference did get rid of the Compile Error. I seems like DLookupPlus is running but all it places in my form is "Error" which is what the VBA code for DLookupPlus says to put in the field if it hits DLookupPlusErr: in the code. I think the syntax of my DLookupPlus call on the form is okay. It looks like this:
=DLookupPlus("Type2","QueryAddrBk2GTBType","ContactID=" & [ContactID],"Type2")
Any thoughts where my hang-up might be now?
I can get a regular =DLookUp("Type2","QueryAddrBk2GTBType","ContactID=75578") to work and it displays the single value for Type2 associated with contact 75578.

	Kevin Robertson @Reply 3 years ago
In the Errorhandler (DLookupPlusErr) of the function temporarily change to: `DLookupPlus = Err.Number & ": " & Err.Description` This will give you the error number and the description of the error. Also, where are you placing the call to the function?

	Brian Puppa OP @Reply 3 years ago
So now instead of "Error" it says "13: Type mismatch" I have a text box in my Customer form where I placed =DLookupPlus("Type2","QueryAddrBk2GTBType","ContactID=" & [ContactID],"Type2")

	Kevin Robertson @Reply 3 years ago
Is ContactID a number in the table, or Short Text?

	Brian Puppa OP @Reply 3 years ago
AutoNumber Long Integer

	Kevin Robertson @Reply 3 years ago
If the function is in a text box on the Customer form where is the ContactID coming from? Is it in the table? Is it being looked up? Or should the criteria be CustomerID?

	Brian Puppa OP @Reply 3 years ago
ContactID is stored in the Customer Table which is pulled into the junction table which is then linked to the Customer Type Table by the query QueryAddrBk2GTBType -- looks like Richard's example structure. Not sure what you mean "should the criteria be Customer/ContactID?"

	Brian Puppa OP @Reply 3 years ago
Good news Kevin. I got it working. I suspected something corrupted along the way, so I started with a fresh database and copied over much of what I had done. Once I put it all together the DLookupPlus function was working perfectly. Thanks again for your help with this! Much appreciated.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View