How to use lookup query to return only entries matching the related field of active record

5 pts.
Tags:
Microsoft Access
Microsoft Access 2007
Microsoft Access queries
I'm creating a lookup field and would like to list only those items relevant to the record actively using the lookup. So my DB has two tables one using the lookup which as a child ID field that I'd like to match with the parent ID field of another table.

Answer Wiki

Thanks. We'll let you know when a new response is added.

This one shouldn’t be that difficult.

I’ll assume you want to use 2 combo boxes, with the first selecting a category and the 2nd the lookup value. (You can’t do this in 1 combo with multiple fields – at least not without some fancy VBA code).

Ok, you have combo1 and combo2. Place them on a form

In combo1 – set the Row Source value to the query from the first table: “Select fieldname from tablename, order by whatever”

In combo2 – set the Row Source value to a new query that references the first, using this syntax: “Select lookupfieldname from lookupchildtable where lookupparentid = ” & [forms]![yourformname]![combo1]

create an afterupdate event for combo1. Choose code builder

Your code should look like this

Private Sub Combo1_AfterUpdate()
me.combo2.requery
End Sub

That’s it. Now, whenever you change a value in combo1, only the child values relating to that category will show in combo2.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following