(newbie) Using a dlookup in a datasheet view form

[Nyanko]

Hi,

I appreciate this may be a silly question, but I'm tying myself in knots and can't believe something I'm trying to do is turning out to be so difficult.

I have a main/sub form set up where the sub form is set to show a datasheet view of associate records. I have a combo box that looks up a Product Code from a separate table:

SELECT [lkp_ProductList].[ID], [lkp_ProductList].[ProductCode] 
FROM lkp_ProductList 
ORDER BY [ProductCode];

This works perfectly.

I am looking to bring through the ProductName in a new field when the combo box is changed - just so the user knows that have chosen the correct value. I have tried using a dlookup in the expression builder but get errors. I'm guessing that I have some syntax wrong, but I'm going in circles now ..

=DLookUp("[ProductName]","lkp_ProductList","[ProductCode]=[cmb_ProductCode]")

My desired result would look like

ProductCode..ProductName
AAAAAAAAA..ProductA
BBBBBBBBB..ProductB
AAAAAAAAA..ProductA
CCCCCCCCC..ProductC

Please can you put me out of my mystery :banghead:

 

[JHB]

What is the error you get, number and description?
Try the below:

=DLookUp("[ProductName]","lkp_ProductList","[ProductCode][B][COLOR=Red]"[/COLOR][/B]=[cmb_ProductCode])

 

[Nyanko]

I have updated this and there is nothing ... no errors no values. When I change the drop down nothing happens.
When I look in design view the field has a green triangle error that states :
"Invalid Control Source : Control Property"
Circular Reference

Surely this can't be this difficult ?

I wondered if it was getting confused between the names of the table and the names on the form (?) so I updated to

=DLookUp("[lkp_ProductList]![ProductName]","lkp_ProductList","[lkp_ProductList]![ProductCode]"=[cmb_ProductCode])

Now the green arrow errors are gone, but the result is still blank no matter what code I choose from cmb_ProductCode

 

[JHB]

..
I wondered if it was getting confused between the names of the table and the names on the form (?) so I updated to

No that is not the fault, DLookup function has the table name as second parameter!
What is the field type for [ProductCode]?
If it is text then you need single quotes:

=DLookUp("[ProductName]","lkp_ProductList","[ProductCode][B][COLOR=Red]'"[/COLOR][/B]=[cmb_ProductCode] & [B][COLOR=Red]"'"[/COLOR][/B][COLOR=Red][COLOR=Black])[/COLOR][/COLOR][B][COLOR=Red] [/COLOR][/B]

Else post your database with some sample data, (zip it) + name of the form where you've the problem.

 

[Nyanko]

Hi,

Thanks for your assistance with this. I'm still not able to get it to work and I can't work out why. I have made a very very basic database but still can't get this to work !!

Hopefully it will be fairly obvious what I'm trying to do. In brief, I just want a field on a form to return the project name when I select a code via a combo box. This field doesn't need to be saved to the table and is more for input validation by the users, so they know they have the correct field and for visual checking - but I don't want to create a 2 column combo box.

Help would be very much appreciated !

 

[arnelgp]

your have Productcode in your criteria not ProjectCode. try this

 

[Nyanko]

Thank you for this, it has really helped ! I've been able to reverse engineer the formula and apply it to other situations

Thanks for taking the time to help