Filter dropdown of subform based on field in parent form.

[wchelly]

I want to filter the selection options of a dropdown [Serial_Number], based on the item type [container] in the parent form. How do I go about doing this?

 

[pr2-eugin]

Is Serial_Number in SubForm and Container in Parent Form?? Could you explain your situation a bit more??

 

[wchelly]

Serial_No is in the subform
Container_Type is actually in another subform inside the same mainform.
Both have foreign key of ShipperID.

Shipment_Table (Lists each shipment by ID and Date)
ShipperID
Shipment_Date

Material_DescriptionTBL (Lists number of containers and Container on each shipment)
Truck_Number
Trailer_Number
Material_Type
No_of_containers
Container_Type

Container_TBL (This table lists all the containers by serial number and Container Type)
Serial_Number
Container_Type

TrackPacages_TBL (Table lists which containers are on which truck and shipment)
ShipperID
Serial_No (This is the field for which I need the filtered drop-down. Essentially we have about 1500 containers and my thinking is that if I can filter it by container type, then our drop-down items would be significantly reduced)

 

[wchelly]

Really, at this point I am asking more generically "Is it possible to filter a dropdown menu based on another related or parent open form?"

 

[Isskint]

Yes


Sorry, seriously, there are seceral ways to do this. Perhaps the best is to add the WHERE criteria to the combobox controlSource referencing the [container] field.

If Container is a text field, something like:-
SELECT X,Y,container,Z etc FROM ABC WHERE ((container='" & Forms!ParentForm.[Serial_Number] & "'")


If Container is a number field, something like:-
SELECT X,Y,container,Z etc FROM ABC WHERE ((container=" & Forms!ParentForm.[Serial_Number])

You may need to add some code to requery the combobox in the [Serial_Number]'s AfterUpdate event

 

[Severin]

Here's a quick example... and there's some other samples on my website!~)

Just remember if you want to reference a form from the vba editor, then the form has to have the form property 'Has Module' = True

 

[duthiedon]

The solution in the database was great! Helped solve my problem! Thanks for contributing.