Relational Combo boxes

[tinmar]

I've been trying to figure out how to have 2 combo boxes on a form, but have the 2nd combo box only have certain drop down selections based on what was chosed in combo box 1.

e.g If I have a table(table1) and have one field called Department and another called Name, If a certain deparmtent is chosen in box 1, then only the correlating names in the table will be given in combo box 2.

Please help

Thanks

 

[missinglinq]

The concept is called 'cascading comboboxes.' Here are two links to tutorials on the subject:

http://www.fontstuff.com/access/acctut10.htm

http://www.tek-tips.com/faqs.cfm?fid=4289

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[tinmar]

Thanks for the sites, however, even when I follow the above code for the examples, combo 2 box does not show any lists based on selection of box 1........

 

[dhookom]

If those don't work, there is a small database with Cascading combo boxes at Roger's Access Library.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

What is the row source for the second combo? What code have you put on the second combo? You will have to provide more than "even when I follow the above code for the examples, combo 2 box does not show any lists based on selection of box 1........". Not much to go on.

 

[tinmar]

OOps, yeah me bad....

- using 1 table , fields are ( country ) & ( city )

- 2 combo boxes, cbocountry and cbocity
- in cbocountry, used

SELECT DISTINCT tblAll.Country FROM tblAll ORDER BY tblAll.Country;

in the row source....

In cbocountry 'afterevent' the following was put in

Private Sub cboCountry_AfterUpdate()
On Error Resume Next
cboCity.RowSource = "Select tblAll.City " & _
"FROM tblAll " & _
"WHERE tblAll.Country = '" & cboCountry.Value & "' " & _
"ORDER BY tblAll.City;"
End Sub

- so i thought technically, there isn't much i really need to put into cbocity is there...

The problem I have is that, no list appears visible in cbocountry except for blanks. However if I do click on a blank some cities do appear.

 

[MajP]

It sounds like your column count is not set up correctly on cboCountry. My guess you did a copy and paste on that combo from a combo with two fields. Ensure for cboCountry

columnCount: 1
bound Column: 1

my guess you have something like

columnCount: 2
columnWidths:0;1"
bound column: 1

 

[tinmar]

Tried that, still blank....... but yes your were right I did have columncount as 2

my table has the 3 fields; ID (PK), Country and city

 

[dhookom]

Please provide these properties for both combo boxes:

Name:
Row Source:
Column Count:
Bound Column:
Column Widths:


Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya tinmar . . .

[blue]The problem I have is that, [purple]no list appears visible in cbocountry except for blanks.[/purple][/blue]

Be aware: when you make a selection in [blue]cbocountry[/blue] you have to [purple]Requery[/purple] [blue]cbocity[/blue] to update the returned recordset!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

You don't have to requery the city combo box if you set the RowSource with code like:

   cboCity.RowSource = "Select tblAll.City " & _
            "FROM tblAll " & _
            "WHERE tblAll.Country = '" & cboCountry.Value & "' " & _
            "ORDER BY tblAll.City;"

I just created something similar in the Northwinds with the Customers table and it worked as desired:
[tt][blue]
Name: cboCountry
Row Source: SELECT DISTINCT Country FROM Customers ORDER BY Country;
Column Count: 1
Bound Column: 1
Column Widths:

Name: cboCity
Row Source: SELECT DISTINCT City FROM Customers WHERE Country = 'xxx' ORDER BY City;
Column Count: 1
Bound Column: 1
Column Widths:
[/blue][/tt]
Code in the After Update event of cboCountry

Private Sub cboCountry_AfterUpdate()
   On Error Resume Next
    Me.cboCity.RowSource = "SELECT DISTINCT City FROM Customers " & _
            "WHERE Country = '" & Me.cboCountry & "' " & _
            "ORDER BY City;"

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

Your statement

The problem I have is that, no list appears visible in cbocountry except for blanks. However if I do click on a blank some cities do appear.

Makes me wonder if you're running Access 2003 with SP3 installed. One of the most common bugs reported with this service pack involves comboboxes with disappearing data! The field(s) in the cbo, in the underlying table, have some kind of formatting in place. This is often the simple > used to force UpperCase. Remove any formatting from the fields and things should work short term, but the definitive solution is to install the hotfix for SP3.

If you're running 2003/SP3, here's a link to Allen Browne's site where you can find the latest news about these bugs, as well a link to the Hotfix:

http://allenbrowne.com/bug-Access2003SP3.html

[/quote]





The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!