There has got to be a simple(r) way to do this.
I have a situation where I am listing parts using continuous forms. The parts listing has things like the part type, manufacturer, part number and the quantity. The list is of the part instances & quantity. I can look the other things up from the instance of the part, so displaying existing parts is no problem, just use a joined query to retrieve the related records or using a DLookup to pull the fields individually.
Type Manufacturer Number Qty
-------------------------------------
Widget Acme 123ab 1
Widget Acme 456 1
Anvil Xyz 12345 2
Llama Northwind 322-12A 1
...
The problem is where the users need to add new parts. There are thousands of different parts, so they can't really choose from a single part number combobox. They want to be able select a part type, manufacturer, and then the part number from three different comboboxes that successively narrow the results in each combobox.
Select a part type >> select a manufacturer who makes one of those types >> select part number of parts of that type by that manufacturer >> enter quantity
Ok, so I can use 2 unbound comboboxes with a DLookup as default, and link them via their Row Source queries. The final combobox is part number with its Row Source filtered - so it only lists the parts from selected type and manufacturer. I have done that before with this kind of situation and it works well.
However I am trying to do this with continuous forms in Access and it is returning the same result for the Dlookup on each record. E.g.
Type Manufacturer Number Qty
-------------------------------------
Widget Acme 123ab 1
Widget Acme 456 1
Widget Acme 12345 2
Widget Acme 322-12A 1
...
Argh, what's going on? My Dlookups seem to be only using the value from the first record. Why?
Is this even a good way to do this? Is there a better pattern to use than drill-down comboboxes on the continuous form? The only other way I can think of to get around this is to use the comboboxes on a "add/edit part" type form where the details are selected in a dialog and then the part number is populated from that. But that seems even more kludgy.
