Drilldown using unbound comboboxes with Access "Continuous Form"

Question

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.

Answer 1

Using unbound controls on a continuous form will only drive you demented, because an unbound control can only refer to the selected record, which could be any of them. I think the pop-up form for adding new records would be quite nice, and easy for the user to understand.