Cascading Combo Box Filtering

[dgaletar]

Sure. There is also an image of it in my first post.

CUA# - Number (also PK)(this is the vehicles number assigned by our department - we are in charge of maintaining all vehicles)
TAG # - Text (vehicles license plate #)
VIN - Text (vehicles VIN #)
Year - Number (year of vehicle)
Make - Text (lookup box with the values typed in)
Model - Text
Trim Level - Text
Standard Seating - Number
Inspection Due - Date/Time
Department - Number (combo box with the following as the row source: SELECT [Department].ID, [Department].Department FROM Department ORDER BY [Department]; )
Alt ID - Text (this is the number that the department uses and is sometimes different from the CUA#)
Tag Due - Date/Time
Link - Hyperlink
Attachments - Attachment

...and that's it!

[Seth Schrock]

Okay, that is what I thought. You are going to want your criteria to be

WHERE Department = 4

in your test query. That way you will find all the vehicles that are in department number 4. If you search for CUA# = 4, then you will only find the vehicle with ID number 4. Does that make sense?

Once we move this back to VBA, then the WHERE clause will be

"WHERE Department = " & Me.Department

[dgaletar]

OKAY... here's the new query code:

SELECT [CUA#]
FROM VEHICLE_DETAILS
WHERE [Department]=4;

...and the results are correct; when I run the query I get a list of the numbers for the first department.

YEAH!!!

[Seth Schrock]

Great! So now we can edit your VBA to be:

Dim strSql As String
    strSql = "SELECT [CUA#] " & _
    "FROM VEHICLE_DETAILS " & _
    "WHERE [Department] = " & Me!Department
 
    .RowSource = strSql

This will replace the code in lines 10 - 16 of post #3. Let me know if that works.

[dgaletar]

Here is my code, start to finish. I am still getting an error msg though.

Private Sub Combo14_Click()
        With Me![CUA#]
            If IsNull(Me!Department) Then
                .RowSource = ""
    Dim strSql As String
        strSql = "SELECT [CUA#] " & _
        "FROM VEHICLE_DETAILS " & _
        "WHERE [Department] = " & Me!Department
     
        .RowSource = strSql

     
    Debug.Print strSql
    .RowSource = strSql

            
             End If
            Call .Requery
        End With
End Sub

" Call .Requery" is now in yellow.

[Seth Schrock]

You can use just .Requery, but I don't think that you will need it. I could be wrong though.

[dgaletar]

When I take the "Call" out I still get the error, and when I comment it out all together the "End With" turns yellow.

[Seth Schrock]

It sounds like you need to reset the execution of you code. Click on the button that looks like the stop button next to the "play" and "pause" buttons or click on Run > Reset. Make sure that your .Requery is still commented out, and then click on your combo box to trigger its event and see what happens.

[dgaletar]

Nothing happened. Whichever department I choose shows all of the results for the truck numbers! :-(

[Rabbit]

You're missing the Else part of the If statement. Right now your code won't run unless Department is null. Also, there's no need to assign strSQL twice.

[Seth Schrock]

Oops. Missed that it got removed.

[dgaletar]

I'm sorry guys, but I'm lost. I'm trying different coding there and I still can't figure out what this string should look like.

[zmbd]

Agree with Seth,
Post the code again... looks like a Grimlen has taken over the keyboards again :)

[dgaletar]

Private Sub Combo14_Click()
        With Me![CUA#]
            If IsNull(Me!Department) Then
                .RowSource = ""

            Else
                .RowSource = "SELECT [CUA#] " & _
                             "FROM [tblVEHICLE_DETAILS] " & _
                             "WHERE [Department]=" & Me!Department


'    Dim strSql As String
'        strSql = "SELECT [CUA#] " & _
'        "FROM VEHICLE_DETAILS " & _
'        "WHERE [Department] = " & Me!Department
'
        .RowSource = strSql

     
    Debug.Print strSql
    .RowSource = strSql

            
             End If
             Call .Requery
        End With
End Sub

[zmbd]

Yours cleaned up a tad:

Private Sub Combo14_Click()
    With Me![CUA#]
        If IsNull(Me!Department) Then
            .RowSource = ""
        Else
            .RowSource = "SELECT [CUA#] " & _
                "FROM [tblVEHICLE_DETAILS] " & _
                "WHERE [Department]=" & Me!Department
        End If
        .Requery
    End With
End Sub

The way I would do this for troubleshooting :

Private Sub Combo14_Click()
Dim zstrSQL As String
    With Me![CUA#]
        If IsNull(Me!Department) Then
            .RowSource = ""
        Else
            zstrSQL = "SELECT [CUA#] " & _
                "FROM [tblVEHICLE_DETAILS] " & _
                "WHERE [Department]=" & Me!Department
'Let us see the string
            Debug.Print "resolved string is::: " & zstrSQL
            .RowSource = zstrSQL
        End If
        .Requery
    End With
End Sub

I know that some like to use the "CALL" with a requery or other method of the class/form... more than likely a better way of coding; however, it's not something I'm used to doing in VBA