Using multiple combo boxes to search 1

[relax4delicutfew]

I have a form which currently has 2 combo boxes, and I use them to sort data from a query I created. My goal is to be able to use the boxes to search independent of one another and in conjunction with one other. I have attached my code. Any help would be greatly appreciated.

Option Compare Database
Option Explicit

Private Const strSQL1 = "SELECT CaseID, Milepost, Purpose, GuardrailType, TerminalType, HardwareComments " & _
   "FROM qryCombo1 WHERE"
Private Const strSQL2 = " RouteNumber = '"
Private Const strSQL3 = " Direction = '"
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 cboDirection_AfterUpdate()
   If Me!cboDirection.Value > 0 Then
      Call FillList
   End If
End Sub

Private Sub FillList()
   strSQL5 = strSQL2 & Me!cboRoute.Value
   strSQL6 = strSQL3 & Me!cboDirection.Value
   
   If Len(Me!cboRoute.Value & "") > 0 Then
        Me!lstCrashes.RowSource = strSQL1 & strSQL5
        Me!lstCrashes.Requery
   End If
   
   If Len(Me!cboDirection.Value & "") > 0 And Len(Me!cboRoute.Value & "") = 0 Then
        Me!lstCrashes.RowSource = strSQL1 & strSQL6
        Me!lstCrashes.Requery
   End If
   
   If Len(Me!cboDirection.Value & "") > 0 And Len(Me!cboRoute.Value & "") > 0 Then
        Me!lstCrashes.RowSource = strSQL1 & strSQL5 & strSQL4 & strSQL6
        Me!lstCrashes.Requery
   End If
End Sub

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

Thanks
Craig

 

[kjv1611]

Have you tried incorporating the DLookup function in your code?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[relax4delicutfew]

Stephen:

I have not tried the DLookup function, as I have never heard of it. Ive fallen into Access for a summer job for my professor, and I dont have extensive knowledge in VBA. I am more of a C++ programmer. I will look into it to see how it may help.

Thanks
Craig

 

[JeffTullin]

Here are some other quick thoughts if you struggle with DLOOKUP and want to get the above working in some form.

First, although I see your constants have an opening quote for the strings, I can't see a closing quote.

So you'll end up with SQL like

Select ... from qryCombo1 where RouteNumber = 'FOOBAR ;
and that's invalid.

I'd be tempted to do something like this:

Private Sub FillList()
dim szSQL as string
szSql = "SELECT CaseID, Milepost, Purpose, GuardrailType, TerminalType, HardwareComments " & _
   "FROM qryCombo1 WHERE "

'add filter for route, if any
if Me!cboRoute >"" then
   szSQL = szSQL & RouteNumber = '" 
   szSQL = Me!cboRoute  & "' "
endif

if Me!cboDirection  >"" then
   'add AND if we had a route
   if Me!cboRoute >"" then
      szSQL = szSQL & " and "
   endif
   'add filter for direction
   szSQL = szSQL & Direction = '" 
   szSQL = Me!cboDirection & "' "
endif


Me!lstCrashes.RowSource = szSQL
Me!lstCrashes.Requery
end sub

 

[relax4delicutfew]

JeffTullin:

Thanks for your response and help. I did struggle trying to use the DLookup function. Your code makes much more sense that the bulky code that I was using, and I see how I forgot to put the ' on my strings. It is tough learning VBA on the fly. I tried the code you gave me, and the values still did not output at the combo boxes were selected. I am going to try and mess around to see if I cant get this working.

Thank you very much!
Craig

 

[FancyPrairie]

Here's a routine that will build the Where clause for you. It works for combo boxes and text boxes (a list box version is demonstrated in another post on this site).

The key to this function working is to define the tag property of the control (i.e. combobox/textbox). The tag property consists of several arguments and should look something like this (syntax and spacing is very important...note no spaces and note ! and .

Where=TableName!FieldName.FieldType.Operator

TableName....Name of the Table or query that contains the field
FieldName....Name of the field within the table or query
FieldType....String and Date are handled specifically. All others are treated the same
Operator.....Equal sign, Greater than, Like, etc.

Example:

Set the Tag Property of the combobox that displays the RouteNumber as follows.

Where=qryCombo1!RouteNumber.String.=

So, to build you SQL statement, the code should look like this:

Dim strSQL as String

strSQL = strSQL1 & BuildWhere(Me)

Msgbox strSQL


Simply copy and paste the code into a new module, set your tag properties accordingly and you're ready to go. Note that this routine assumes that you have NOT defined anything else in the Tag Property of the controls. Also note that I inserted a couple of Msgbox commands so that you can see what's going on.

Function BuildWhere(frm as Form) As String

    Dim frm As Form
    Dim ctl As Control
    Dim var As Variant
    
    Dim i As Integer
    
    Dim strAnd As String
    Dim strWhere As String
    
    strWhere = vbNullString
    strAnd = vbNullString
    
    Set frm = Forms!frmReportCriteria
    
    For Each ctl In frm.Controls
    
        If (ctl.ControlType = acComboBox) Or (ctl.ControlType = acTextBox) Then
            If (Len(ctl.Value) > 0) Then
                If (InStr(ctl.Tag, "Where=") > 0) Then
MsgBox ctl.Tag
                    var = Split(ctl.Tag, ".")
                    If (var(1) = "String") Then
                        strWhere = strWhere & strAnd & "(" & Mid(var(0), 7) & var(2) & "'" & ctl.Value & "') "
                    ElseIf (var(1) = "Date") Then
                        strWhere = strWhere & strAnd & "(" & Mid(var(0), 7) & var(2) & "#" & ctl.Value & "#) "
                    Else
                        strWhere = strWhere & strAnd & "(" & Mid(var(0), 7) & var(2) & "'" & ctl.Value & "') "
                    End If
                    strAnd = " AND "
                End If
            End If
        End If
    Next
    
    BuildWhere = strWhere
    
End Function

 

[FancyPrairie]

Just realized that I left some "testing" code in that module. Therefore, get rid of the line that says Set frm = Forms!frmReportCriteria

 

[relax4delicutfew]

Dear FancyPrairie:

Thank you very much for taking time out to help me with my problem. I created a new module for code you gave me and entered the Where statements in the respective Tag Properties for my combo boxes. The problem I am having is where does this code:

Dim strSQL as String

strSQL = strSQL1 & BuildWhere(Me)

Msgbox strSQL

fit in. Should I delete all the currect code I had a replace with that statement?

As you can probably tell I am on the novice end of programming with Access and I appreciate your patience.

Thanks Very Much
Craig

 

[FancyPrairie]

I don't have time to review exactly what your code is doing. But this is what I think you want. Change your Sub FillList to a Function, like this:

Function FillList()

lstCrashes.RowSource = strSQL1 & BuildWhere(Me)
lstCrashes.Requery

End Function

In the AfterUpdate event of each of you comboboxes, add the following code (it currently reads [Event Procedure]:
=FillList()

The following was just an example of how to make it work, just ignore it

Dim strSQL as String

strSQL = strSQL1 & BuildWhere(Me)

Msgbox strSQL


To summarize:
1. Set the tag property of each of your comboboxes to something like: Where=......
2. Set the AfterUpdate property of each of your comboboxes to =FillList()
3. You only need 2 modules for this to work:
FillList
BuildWhere

 

[relax4delicutfew]

Thanks for all the help!

Craig