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_Afte rUpdate()" 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_AfterUp date()" 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."