Cascading Combo Box Filtering

[dgaletar]

{Moderator Edit}
I have moved this hijack post to a new thread. Please do not take over an existing thread with your own questions as this is a punishable offense.
The original thread, from where this was originally taken, can be found at how to filter list of items in a combo box by selecting items in another combo.
{/Moderator Edit}
OK, I changed the FROM statement, and am still getting flagged. Now the ".RowSource" & the "FROM" lines have red dots, and the "WHERE" has a red dot with a yellow arrow.

Does that make sense???

Also, when I hover the mouse anywhere over those three lines, a message (sort of a help tip) appears below the mouse. It says: Me!Department=4.

[Seth Schrock]

The code in your screenshot1 doesn't match the code you provided in post #4. Please post what you have using Copy/Paste (and please use code tags, the [CODE/] button) so that we can see exactly what you are working with.

What I'm seeing in the screenshot is that your SQL code selects [Combo26] from your table. My guess is that there isn't a field called Combo26, which would cause an error.

[dgaletar]

Sorry about that! I changed it after I found that [Combo26] was incorrect.

Here is the code as I am using it now:

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

I also noticed that in Rabbits article he used this:

Private Sub Company_AfterUpdate()

instead of:

Private Sub Combo14_Click()

...with me changing "Company" to "Department".

But, when I tried to execute the command, the dropdown box didn't work.

Then, when I went back into the design mode, the [EventProcedure] was gone from the "OnClick" line. When I clicked on the three dots, it started from scratch, starting a new "Private Sub" with the line "Combo14_Click()" in it.

[Seth Schrock]

Just for troubleshooting purposes, lets assign your SQL code to a variable.

Dim strSql as String
strSql = "SELECT [CUA#] " & _
"FROM [VEHICLE DETAILS : Table] " & _
"WHERE [CUA#]=" & Me!Department
 
Debug.Print strSql
.RowSource = strSql

Replace your lines 6-8 with the above code, make sure your immediate window is open (Ctrl + G will open it if not), and then run your code. Copy and paste what is printed in the immediate window into a new query and run it. If the results are what you expect, then the problem isn't with your SQL and is somewhere else. I'll continue to look for that something else, but will wait to post any ideas until after you respond with the results.

[dgaletar]

I am soooo sorry Seth Schrock, but this is wayyyy over my head! I coppied and pasted the code that you listed into mine and now I have this:

