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.

[NeoPa]

Right. The three separate threads are now set. The other two are tidy. This is a bit of a rambling mess, but nothing more I can do about that at this late stage.

DGaletar. I need you to read through the following links carefully, as your posts are causing a lot of confusion and a very great deal of wasted time for a lot of people, simply because you are not following the rules.
Before Posting (VBA or SQL) Code.
How to ask "good" questions -- READ BEFORE SUBMITTING A QUESTION!.
POSTING_GUIDELINES: Please Read Carefully Before Posting to a Forum.

After this point you will be expected to know and abide by these rules. Threads like this are likely to be deleted without preamble, regardless of how many responses are in them. If a thread is hijacked then anyone answering a hijack question must know their post is likely to be lost. Hijacking includes asking multiple questions in the same thread. If I don't sound happy, then that's because it's taken an hour of my time just organising the disparate threads (With thanks to Z who had already done part of what was a complicated job).

[dgaletar]

Good morning group. Fresh day, fresh eyes!

OK, I fixed the table name issue (sorry again for that), and ran the form. I am still getting the same error as I did in post #57:

                .RowSource = zstrSQL

Any suggestions?

(here is the full code just to be safe):

Option Compare Database
 
    Private Sub Combo14_Click()
    Dim zstrSQL As String
        With Me![CUA#]
            If IsNull(Me!Department) Then
                .RowSource = ""
            Else
                zstrSQL = "SELECT [CUA#] " & _
                    "FROM [VEHICLE_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
 

You know, I was just checking the second combo box. The 'Row Source' = VEHICLE_DETAILS; and the 'Row Source Type' = Table/Query; but the 'Control Source' is empty. Does that sound right???

[Seth Schrock]

Both combo boxes should have the control source empty because these are unbound controls.

Also, there is no post 57, so I'm not sure what error you are getting. Just tell us the whole error message along with the number.

In line 2, place Option Explicit and then compile your code and see if you get any errors. I'm going to make a guess based on your initial screen shot that you will get an error when you do this (Link to screenshots). The screen shot shows three controls (not including labels): Select a department (which I assume has the name "Department"), "Vehicles Number" (not sure the name of the control), and a button (based on the caption, I'm going to guess that its name is Command25). In your code, the event is for a control named Combo14. Based on the information that I have, I'm going to guess that this is the combo box for the Vehicle Number. However, in line 5, your With statement is for a Me.CUA#. My guess is that these need to be the same name. If Me.CUA# doesn't exist (possibly because of multiple tries to get this to work), then you would get an error on the .RowSource because that property doesn't exist.

Compile your database with Option Explicit set and let us know the result. (To compile the database, click on the Debug menu and click Compile Database name.)

[NeoPa]

@Dgaletar - Please check for and read my PM.

[dgaletar]

Sorry, Seth, post 47.

"The screen shot shows three controls (not including labels): Select a department (which I assume has the name "Department") [NO. THE NAME FOR THIS COMBO BOX IS Combo14], "Vehicles Number" (not sure the name of the control)[THE NAME FOR THIS COMBO BOX IS Combo26], and a button (based on the caption, I'm going to guess that its name is Command25)[NO. I REALIZED EARLY ON THAT I DID NOT NEED A BUTTON, OR AT LEAST I DON'T YET, SO I REMOVED IT.].

"In your code, the event is for a control named Combo14. Based on the information that I have, I'm going to guess that this is the combo box for the Vehicle Number." No, that is not correct. It is the Combo Box for the control labeled "Select a Department".

"However, in line 5, your With statement is for a Me.CUA#. My guess is that these need to be the same name. If Me.CUA# doesn't exist (possibly because of multiple tries to get this to work), then you would get an error on the .RowSource because that property doesn't exist." CUA# does exist in the table VEHICLE_DETAILS.

[Seth Schrock]

You can't reference a field in a table using the Me! command. This can only be used for controls. So based on the information that you have given me, your With statement (line 5) needs to be

With Me.Combo26

Did you set Option Explicit and compile your code? What was the result?

