Dynamic Search Combobox

[MichaelTran]

Hello All,

Please help. I have three independent combo search boxes, cboLotno,
cboPartNo and cbosupplier. The table name is tbl_Ledger_data and Subform name is tbl_Ledger_Data_Subform

What I need to change is when a part number selected from the cboPartNo.
1) The cbosupplier to only displays the supplier names that related/made to the searched part number from cbopartno
2) The user then can select supplier names from the cbosupplier
3) The subform will displays only supplier names selected from cbosupplier.

Here is the codes:
Private Sub cboLotNo_AfterUpdate()
Dim myLotNoSel As String
Me.lblStatus.Caption = ""
Me.lblStatus.Visible = False
'myLotNoSel = "Select * from tbl_Ledger_Data where [LotNumber] = '" & Me.cboLotNo & "'"
myLotNoSel = "Select * from tbl_Ledger_Data where [LotNumber] = '" & Me.cboLotNo & "' order by [CompDate] desc"
Me.tbl_Ledger_Data_subform.Form.RecordSource = myLotNoSel
Me.tbl_Ledger_Data_subform.Form.Requery
Me.cboPartNo = Null
Me.cboSupplier = Null
Me.lblStatus.Caption = "Search Results and Recommendations:"
Me.lblStatus.Visible = True

End Sub

Private Sub cboPartNo_AfterUpdate()
Dim myPartNoSel As String
Me.lblStatus.Visible = False
'myPartNoSel = "Select * from tbl_Ledger_Data where ([PartNo] = '" & Me.cboPartNo & "')"
myPartNoSel = "Select * from tbl_Ledger_Data where [PartNo] = '" & Me.cboPartNo & "'order by [CompDate] desc"
Me.tbl_Ledger_Data_subform.Form.RecordSource = myPartNoSel
Me.tbl_Ledger_Data_subform.Form.Requery
Me.cboLotNo = Null
Me.cboSupplier = Null
Me.lblStatus.Caption = "Search Results and Recommendations:"
Me.lblStatus.Visible = True

End Sub

Private Sub cboSupplier_AfterUpdate()
Dim mySupplierSel As String
Me.lblStatus.Visible = False
'mySupplierSel = "Select * from tbl_Ledger_Data where ([Manufactures] = '" & Me.cboSupplier & "')"
mySupplierSel = "Select * from tbl_Ledger_Data where [Manufactures] = '" & Me.cboSupplier & "'order by [CompDate] desc"
Me.tbl_Ledger_Data_subform.Form.RecordSource = mySupplierSel
Me.tbl_Ledger_Data_subform.Form.Requery
Me.cboLotNo = Null
Me.cboPartNo = Null
Me.lblStatus.Caption = "Search Results and Recommendatons:"
Me.lblStatus.Visible = True
End Sub

Private Sub cmdClear_Click()
Dim strStatText, myShowAllSel As String
Me.lblStatus.Caption = ""
Me.lblStatus.Visible = False
Me.cboLotNo = Null
Me.cboPartNo = Null
Me.cboSupplier = Null
myShowAllSel = "Select * from tbl_Ledger_data"
Me.tbl_Ledger_Data_subform.Form.RecordSource = myShowAllSel
Me.tbl_Ledger_Data_subform.Form.Requery
End Sub

Your guidance is greatly appreciated.

Michael

 

[pbaldy]

Is this what you're trying to do?

http://www.baldyweb.com/CascadingCombo.htm

 

[arnelgp]

on after update event of partno combo, change the rowsource of supplier combo filter to that partno.