Private Sub Department_AfterUpdate()
'Private Sub Combo14_Click()
        With Me![CUA#]
            If IsNull(Me!Department) Then
                .RowSource = ""
            Else
'                .RowSource = "SELECT [CUA#] " & _
'                             "FROM [VEHICLE DETAILS : Table] " & _
'                             "WHERE [CUA#]=" & Me!Department
    Dim strSql As String
    strSql = "SELECT [CUA#] " & _
    "FROM [VEHICLE DETAILS : Table] " & _
    "WHERE [CUA#]=" & Me!Department
 
    Debug.Print strSql
    .RowSource = strSql
 
 
            End If
            Call .Requery
        End With
End Sub
 

I then did Ctrl+G and opened the "immediate window".

"run your code"... I pressed the green arrow and a Macros box popped up asking me for the Macro name. I of course have not named any of my macros (as I do not know how to, or that I am supposed to), so I typed in "Department" as a name and hit create. It opened a new box labeled "Vehicles - Module1 (Code)". I copied and pasted all of the code into this new box and it looks like this:

Sub Department()
Private Sub Department_AfterUpdate()
'Private Sub Combo14_Click()
        With Me![CUA#]
            If IsNull(Me!Department) Then
                .RowSource = ""
            Else
'                .RowSource = "SELECT [CUA#] " & _
'                             "FROM [VEHICLE DETAILS : Table] " & _
'                             "WHERE [CUA#]=" & Me!Department
    Dim strSql As String
    strSql = "SELECT [CUA#] " & _
    "FROM [VEHICLE DETAILS : Table] " & _
    "WHERE [CUA#]=" & Me!Department
 
    Debug.Print strSql
    .RowSource = strSql
 
 
            End If
            Call .Requery
        End With
End Sub
 
End Sub

Now when I click on the green arrow I get an error message saying "Compile Error: Expected End Sub". If I remove that line from the code and click on the green arrow, I get another error message that says "Compile error - Invalid Use of Me keyword", and the "Private Sub Department_AfterUpdate()" turns yellow.

I feel like an idiot!!! What am I doing wrong?

[Seth Schrock]

Don't worry, you aren't an idiot. It wasn't that long ago that I was running into the same types of problems.

To run your code, you need to perform the action that triggers the event. In this case, you need to change the value selected in the Department combo box. That will trigger the Department's AfterUpdate event and run your code.

[zmbd]

I've split this entire series out of the original thread they were posted because this was technically a Hijack. Honestly, I'm surprised it was allowed to continue.

However, at this point, the information and the help certainly merited its own thread instead of being deleted.

Dgaletar: Please in the future do not Hijack other people's threads, even though it is the same or close to the same topic. Instead, please post your own thread. It is usually permissible to provide a link(s) to the thread(s) that have a direct relationship to your question.

[dgaletar]

OK zmbd, thanks. It won't happen again.

And Seth Schrock, thanks for understanding!

OK, So I ran the code again with the immediate window open. First off, I had to change that first line from "Private Sub Company_AfterUpdate()" to "Private Sub Combo14_Click()" for it to run at all.

Then, when I did run it, I got an error popup - Microsoft Visual Basic - Runtime error '438': Object doesn't support this property or method.

But, in the immediate window I got this: SELECT [CUA#] FROM [VEHICLE DETAILS : Table] WHERE [CUA#]=4.

[Seth Schrock]

Okay. If you click Debug on the error message, which line does it highlight?

Take what you got in the immediate window, create a new query object and paste that into the SQL view and try to run it. Let me know the results.

[dgaletar]

    .RowSource = strSql

is highlighted.

Okay, never did this before so bear with me...

I clicked on "Create", and then "Query Design". I closed the "Show Table" box and clicked on the "SQL" view in the top left hand corner. I pasted that line of code and got this error message:

"The Microsoft Access database engine cannot find the input table or query 'VEHICLE DETAILS : Table"'. Make sure it exists and that it's name is spelled correctly."

[dgaletar]

You know, I'm kinda stuck on that immediate window code:

SELECT [CUA#] FROM [VEHICLE DETAILS : Table] WHERE [CUA#]=4

Why am I looking for the [CUA#] where the [CUA#]=4? I'm trying to populate a list of CUA#'s that belong to a specific department. Right?

[Seth Schrock]

Okay. Evidentally you can't use a variable as the row source. Didn't know that. Well, once we get the SQL fixed, we can just use the SQL for the row source instead of the variable.

So, per the error message, you just need to fix the name of the table in the query.

[dgaletar]

So, re-name the table? The actual table, or just in the query?

[Seth Schrock]

Did see your second post.

So you need to change your WHERE clause to be

"WHERE Department = " & Me.Department

All the immediate window does is shows you what the string looks like with the values from the controls and with it all concatenated together. It is what the SQL engine sees. So what you are seeing in the immediate window is what you typed in line 14 (post 7)

[Seth Schrock]

Just in the query. The error message is saying that there is no table named VEHICLE DETAILS : Table. My guess is that the table name is actually VEHICLE DETAILS, but this is only a guess since I don't have your database in front of me.

[dgaletar]

OK, so you are saying that I need to change the name of the table only in the query to "Me.Department"?

[dgaletar]

Oh!!! You are right! It is just named "VEHICLE DETAILS". Ooops!

[dgaletar]

OK, now we are cooking with gas! I changed the name in the query and ran it and it returned a value of 4.

[dgaletar]

OK, I also changed the table name in the Macros box. Got the same error with the same line highlighted:

    .RowSource = strSql

[Seth Schrock]

Me.Department is a reference to a control on the current form, not a table name.

Here is what the query needs:
SELECT - your fields names. Currently asking for the field CUA#
FROM - the name of the table where your selected fields can be found, currently listed as "VEHICLE DETAILS : Table" but this is wrong
WHERE - the criteria that specifies which records to be selected

You need a valid table name for the query to get records from. You need valid field names from the table you are querying to SELECT and to set criteria by in the WHERE clause. I don't know what tables names you have or the fields names so I can't tell you what exact text needs to go in the query.

[Seth Schrock]

Cross posted again :)

So did your query work and did it return the records you wanted?

[dgaletar]

Got it. Here's what I have listed in the SQL view of the query:

SELECT [CUA#] FROM [VEHICLE DETAILS] WHERE [CUA#]=[Department]
 

...but when I run the query, it only returns an empty box labeled CUA#.

I imagine that I now need to connect the query to the form with the drop down box, right?

[Seth Schrock]

Instead of [Department], put 4 as this is what Me.Department equals.

[dgaletar]

Got it. Here's what I have listed in the SQL view of the query now:

SELECT [CUA#]
      FROM [tblVEHICLE DETAILS]
      WHERE [CUA#]=4
 

...and when I run the query, it returns a 4 in the box labeled CUA#.

[Seth Schrock]

In tblVEHICLE DETAILS, is there a field titled CUA#?

[dgaletar]

OK, I can't seem to post to this list anymore. I have posted the same thing 3 times and it is showing up in the other forum topic.

[dgaletar]

Yes. It is the Primary Key for the table. Department is also in this table, although it is a drop down list, drawing it's info from the table Departments.

[Seth Schrock]

Oh, Duh. I can't believe I missed that. What other fields in tblVEHICLE DETAILS do you want to see in your combo box? You will need to enter those field names in the SELECT portion of your query as well.

What other forum topic are you talking about?

[dgaletar]

The last one... the forum topic where we were originally.

Anyway, that is the only field that will need to be seen. The idea is that they select the department from the first drop down list. Then, they move to the next drop down where the CUA#'s are populated for that department.

Although we haven't gotten this far yet, once they select the CUA# that they want from that list, it opens the appropriate form for that vehicle.

[Seth Schrock]

Oh, we are in the same forum topic, just page 2. Each page only holds 25 posts.

Something seems strange with the way you are designing this database. Can you list the fields in tblVEHICLE DETAILS along with their data types for me? I have a guess of what is wrong, but I would like to check the list before going further.

[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

[dgaletar]

Using the cleaned up code, I get an error that highlights the following fields:

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

[dgaletar]

and using the second "troubleshooting" code, I get an error that highlights the following field:

                .RowSource = zstrSQL
 

[dgaletar]

Guys, I'm really sorry but I have to leave for the day. I will be back at this tomorrow morning at 7:01am EST.

THANK YOU ALL FOR ALL OF THE HELP WITH THIS!!!

[zmbd]

We cross posted, added a tad.

Simply stating that you get an error isn't helpfull. Please give the EXACT title, error number, and error text/description.

Also, please do a Compile from the Debug menu, fix any errors shown and then re-compile, fix, repeat until you do not get any errors.

In the debug window will be the SQL string (press <ctrl><g>) post that string.

[Seth Schrock]

From post #35 that you said worked, your table name is VEHICLE_DETAILS. In post #46, the table name became tblVEHICLE_DETAILS on line 8. Line 14 has what I believe to be the correct table name, but it is commented out. When the code got cleaned up, the table name tblVEHICLE_DETAILS was the name used. If this table name is incorrect, it would explain post #48 saying that you got an error on that code. The table name was an issue before and really needs to get cleared up.

Which table name is correct?
tblVEHICLE_DETAILS
or
VEHICLE_DETAILS