Query search using combo boxes

[relax4delicutfew]

I am developing a database where I need to search through the data based on different critera. I am using combo boxes to do this, however I have run into a problem. I can the first combo box to search correctly, however when successive combo boxes are added they wont search correctly. Right now I have two combo boxes, and I want to be able to use each independently as well as a joint search. If anyone can help me it would be greatly appreciated.

Here is the code I am currently working with:

 Option Compare Database
Option Explicit

Private Const strSQL1 = "SELECT CaseID FROM qryCombo1 WHERE"
Private Const strSQL2 = " RouteNumber = '"
Private Const strSQL3 = " PostedSpeed = '"
Private Const strSQL4 = "AND"
Private strSQL5 As String
Private strSQL6 As String

Private Sub cboRoute_AfterUpdate()
If Me!cboRoute.Value > 0 Then
Call FillList
End If
End Sub

Private Sub cboSpeed_AfterUpdate()
If Me!cboSpeed.Value > 0 Then
Call FillList
End If
End Sub

Private Sub FillList()
strSQL5 = strSQL2 & Me!cboRoute.Value & "'"
strSQL6 = strSQL3 & Me!cboSpeed.Value & "'"

If Len(Me!cboRoute.Value & "") > 0 Then
Me!lstCrashes.RowSource = strSQL1 & strSQL5
Me!lstCrashes.Requery
ElseIf Len(Me!cboSpeed.Value & "") > 0 And Len(Me!cboRoute.Value & "") > 0 Then
Me!lstCrashes.RowSource = strSQL1 & strSQL5 & strSQL4 & strSQL6
Me!lstCrashes.Requery
ElseIf Len(Me!cboSpeed.Value & "") > 0 And Len(Me!cboRoute.Value & "") = 0 Then
Me!lstCrashes.RowSource = strSQL1 & strSQL6
Me!lstCrashes.Requery
End If
End Sub

Private Sub Form_Activate()
If Me!cboRoute.Value Like "***" Or Me!cboSpeed.Value Like "***" Then
Call FillList
End If
End Sub

Thanks Very Much
Craig

 

[ormsk]

Hi

Have a look at the following code that I have used previously...

This is a powerful search item which uses listboxes, combo boxes, date ranges etc. Each control simply invokes a procedure (like your FillList). The beauty with this is that you can easily add or delete a control without having to go through the whole code changing IF / ELSEIF statements.

Simply put...you start with a WHERE clause that captures all records...then check if a control has a value selected...then add a further AND to build the filter.

Hope this helps.

Dim strCriteria As String
Dim SQL As String
Dim strOrderByClause As String
Dim strSortDirection As String

Dim strDay As String
Dim strMonth As String
Dim strYear As String
Dim strDayT As String
Dim strMonthT As String
Dim strYearT As String

Dim strExcludeStatus As String
Dim i As Integer

'include IGNORED items into criteria
strCriteria = " WHERE ([PODLogStatusID] <> 5 OR [PODLogStatusID] is null)"

SQL = "SELECT * FROM [Q-POD Search Results]"

' customer
If Len(cboCustomer.Column(1)) > 0 Then
If Len(strCriteria) = 0 Then
strCriteria = " WHERE [Customer] = " & "'" & cboCustomer.Column(1) & "'"
Else
strCriteria = strCriteria & " AND [Customer] = " & "'" & cboCustomer.Column(1) & "'"
End If
End If

'Customer Ref
If IsNull(cboStyle) = False Then
If Len(strCriteria) = 0 Then
strCriteria = " WHERE [Stock Counter] = " & cboStyle.Column(3)
Else
strCriteria = strCriteria & " AND [Customer Ref] = " & "'" & cboStyle.Column(0) & "'"
End If
End If

' PO number
' Note: will ignore Customer and Style as this field is unique
If IsNull(txtPONumber) = False Then
'txtASN.Value = 0
strCriteria = " WHERE [PO Number] = " & txtPONumber.Value ' reset strcriteria
End If

' ASN
' Note: will ignore Customer and Style as this field is unique
If IsNull(txtASN) = False Then
' txtPONumber.Value = 0
strCriteria = " WHERE [ASN] = '" & txtASN.Value & "'" ' reset strcriteria
End If

' Advice Note No
' Note: will ignore Customer and Style as this field is unique
If IsNull(txtAdviceNoteNo) = False Then
' txtPONumber.Value = 0
strCriteria = " WHERE [Advice Note No] = " & txtAdviceNoteNo.Value ' reset strcriteria
End If


