Dlookup - 2 2

[BobChesh]

Folks,
Having problems with the Dlookup function. I have viewed FAQ 705-4978, but can't make it work.

I have a form where there are two combo boxes which select two "Reasons for Failure", combo1 being a Primary Cause and the combo2 being a Secondary Cause. I want to select the Primary Cause in the first combo and then in the second combo only to select those Secondary Causes that are related to the Primary. I then want to hold this data in a data table for later analysis. I have two tables PrimaryFailureReasons and SecondaryFailureReasons. The Primary holds just the Primary Cause, while the Secondary holds both Primary and Secondary. So, for instance, in the PrimaryFailureReasons table there is "Customer Caused" whilst in the SecondaryFailureReasons table there is "Customer Caused" (Primary field) and then Secondary Cause field reasons like "Not In", "No longer Required", "Cancelled Earlier" etc etc.

I am using the Dlookup function =DLookUp("[Secondary]","[SecondaryFailureReasons]","[Primary]=forms!Frm_Data![PrimaryReason]") in the Row Source line of combo2, but all I get is a blank. I can make it work using a query, but since this is going to used by several users, I felt that Dlookup is more dynamic and does not lock the data.

 

[Remou]

I think you would be better off with this faq702-4289.

 

[ck1999]

try in the click event of 1st combo1 to place a combo2.requery

Then instead of using dlookup, when you add the combobox use the navigation to select items to place in combobox. Then when finished open up rowsource query and add the primary reason field make it not visible then have the criteria = combo1.value

ck1999