Filter Form w/Text Combo Box

Question

I have a table with several fields, one is a lookup filed (defined as short text) looking up values from another table.

I have a form that has the above mentioned table as a Record Source. I want to filer this form by the data in the mentiond lookup field. I have created a combo box on the form to filter this data and in the Record SOurce have added this control to the Criteria of the Record Source Query. 

But When I try to filter by the lookup column I get nothing. Does the lookup column state the actual text string that is displayed and does the lookup values in the table display the txt string or only a reference value to the original table?

If I have CustomerID  CustomerName

And on the original table I have a lookup to Customer Name

and on the form I want to filter by Customer Name

Are all these values stored as text so I can filter by text? What am I doing wrong here?

Answer 1

Hi,

First of all, it is highly recommended you avoid using lookup fields at the table level. Instead, you can use comboboxes in your forms to display a lookup value.

With reference to your questions, it all depends on how you set up the lookup field in your table. If you used the Wizard, it more than likely stores the ID value but displays the Text value you're looking up. If so, then the criteria should match the numeric value for the query to return any result.

Just my 2 cents...

Answer 2

You'll find an example in FindRecord.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the option to 'Open form to filter by a foreign key' opens a form which includes an unbound combo box in the form header, in which a contact can be selected by name.  The value of the combo box is a numeric value in the hidden first column of the combo box's RowSource query.  The column is hidden by setting the control's ColumnWidths property to 0cm, and its ColumnCount property to 2.  The form is then filtered on the numeric ContactID foreign key column in the combo box's AfterUpdate event procedure.  In my case the column was set up manually in this way in the table design, but yours will almost certainly been set up in the same way by the wizard.

---

Ken Sheridan, Stafford, England