Query Builder Filter

[maax555]

Hi All, I have a simple for where I want part number combo box to filter and only show part numbers which belong to the customer combo box,
The part numbers have already been added against customers in table PartNumber.
I was trying to achieve this in the criteria section but no luck.



I have attached the db as the two small images I tried to upload (56kb in total) were generating an error in character length allowed.

So the form EngineeringTable has the combo box part number which I wish to restrict to only part numbers associated with the customer combo box.

Customer and partnumber data are in the PartNumber table.

Many thanks

[moke123]

Google cascading combo boxes.

Make sure your parts combo has the customers field in it's recordsource and set the customer combo as the criteria for that field. Do a requery in the after update of the customer combo

[mike60smart]

Hi

As mentioned you need to use Cascading Combobox's.

To be able to use Cascading correctly you first need to get rid of all the Lookup Fields in your tables.

Google "The Evil's of Lookupp Fields in Access Tables" for an explanation as to why you should not use them.

[maax555]

Google cascading combo boxes.

Make sure your parts combo has the customers field in it's recordsource and set the customer combo as the criteria for that field. Do a requery in the after update of the customer combo

Many thanks for your reply. However I am not able to make this work.

Its been many years since I used Access and I am struggling more than I anticipated.

thanks

EDIT. my screen had not refreshed so I did not see mike's post. I will check this out.

EDIT 2. The googled search takes me https://access.mvps.org/access/lookupfields.htm this is not reachable.

thanks

[moke123]

here ya go.

[maax555]

here ya go.

Thank you, I had something similar but not close enough.

[maax555]

[QUOTE=mike60smart;521570]Hi

As mentioned you need to use Cascading Combobox's.

To be able to use Cascading correctly you first need to get rid of all the Lookup Fields in your tables.


Am I correct in thinking that instead of the lookups in tables I would use a combobox on the form?
This is the way I kind of remember doing in in the past. The lookup wizard in the tables is something I had not seen before so thought this must be the way to go now.

[mike60smart]

Hi
The field in the table is a Number data type and you would remove the Lookup.

And, Yes you would create a Combobox on the Form and then store the ID value vice the text.

[Micron]

Lookups in tables are just a way to avoid having to make the necessary and properly related tables. They also support the ability for users to muck about in the tables directly, which is another thing that is generally considered to be bad practice. While you might have combos on a form, they're not used in place of table lookup fields. They're used in conjunction with properly designed db schema and that means having the right tables with the right fields for the job.

[moke123]

The lookup wizard in the tables is something I had not seen before so thought this must be the way to go now.

There are a few of those gems (snark) that make it easy to do one thing, but it complicates 10 others.