Is it to do with the SQL query? 1

[associates]

Hi,

I was wondering if anyone could help me out here. This is rather a long one but i hope you can bear with me. This is regarding the filtering record in the listbox based on comboboxes. I have a form that has a listbox, a few comboboxes used to filter the records in the listbox. In the listbox from the first column to the last, i have invoice_no, invoice_date, invoice_jobNo and so on. Everything was working until i decided to sort columns in the listbox. so i put buttons above the listbox. Each buttons correspond to the associates column. They are working very well. The following are the codes for sorting out the invoice_no ascending and descending.

Private Function basOrderby(col As String, xorder As String) As Integer
Dim strSQL As String

'Set row source for list box
strSQL = "SELECT DISTINCTROW Invoice_ID, Invoice_No, Invoice_Date, Invoice_Industry, Invoice_Client, Invoice_Job, Invoice_Total, Invoice_Status "
strSQL = strSQL & "FROM Invoice_Register "
strSQL = strSQL & "ORDER BY " & col & " " & xorder
Me!ListInvoices.RowSource = strSQL
Me!ListInvoices.Requery

End Function


Private Sub CM_OrderByInvAsc_Click()
'Set Invoice_No order in ascending order and apply captions
Dim response As Integer
response = basOrderby("Invoice_No", "asc")
Me!CM_OrderByInvDesc.Visible = True
Me!CM_OrderByInvDesc.Caption = "v Invoice_No v"
Me!CM_OrderByInvDesc.SetFocus
Me!CM_OrderByInvAsc.Visible = False
Me!ListInvoices.SetFocus
End Sub

Private Sub CM_OrderByInvDesc_Click()
'Set Invoice_No order in descending order and apply captions
Dim response As Integer
response = basOrderby("Invoice_No", "DESC")
Me!CM_OrderByInvAsc.Visible = True
Me!CM_OrderByInvAsc.Caption = "^ Invoice_No ^"
Me!CM_OrderByInvAsc.SetFocus
Me!CM_OrderByInvDesc.Visible = False
Me!ListInvoices.SetFocus
End Sub

However, i noticed that those comboboxes that were created earlier for the use of filtering the listbox don't work anymore. Nothing happens in the listbox when selecting an item in the combobox.

Private Sub CB_InvoiceNo_AfterUpdate()
Me.TB_InvoiceNoquery = "*"
DoEvents
If Not IsNull(Me.CB_InvoiceNo) Then
If Me.CB_InvoiceNo <> "" Then
Me.TB_InvoiceNoquery = Me.CB_InvoiceNo
End If
End If

DoEvents
Me.Listinvoices.Requery
End Sub

the TB_InvoiceNoquery is what i put into the criteria under invoice_no fields in the sql statement:Query builder

I think this is to do with the sql rowsource because i pass in a sql statement into it when performing the sorting the columns in the listbox. Is there any way of reseting the sql rowsource so that the combobox can still be used to filter the listbox?

I hope this helps explain.

Thank you in advance and look forward to hearing from you

 

[TheAceMan1]

How are ya associates . . .

[blue]Is there any way of reseting the sql rowsource [purple]so that the combobox can still be used to filter the listbox?[/purple][/blue]

Sure! . . . Include a [blue]Where[/blue] clause in your [blue]SQL[/blue]!

Note: when you update the [purple]RowSource[/purple] of a listbox, it [blue]automatically requires![/blue]

See Ya! . . . . . .