combobox DLookup (1 Viewer)

[bobbybeers]

OK, I've read how to do this...really I have. I have a combobox value that I want to use which then based on the combobox value shows the value of another field for that respective record. Should be very simple - note, the recordset for the form is not the same table as I want to get the value for (which makes this harder apperently). I've tried ALL the different suggestions on here on how to do this - I do get a result but its not right....what am I missing??

=DLookUp("[PS2]","[02_PRESS_SCH]","[PS_DESC]"=[PS_COMBO])

PS2 is the field name, 02_PRESS_SCH is the table, PS_DESC is the field that I want to match with my combobox value.

 

[pr2-eugin]

Hello bobbybeers, DLookUp only returns the First match and only that.. I think you want something like a Cascading ComboBox.. DLookUp is not the way..

 

[bobbybeers]

Actually I only want the first match....if it worked that would be great, but it just doesn't. one thing to mention, is that I'm putting the above statement in the text box's control source (as opposed to code). Doesn't work, I've tried all I can think of including
=DLookUp("[PS1]","02_PRESS_SCH","[PS_DESC] =" & [Forms]![CC_MAIN]![PS_COMBO])

obviously, PS_COMBO is the value of the combobox I'm trying to filter on.

 

[pr2-eugin]

What is the RowSource of the combo box? Also the bound column? Try the following code.

Private Sub PS_COMBO_AfterUpdate()
    Me.textBoxControlName =[URL="http://www.techonthenet.com/access/functions/advanced/nz.php"] Nz([/URL]DLookUp("[PS1]","02_PRESS_SCH","[PS_DESC] =" & Me.[PS_COMBO]),"")
End Sub

 

[bobbybeers]

A note to help communicate what I'm trying to do:
The combobox works fine - I want to take a value from it, use it to find a record on a table and then have a different text box give a value from a different field of that record.

I'm not sure what that code you gave does, but it looks like its assigning a value to my combobox which I don't want to do, I want to read the value only...

Another note....I seem to be able to get Dlookup to work when I manually give it a value, in this case "XS"

=DLookUp("[PS2]","02_PRESS_SCH","[PS_DESC] = 'XS'")

All I want to do is get it to read whats in my combobox instead of the "XS" I forced in above.

I've tried the following examples with no success:

=DLookUp("[PS2]","02_PRESS_SCH","[PS_DESC] =" & [Forms]![CC_MAIN]![PS_COMBO])

doesn't work

=DLookUp("[PS2]","02_PRESS_SCH","[PS_DESC] = [PS_COMBO]")
doesn't work either.....

 

[pr2-eugin]

That explains it.. You need to properly concatenate the Values to the DLookUp... The link would help you how to pass values.. to DLookUp..

Look to me like the ComboBox returns an Integer while the PS_DESC requires a String.. Either that or you need to properly pass string values to the DlookUp statement..

 

[pr2-eugin]

Could see that you have added more information after I have posted.. So...

What is the RowSource of the combo box? Also the bound column?

The code I gave was based on your previous reply..

...one thing to mention, is that I'm putting the above statement in the text box's control source (as opposed to code).

You have done it in the Control Source (literally using the Property sheet) What I provided was in Code that does the same.. If you notice, it would have mentioned..

Private Sub PS_COMBO_AfterUpdate()
    Me.[B][COLOR=Red]textBoxControlName[/COLOR][/B] = Nz(DLookUp("[PS1]","02_PRESS_SCH","[PS_DESC] =" & Me.[PS_COMBO]),"")
End Sub

Where textBoxControlName is the name of the text box you are trying to assign the value to..

If you could re-read post 6, it might help you understand what the problem is..

 

[bobbybeers]

Thank you, Thank you, Thank you....
I didn't use any information from your last post - just followed the link in the previous post to the reference you provided and found out that I had the syntax wrong..... the ' was the problem. thanks again!!