Using DLookup on a subform

[alexwood]

Any help with this would be greatly appreciated.
I have a form, CALLS, and within this is a subform, CUST. When I enter the customers account number, in the corresponding field of the CUST subform, the DLookup is performed, and if that customer is an existing one, the remaining fields on the CUST subform are populated.

If I open the CUST subform on its own, this works perfectly well. However, when I open the CALLS form, and try to use the CUST form in the manner described above, I get a run-time error.

I'm pretty sure that what I want to do is not impossible, and the solution is a fairly easy one, but I'm stuck, and I can't find it !

Many thanks in advance.

 

[JerryDennison]

You must include the full path in the DLookUp() function.

=DLookUp("[FieldName]", "RecordsetName", "[CriteriaField] = " & Forms!MainFormName!SubFormName!ControlName)

But there is a much easier way! In the subform's recordsource add the other table. Link the two(if they didn't automatically) between the appropriate foreign and primary keys. Double click the join line and change it to include all records from the primary table and only those matching from the lookup table. Place the '*' of the primary table on the QBE grid and then place those fields you want displayed when the user pics a choice from the combobox on the QBE grid. Close and save. These fields(the one(s) you were using the DLookUp to find) will now be in your field list. Change the control source of the dlookup control(s) to the new fields. Just make sure you set these control's enabled property to no and locked property to yes(you don't want the user changing these values here in an uncontrolled manner).

Now whenever your user selects a value from the list in the combobox all the pertinent info about that selection will display automatically and FAR faster than using DLookUp.

 

[alexwood]

Thanks for the inf Jerry.
Unfortunately, the first one doesn't work. I get an error message, albeit a different one. This time it is saying it cannot find the form CUST referred to in the VB code.

The second one in not quite what I want, if I have read it correctly. The account numbers are 11 digits long, and there will be anything upto 300 of them.

Thanks for the help though, I'll just have to keep tinkering with it, see if I can stumble across the solution.