Cascading Combo Box Filtering

**dgaletar** · Jan 31 '13, 08:30 PM

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

Code:

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

**dgaletar** · Jan 31 '13, 08:32 PM

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

Code:

                .RowSource = zstrSQL

**dgaletar** · Jan 31 '13, 08:35 PM

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** · Jan 31 '13, 08:36 PM

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** · Jan 31 '13, 08:51 PM

From post #35 that you said worked, your table name is VEHICLE_DETAILS . In post #46, the table name became tblVEHICLE_DETA ILS 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_DETA ILS 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_DETA ILS
or
VEHICLE_DETAILS

**NeoPa** · Jan 31 '13, 11:18 PM

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_GUIDELI NES: 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** · Feb 1 '13, 11:58 AM

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:

Code:

                .RowSource = zstrSQL

Any suggestions?

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

Code:

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** · Feb 1 '13, 12:22 PM

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** · Feb 1 '13, 01:36 PM

@Dgaletar - Please check for and read my PM.

**dgaletar** · Feb 1 '13, 01:39 PM

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** · Feb 1 '13, 01:43 PM

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

Code:

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** · Feb 1 '13, 01:43 PM

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

**Seth Schrock** · Feb 1 '13, 01:50 PM

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

Code:

.RowSource = zstrSQL

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

However, I would encourage dgaletar to follow instructions.

**zmbd** · Feb 1 '13, 02:04 PM

@ 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 responsibilitie s to provide some help - failing to provide the requested information steals the time from someone else's question.

**dgaletar** · Feb 1 '13, 02:20 PM

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.

Cascading Combo Box Filtering

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment