Filtering a List Box

[Nuke1096]

Here's the dilemma I've having currently. Right now I have an existing VB project that I didn't build, but I need to add a filter so to the form that it only displays orders in a List Box that have been Canceled or ones that are still Active. I want the user to choose which by a combo box. There are already three combo boxes setup on the form in the same fashion to filter by Name, Department, etc. The problem is the way this form was designed, was that it was built in a such a way that it doesn't use a query from what I can see, and instead does everything via the List Box row source. Here's whats currently in the List Box Row Source.

SELECT tbl_charter_data_history.Charter_ID, tbl_charter_data_history.Charter_Date, tbl_charter_data_history.Bill_To, Format([Phone_No],"000-000-0000") AS Phone, tbl_charter_data_history.Pick_up_Time, tbl_charter_data_history.Pickup, tbl_charter_data_history.Destination FROM tbl_charter_data_history WHERE (((tbl_charter_data_history.Charter_Date) Between Forms!frm_EditCharter_Select2!cboFromDate And Forms!frm_EditCharter_Select2!cboToDate) And ((tbl_charter_data_history.Bill_To)=IIf(Forms!frm_EditCharter_Select2!cboCustomer Is Not Null,Forms!frm_EditCharter_Select2!cboCustomer,tbl_charter_data_history!Bill_To)) And ((tbl_charter_data_history.Phone_No)=IIf(Forms!frm_EditCharter_Select2!cboPhone Is Not Null,Forms!frm_EditCharter_Select2!cboPhone,[phone_No])) And ((tbl_charter_data_history.Branch_assigned_Charter)=IIf(Forms!frm_EditCharter_Select2!cboBase Is Not Null,Forms!frm_EditCharter_Select2!cboBase,tbl_charter_data_history!Branch_assigned_Charter))) ORDER BY tbl_charter_data_history.Charter_Date DESC;

Here's additional relevant information...

Table name where the fields I want to filter are stored - tbl_charter_data_history
Field Name in Table - Status (CANCELLED or ACTIVE)


List Box Name - List33


Code for the Combo Box in question...

Private Sub cboCancel_AfterUpdate()Me.Refresh
End Sub

So my question ultimately is, is there an easy way I can insert something into the row source for the List Box or use code to be able to do this without having to change anything else and rebuild the form from scratch?

[ranman256]

Set the Filter on the data according to the cbo box. No need to change the sql.
(or similar)

Private Sub cboCancel_AfterUpdate()
Me.Filter = "[Status]='" & cboCancel & "'"
Me.FilterOn = True
End Sub

[amrut]

Modify the SQL to include the filter you wish add -
Something like -

Private Sub cboCancel_AfterUpdate()
Dim strSQL as String
strSQL="SELECT tbl_charter_data_history.Charter_ID, tbl_charter_data_history.Charter_Date,tbl_charter_data_history.Status FROM [tbl_charter_data_history] WHERE [Status]=" & Me.YourComboBox & ";"
Me.ListBoxName.RowSource=strSQL
Me.ListBoxName.Requery
End Sub

[Nuke1096]

Set the Filter on the data according to the cbo box. No need to change the sql.
(or similar)

Private Sub cboCancel_AfterUpdate()
Me.Filter = "[Status]='" & cboCancel & "'"
Me.FilterOn = True
End Sub

I tried this code and unfortunately that didn't do anything.

[Nuke1096]

Modify the SQL to include the filter you wish add -
Something like -

Private Sub cboCancel_AfterUpdate()
Dim strSQL as String
strSQL="SELECT tbl_charter_data_history.Charter_ID, tbl_charter_data_history.Charter_Date,tbl_charter_data_history.Status FROM [tbl_charter_data_history] WHERE [Status]=" & Me.YourComboBox & ";"
Me.ListBoxName.RowSource=strSQL
Me.ListBoxName.Requery
End Sub

This unfortunately changes the whole structure of the list box and row source. Also, when I select an option from the Combo box, it prompts me to enter parameter value twice.

[hapm]

So you want the rest of the filters to stay working, don't you? So lets wrap up that query to something readable and add the additional filter:

SELECT tbl_charter_data_history.Charter_ID, tbl_charter_data_history.Charter_Date, tbl_charter_data_history.Bill_To, Format([Phone_No],"000-000-0000") AS Phone, tbl_charter_data_history.Pick_up_Time, tbl_charter_data_history.Pickup, tbl_charter_data_history.Destination 
FROM tbl_charter_data_history 
WHERE (((tbl_charter_data_history.Charter_Date) Between Forms!frm_EditCharter_Select2!cboFromDate And Forms!frm_EditCharter_Select2!cboToDate)
 And ((tbl_charter_data_history.Bill_To)=Nz(Forms!frm_EditCharter_Select2!cboCustomer,tbl_charter_data_history!Bill_To))
 And ((tbl_charter_data_history.Phone_No)=Nz(Forms!frm_EditCharter_Select2!cboPhone,[phone_No])) 
 And ((tbl_charter_data_history.Branch_assigned_Charter)=Nz(Forms!frm_EditCharter_Select2!cboBase,tbl_charter_data_history!Branch_assigned_Charter))
 And ((tbl_charter_data_history.Status)=Nz(Forms!frm_EditCharter_Select2!cboYourStatusFilter,tbl_charter_data_history!Status)))
ORDER BY tbl_charter_data_history.Charter_Date DESC;

Don't forget to replace cboYourStatusFilter with your new combobox name. No change to the vba code needed here.

[Nuke1096]

Wow Hapm, that worked and definitely did the trick. Thank you very much!