Sweep using unrelated comboboxes with Access Continuous Form

Question

There should be a simple (r) way to do this.

I have a situation where I list details using continuous forms. There are things in the part listing such as part type, manufacturer, part number and quantity. The list contains instances and the number of parts. I can look at other things from an instance of a part, so displaying existing parts is not a problem, just use a combined query to retrieve related records or using DLookup to pull 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 that users need to add new parts. There are thousands of different parts, so they cannot select one of the drop-down lists of numbers. They want to be able to choose the type of part, manufacturer, and then the part number from three different combobox that sequentially narrow down the results in each combo box.

Select a part type → select a manufacturer that makes one of these types → select a part number for this type by manufacturer → enter quantity

Ok, so I can use 2 unlinked comboboxes with DLookup by default and link them using Row Source queries. The final combobox is a part number with a Row Source filter, so it only lists parts from the selected type and manufacturer. I have done this before with this situation, and it works well.

However, I try to do this using continuous forms in Access and return the same result for Dlookup for each entry. For instance.

 Type Manufacturer Number Qty ------------------------------------- Widget Acme 123ab 1 Widget Acme 456 1 Widget Acme 12345 2 Widget Acme 322-12A 1 ... 

Arg, what's going on? My Dlookups seem to use only the value from the first record. What for?

Is this even a good way to do this? Is there a better pattern to use than cut-out combined cells in continuous form? The only way I can get around this is to use comboboxes in an add / edit form where the parts will be selected in the dialog box and then the part number is populated from this. But that seems even more stupid.

Answer 1

Using unrelated controls in a continuous manner will horrify you because an unrelated control can only reference a selected record, which can be any of them. I think the pop-up form for adding new entries will be quite enjoyable and easy for the user to understand.