I would also highly recommend renaming your controls so that they mean something. You can search online for Access naming conventions. It will really help you.

[zmbd]

see my post - do as asked.
line12 - post that resolved string as asked

[Seth Schrock]

I think we got the SQL portion figured out Z. Starting with Post #4 ending with post #33 we got the results that were needed. The issue now seems to be with control names, which I must admit, are very confusing. Evidentally, CUA# is only a field and not a control so it doesn't have a .RowSource property. So the error coming from

.RowSource = zstrSQL

is coming from the .RowSource side and not the zstrSQL side.

However, I would encourage dgaletar to follow instructions.

[zmbd]

@ Seth,
Disagreed.
- You asked OP to do as I'd asked in post #49 - although I forgot to include the Option explicit... busy day, I was/am fairly rushed at work this month.

- OP post #47 clearly states that there is still an error occuring. We're only guessing at the SQL.....

- .... your questions are still picking and poking at that SQL string, which when posted would give you some better tools to work with.

- OP still is not providing the EXACT title, error number, and error text/description.

Failing to provide requested information is worse than useless, it wastes what little time all of have to help. I have between 5 and 10 minutes at work between tests and at home between 4 children and a wife and other responsibilities to provide some help - failing to provide the requested information steals the time from someone else's question.

[dgaletar]

OK, I get it! I'm a BAD BAD man!!! Sorry, but this is all pretty confusing to me. I'm doing the best that I can!

I didn't give you the information that you asked for in post#49 because you also gave me a task to do. I had no idea how to do that so it took me a while to figure it out. When I returned, Seth had posted another response and I went on to that.

I imagine that I won't be in this forum much longer anyway. But until NeOpa get his hands on me, would you please ask a specific question and I will give you an specific response as fast as I can.

[Seth Schrock]

1. Get Z the string that got printed in the immediate window
2. Set Option Explicit in line 2 of your code
3. Compile your database and fix the errors. If you need help, then possibly a new thread might be in order for just that question.

[dgaletar]

OK, right now, when I execute the form I get the following Microsoft Visual Basic for Applicati... error box: Compile error; Syntax error. Line 1 (Private Sub Combo14_Click()) is highlighted in yellow, and line 7 ( zstrSQL = "SELECT [CUA#] " &) is highlighted in blue.

[dgaletar]

Sorry, I posted that before I saw your post.

1. From the immediate window:

resolved string is::: SELECT [CUA#] FROM [VEHICLE_DETAILS] WHERE [Department]=4

2. I put "Option Explicit" in line 2 of the VBA code and moved "Dim zstrSQL As String" to line 3. I got another compile error: "Invalid inside procedure".

3. I have to google "Compile you database" so it may take ma a while.

[Seth Schrock]

To compile your database, you just need to click on the Debug menu in the VBA editor and click Compile your database name.

Sorry, by line 2, I was meaning line 2 of what you posted in post 52. It should go right below the Option Compare Database. So at this point, your code should look like this:

Option Compare Database
Option Explicit
 
    Private Sub Combo14_Click()
    Dim zstrSQL As String
        With Me![CUA#]
            If IsNull(Me!Department) Then
                .RowSource = ""
            Else
                zstrSQL = "SELECT [CUA#] " & _
                    "FROM [VEHICLE_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
 

Once you have compiled your database with out getting any errors, try replacing line 6 with

With Me.Combo26

[dgaletar]

Thank you again, Seth, for continuing to help me through this.

I did as you said first, clicked on "Compile" under "Debug" and selected my database but nothing happened.

So I switched line 6 code like suggested and...

IT WORKS!!!

You did it Seth!!! Thank you sooooo much!

I'll have to figure out how to open the corresponding form from here, but THANK YOU!!!

[Seth Schrock]

Glad to be able to help!

A quick nudge for your other problem, Google DoCmd.OpenForm and DoCmd.SearchForRecord. If you need any futher help with that, just start a new thread since that is a new problem.

[dgaletar]

Awesome! Thanks bud!

Somehow that was as easy as 1, 2, 3! Thanks!