Combo boxes as searching tools

[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. Right now I have two combo boxes, and I want to be able to use each independently as well as a joint search.

I get in error due to the text I have in my tag for the combo boxes. The current code there is

Where=qryCombo1!RouteNumber.String.=

I also have =FillList() in the afterupdate

The currect code I have is

Option Compare Database
Option Explicit

Function FillList()
    lstCrashes.RowSource = BuildWhere(Me)
    lstCrashes.Requery
End Function

And I have a module

Option Compare Database

Function BuildWhere(frm As Form) As String

    Dim ctl As Control
    Dim var As Variant
    
    Dim i As Integer
    
    Dim strAnd As String
    Dim strWhere As String
    
    strWhere = vbNullString
    strAnd = vbNullString
      
    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

Any help is helping me get this work would be appreciated.

Thanks
Craig Weinschenk

 

[willir]

I get in error due to the text I have in my tag for the combo boxes.

What is the error, and can you provide the line it occurs on if in the code your provided, or provide the code that generates the error.

 

[relax4delicutfew]

Dear Willir

The error occurs when I am in the form view. I try to use one of the combo boxes and when I click on what I want to select for the box, an Access box pops up with the code that I have in my Tag and my only choice is to hit OK.

Thanks for your response.
Craig

 

[willir]

Relax

This suggests you have error handling code in your form - usually created when you use the wizards to create a command button, or combo box. This is a good thing to prevent end users from getting thrown into the VBA coding window, but sure makes debugging tough.

Open the form in design mode, and make sure the properties window is open (from the menu, select "View" -> "Properties")

Now select the combo box that causes the error. Then click on the "Event" tab in the properties window. You will see an Event Procedure", perhaps under the AfterUpdate event. Select this event, and then click on the command button "..." that appears to the right of the field to open up the VBA coding window and places you on the corresponding code. You will see something like...

Private Sub YourComboBox_AfterUpdate()
[COLOR=blue]On Error GoTo ....[/color]
...

End Sub

Place a single quote, ' at the beginning ot the ON Error line (in blue) to comment out the error handling. Remove the ' later after you resolved the problem.

You can view your form normally, and try clicking on the offending combo box. Now, provided other error handling is not included allow the programming "path", you will hit the offending line that causes the error.

You never did tell us what the error message is.

...More advanced.
Place a STOP statment after the On Error line. Your code will STOP at this point. Then use F8 to step through your code. (Don't forget to remove the STOP line afterards.)

Richard