Cascading Combo Boxes

[RexesOperator]

I have looked at the Microsoft site and various examples in the forum. There seems to be a couple of approaches. I just want to make sure I understand how to do this.

I have two tables with a one-to-many relationship - tblCompanies and tblContacts. I want to use a bound form tied to a query - qryTransactionDetails that holds the search fields CompanyID and ContactID (among other fields). I want two use two cascading combo boxes cboCompanyId and cboContactID.

For the cboCompanyId I have:

Row Source Type: Table/Query
Row Source: qryTransactionDetails
Bound Column: 1

for the after update event (using the Microsoft example) I have:

Private Sub cboCompanyID_AfterUpdate()

Me.cboContactID.RowSource = "SELECT ContactID FROM" &_
" cboContactID WHERE CompanyID = " & Me.CompanyID &_
" ORDER BY cboContactID"
Me.cboCompanyID.Requery

End Sub

For the cboContactID I have

Row Source Type: Table/Query
Row Source:
Bound Column: 1.

ATM I just have the two fields in the query so I am not concerned with referencing columns/column widths etc.

Is this correct so far?

 

[pbaldy]

You want to reference the table/field names in the SQL, not cboContactID. You don't need the requery, as setting the row source will accomplish it.

 

[RexesOperator]

Wow that was fast. Were you just waiting for me? I also looked at your example from other posts.

I took out the requery.

The SQL (from the query) is

SELECT tblCompanies.CompanyID,tblContacts.ContactID
FROM tblCompanies INER JOIN tblContacts ON tblCompanies.CompanyID = tblContacts.CompanyID;

Does this not do as you say? - reference the table/field names in the SQL?

Would the AfterUpdate() event become:

Me.cboContactID.RowSource = Me.cboCompanyID

 

[pbaldy]

I was referring to this:

Me.cboContactID.RowSource = "SELECT ContactID FROM" &_
" cboContactID WHERE CompanyID = " & Me.CompanyID &_
" ORDER BY cboContactID"

 

[pbaldy]

And yes, I was waiting for you.

 

[RexesOperator]

And yes, I was waiting for you.

Am I that predictable? Or are you that bored?

I will have to surprise you one day and figure this out for myself. Out of curiosity has Access2007 made it any easier to create cascading combos?

I am attaching what I currently have because I don't see where the error is.

 

[pbaldy]

Am I that predictable? Or are you that bored?

Neither, just happened to come online right after you posted the question.

I only have A2000 here, so can't look at the sample. Did you fix the SQL I pointed out earlier?

 

[RexesOperator]

I did thusly:

Private Sub cboCompanyID_AfterUpdate()
Me.cboContactID.RowSource = "SELECT ContactID FROM" & _
" cboContactID WHERE CompanyID = " & Me.cboCompanyID & _
" ORDER BY cboContactID"


End Sub

See if you can open the converted version.

 

[pbaldy]

I get a weird error trying to open that file, but in any case you haven't changed what I recommended:

Me.cboContactID.RowSource = "SELECT ContactID FROM" & _
" TableNameHere WHERE CompanyID = " & Me.cboCompanyID & _
" ORDER BY FieldNameHere"

 

[RexesOperator]

OK I thought you were highlighting what you wanted me to change to, not change from. This is going to sound thick, but for tablename I use the query name, and for fieldname, I use the name in the query NOT the name of the control, right? I now have:

Me.cboContactID.RowSource = "SELECT ContactID FROM" & _
" qryTransactionDetails WHERE CompanyID = " & Me.cboCompanyID & _
" ORDER BY ContactID"

When I do it this way a parameter box pops up when I click on the cboContactID control. Also, in the cboCompanyID a separate instance for each company appears for each contact. Thus a company with 5 contacts appears in the combo box five times.

 

[boblarson]

Okay, here's your sample redone.

I changed your contactID to an autonumber (shouldn't be text or enterable). Also, I changed the query underlying the second combo.

 

[pbaldy]

Try this:

Me.cboContactID.RowSource = "SELECT ContactID FROM" & _
" tblContacts WHERE CompanyID = '" & Me.cboCompanyID & _
"' ORDER BY ContactID"

 

[pbaldy]

Never mind, Bob's here to save the day!

 

[RexesOperator]

Nice to see you back Bob - I've missed your answers. And I like your site too. When are you going to set up a training course for neophytes like me?

However WRT:

Me.cboContactID.RowSource = "SELECT tblContacts.ContactID, tblContacts.CompanyID, [LASTNAME] & ', ' & [FIRSTNAME] AS Contact " & _
"FROM tblContacts " & _
"ORDER BY [LASTNAME] & ', ' & [FIRSTNAME] "


When I select Company I do not get the list of associated contacts.

One question I have. I am using the query qryTransactionDetails. Should the SELECT items tblContacts.ContactID etc be qryTransactionDetails.ContactID?

 

[boblarson]

In the sample, you should see one contact listed after selecting the company in the first box. The reason you don't see any others is that there is no data in the contacts table, other than the one contact.

 

[RexesOperator]

I should have added that I am trying this in the full db - not the sample.

 

[boblarson]

Pay close attention to what changes I made in the sample

1. ContactID change
2. Number of Columns property changed
3. Column Width property changed

 

[RexesOperator]

Thank you Bob and Paul for your efforts. I did not abandon this - I simply had other work to do. As you may have gathered I am on a ferociously flat learning curve.

After numerous tries I was still having problems. Then I created a new blank db (again), imported everything and for good measure, did a compact and repair. Bingo! Everything works! Now I can now come up with situations with which to bedevil you!

 

[RexesOperator]

Re: Cascading Combo Boxes Redux

Sorry to bug you guys about this again, but I am really getting frustrated with this. I have gone over the examples that Bob did for me. I thought I understood them. I know I got this to work before but I can't get it working again.

The form frmTrxns is based on the query qryTransactions. I want cboContactID to show up after selecting cboCompanyID.

The rowsource for cboComanyID is SELECT tblCompanies.* FROM tblCompanies. I have the following in the AfterUpdate event of the cboCompanyID. cboContactID has 4 columns, each set to 2 centimeters (about an inch for the non-metric types);

Private Sub cboCompanyID_AfterUpdate()

Forms!frmTrxns.cboContactID.RowSource = "SELECT DISTINCT tblContacts.ContactID " & _
"FROM tblContacts " & _
"GROUP BY tblContacts.ContactID " & _
"WHERE tblContacts.CompanyID = '" & cboCompanyID.Value & "' " & _
"ORDER BY tblContacts.ContactID;"

Forms!frmTrxns.cboContactID.SetFocus
Forms!frmTrxns.cboContactID.Dropdown

End Sub

Nothing is showing up in cboContactID.