Drop Down Data Entry Fields

[Beretta22]

I'm trying to set up a data entry screen with two drop down box fields. Available drop down choices in the second drop down box would be dependent upon what my choice is/was in the first box (i.e. is I choose Item A in Field 1, choices B, C, D & F show up in Field 2; if I choose Item W in Field 1, choices B, H, I, & J show up in Field 2)

Can this be done, and if so, how?

 

[Trainingjason]

It is fairly straight forward, create a table with the data held
When the form loads type

me refers to current form
cboBBG is the first combo box
tblBusinessDepartment is the table with the data

Private Sub Form_Load()
Me.cboBBG.RowSource = "Select distinct strBBG from tblbusinessdepartments where strBBG is not null"
End Sub

Add the code to the after update query of the first combo box

cboBusDeptID is the 2nd combo box
strBusinessDeptment is the field to be that will be shown on the 2nd combo, based on the lookup table
the where is the condition from the first combo being used as the basis for the 2nd combo

Private Sub cboBBG_AfterUpdate()
Me.cboBusDeptID.RowSource = "SELECT DISTINCT strBusinessDepartments FROM " & _
"tblBusinessDepartments " & _
"WHERE strbbg = " & Chr(34) & Me.cboBBG & Chr(34)

End Sub


You will probably need to add code to the form current method to reset the drops down.

Hope this helps


Jason

 

[Beretta22]

Thanks, I'll give it a shot and see what happens!