DLookUp on a Continuous Form (1 Viewer)

[shawnacker]

I have searched for days and not been able to find an answer to a supposedly easy question.

I have a continuous form "Entry_Form" based off a table called "Today". I want to add additional criteria to the table using the form and have created a combo box that is based on a separate table called "Reason_Codes". "Reason Codes" has two fields "ID" and "Description".

When selecting the combo box "ID_Selector" I populate one of the fields on the form which then populates the corresponding field on the table. This works.

My problem is that I want to have a text box on the form called "Description_Code" populated automatically the "Description" based the ID chosen.

I have created the following DLookUp equation and placed it into the Control Source, but nothing is populated in the "Description_Code" field.

=DLookUp("[Description]","[Reason_Codes]","[ID] = Forms![Entry_Form]!ID_Selector")

Needless to say, I have tried dozens of varieties, but to no avail. Is DLookUp the proper function? What am I doing wrong?

Any help would be greatly appreciated.

Thank you,

Shawn

 

[ShaneMan]

Hey Shawn,

Assuming ID is a number, give this a try:

=DLookUp("[Description]","[Reason_Codes]","[ID] =" & Forms![Entry_Form]![ID_Selector])

If ID, is the first column in the combo box then you should be ok but if it's not then you would need to try it like this:

=DLookUp("[Description]","[Reason_Codes]","[ID] =" & Forms![Entry_Form]![ID_Selector].Column(WhateverColumnNumberIs))

Access starts counting columns at 0 so, for example, the third column from the left is not 3 it's 2.

HTH,
Shane

 

[Rich]

You don't need DLookup, just display the second column of your combo box, or use an unbound textbox to display its value

 

[coolpra]

Hi Shane,

I also have issue of this DLOOKUP....

First Table - Inventory
Second Table - DO (Delivery Order)

I used a continuous form to get multiple items to DO....Form has the Combo to get item_ID and for Description I use DLOOKUP....

The Lookup working and correctly display the data but not saving in the DO table....

Now I'm screwed up searching how to fix this?

Hope you can help me....

Cheers
CoolPra

 

[namliam]

Lookup values, or relational values SHOULD NOT be stored duplicate in related tables.
Simply store the key of the relation and lookup the description on demand.

 

[coolpra]

Lookup values, or relational values SHOULD NOT be stored duplicate in related tables.
Simply store the key of the relation and lookup the description on demand.

Thanks namliam...You idea is great....I used that way...
make relationship between two tables and then use query to get the data what I want...

Thanks
Cheers,
CoolPra