Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using DLookup on a subform

Using DLookup on a subform

Using DLookup on a subform

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.

RE: Using DLookup on a subform

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.

RE: Using DLookup on a subform

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close