' Status
If Len(cboStatus.Column(1)) > 0 Then
If Len(strCriteria) = 0 Then
strCriteria = " WHERE [Status] = " & "'" & cboStatus.Column(1) & "'"
'strCriteria = " WHERE [Status] = " & "'" & cboStatus.Text & "'"
Else
strCriteria = strCriteria & " AND [Status] = " & "'" & cboStatus.Column(1) & "'"
'strCriteria = strCriteria & " AND [Status] = " & "'" & cboStatus.Text & "'"
End If
End If

' missing advice notes
If chkMissingAdviceNotes.Value = True Then ' invoke search
If Len(strCriteria) = 0 Then
strCriteria = " WHERE([Advice Note No] = Null or [Advice Note No] = 0)"
Else
strCriteria = strCriteria & " AND ([Advice Note No] = Null or [Advice Note No] = 0)"
End If
End If

' missing invoices
If chkMissingInvoices.Value = True Then ' invoke search
If Len(strCriteria) = 0 Then
strCriteria = " WHERE ([InvoiceNo] = Null OR [InvoiceNo] = 0) and ([Advice Note No] <> Null and [Advice Note No] > 0)"
Else
strCriteria = strCriteria & " AND ([InvoiceNo] = Null OR [InvoiceNo] = 0) and ([Advice Note No] <> Null and [Advice Note No] > 0)"
End If
End If

' Qty v Despatched Qty Differences
If chkQtyDifferences.Value = True Then ' invoke search
If Len(strCriteria) = 0 Then
strCriteria = " WHERE [Qty] <> [Qty Despatched] AND [Advice Note No] <> Null AND [Advice Note No] <> 0 AND [QtyDiffChecked]=False "
Else
strCriteria = strCriteria & " AND [Qty] <> [Qty Despatched] AND [Advice Note No] <> Null AND [Advice Note No] <>0 AND [QtyDiffChecked]=False "
End If
End If

' Not invoiced to GSCM
If chkNotGSCMinvoiced.Value = True Then ' invoke search
If Len(strCriteria) = 0 Then
strCriteria = " WHERE [Advice Note No] <> Null AND [Advice Note No] <> 0 and [Invoiced] = false and [CustomerID]=1 "
Else
strCriteria = strCriteria & " AND [Advice Note No] <> Null AND [Advice Note No] <> 0 and [Invoiced] = false and [CustomerID]=1"
End If
End If

' set up date filter
If chkCollectionDateFilter.Value = True Then
If Len(strCriteria) = 0 Then
If cmdDate.Caption = "Collection Date:" Then
strCriteria = strCriteria & " WHERE [Collection Date] >= " & txtCollectionDateFrom.DefaultValue & _
" AND [Collection Date] <= " & txtCollectionDateTo.DefaultValue
Else
strCriteria = strCriteria & " WHERE [DateOfInput] >= " & txtCollectionDateFrom.DefaultValue & _
" AND [DateOfInput] <= " & txtCollectionDateTo.DefaultValue
End If
Else
If cmdDate.Caption = "Collection Date:" Then
strCriteria = strCriteria & " and [Collection Date] >= " & txtCollectionDateFrom.DefaultValue & _
" AND [Collection Date] <= " & txtCollectionDateTo.DefaultValue
Else
strCriteria = strCriteria & " and [DateOfInput] >= " & txtCollectionDateFrom.DefaultValue & _
" AND [DateOfInput] <= " & txtCollectionDateTo.DefaultValue
End If
End If
End If

' Exclude Status List Box Items
strExcludeStatus = ""
For i = 1 To lstExcludeStatus.ListCount - 1
If lstExcludeStatus.Selected(i) = True Then
If Len(strExcludeStatus) > 0 Then
strExcludeStatus = strExcludeStatus & " AND "
End If
strExcludeStatus = strExcludeStatus & "[Status] <> '" & lstExcludeStatus.Column(1, i) & "' "
'MsgBox strExcludeStatus
End If
Next i

' add strExcludeStatus to strCriteria
If Len(strExcludeStatus) > 0 Then
' add Is Null clause to strExcludeStatus
'<>'CANCELLED' Or Is Null
strExcludeStatus = "(" & strExcludeStatus & " or [Status] is Null )"
If Len(strCriteria) = 0 Then
strCriteria = " WHERE " & strExcludeStatus
Else
strCriteria = strCriteria & " AND " & strExcludeStatus
End If
End If

