Form Filter - How to reference a DLookUp field

[blubtt]

I have a form where the 'Record Source' is a table. I have several fields in the table that have foreign key references that utilize the Lookup Combo Box display control to allow users to select from a drop down list in the form.

The issue I am having is that I can't filter the text in the foreign key fields (only the ID's ).

My attempted solution was to create a control on the form (text box) and bind it to each of the foreign key ID's using the DLookUp function and then reference this control in the filter code.

My question is... how do I reference this DLookUp textbox in my filter VbCode?

I have attached a '.jpg' image of various aspects of the form including the filter code on the 'On Change' event.

Thanks in advance...

Brad

JPG Image

 

[PHV]

a form where the 'Record Source' is a table
Why not use a query with all the foreign tables joined ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[blubtt]

PHV -- I would but then I would loose the ability to edit the records in the Form.

 

[dhookom]

Why would you lose the ability to edit? The joins should all be on primary/foreign keys which will make sure the query allows edits.

Duane
Hook'D on Access
MS Access MVP

 

[blubtt]

I had created in my mind an assumption (based on some false testing) that query backed forms don't carry the ability to edit the original source tables.

I tested again and all is well in my world now...

It is interesting how we can run down a rabbit trail for hours... all because our assumptions are wrong.

Thanks for your help!

brad