Select Case fraSingleRatio
Case Is = 1 ' all
' do nothing
Case Is = 2 ' Singles

If Len(strCriteria) = 0 Then
strCriteria = " WHERE SingleRatio = 'Singles' "
Else
strCriteria = strCriteria & " AND SingleRatio = 'Singles' "
End If

Case Is = 3 ' Ratios

If Len(strCriteria) = 0 Then
strCriteria = " WHERE SingleRatio = 'Ratios' "
Else
strCriteria = strCriteria & " AND SingleRatio = 'Ratios' "
End If

Case Is = 4 ' Uknown (blank)
If Len(strCriteria) = 0 Then
strCriteria = " WHERE SingleRatio = null"
Else
strCriteria = strCriteria & " AND SingleRatio = null "
End If

End Select

' this must be final criteria clause as if value = 5,
' then strCriteria is overwritten by this selection
' Status
If cboStatus.Column(0) = 5 Then ' show IGNOREd items
strCriteria = " WHERE [PODLogStatusID] = 5"
End If

'set strOrderByClause
strOrderByClause = " ORDER By "
Select Case fraOptions.Value
Case Is = 1 ' default
strOrderByClause = strOrderByClause & "LogID"
Case Is = 2 ' collcetion date
strOrderByClause = strOrderByClause & "[Collection Date]"
Case Is = 3 ' Cust.Ref / Style
strOrderByClause = strOrderByClause & "[Customer Ref]"
Case Is = 4 ' PO Number
strOrderByClause = strOrderByClause & "[PO Number]"
Case Is = 5 ' Qty
strOrderByClause = strOrderByClause & "[Qty]"
Case Is = 6 ' ASN
strOrderByClause = strOrderByClause & "[ASN]"
Case Is = 7 ' Advice Note No
strOrderByClause = strOrderByClause & "[Advice Note No]"
Case Is = 8 ' Date of Despatch
strOrderByClause = strOrderByClause & "[Date of Despatch]"
Case Is = 9 ' Invoice
strOrderByClause = strOrderByClause & "[InvoiceNo]"
Case Is = 10 ' style
strOrderByClause = strOrderByClause & "[Customer Ref]"
End Select

Select Case fraOrder.Value
Case Is = 1 ' default
strSortDirection = " ASC"
Case Is = 2 ' collcetion date
strSortDirection = " DESC"
End Select

lstResults.RowSource = SQL & strCriteria & strOrderByClause & strSortDirection
lstResults.Requery

 

[relax4delicutfew]

Ormsk,

Thank you for your reply. I am fairly new to Acces and VB, kinda got thrown into doing a project for a prof., so I have a couple questions I was wondering if you might be able to clear up so I can continue learn.

1) I was wondering what purpose does that POD code at the top serve?

2) After u have the code for the style and customer combo boxes, what do those three things, with the ignore note do?

3)Which section is the filter that determines what to display based on the combo box selections? This is main thing I need to learn how to build because I know IF/ELSE does not make for good reuable/easily modifiable code.


Any more help would be appreciated.

Thanks!
Craig

 

[ormsk]

Hi

1) I was wondering what purpose does that POD code at the top serve?

A) For my purposes, the PODLOGSTatus of 5 meant the order was cancelled so I am filtering out the cancelled orders

2) After u have the code for the style and customer combo boxes, what do those three things, with the ignore note do?

A) For my purposes again, when a user entered an actual PO number - this effectively ignored the style and customer boxes since a PO was unique. If, for example, you entered Bloggs for the customer, and entered PO 12345, if PO 12345 was NOT for Bloggs, it would return nothing. So, when a PO was entered, the Customer and style items were ignored.

3)Which section is the filter that determines what to display based on the combo box selections? This is main thing I need to learn how to build because I know IF/ELSE does not make for good reuable/easily modifiable code.

A) This is the beauty of this code. You query each controls value, checking if the length of strCriteria is 0. If it is 0, then that is where the WHERE clause is going to start, if there it has a length > 0, then you simply addd to the strCriteria by adding " AND blah blah"

When you have finished all the controls, simply update by using requery. My example updates a listbox with the results.

If you add another combo box, you can just simply copy and paste a combo box query from above and change a few bits and you are away.