Cascading Combo Box

[rexmorgan]

I thought I knew how to do this but after several hours I am still unable to figure where I am going wrong. I simply have several tables tblCountries, tblStates, tblCounties, and tblEntity Info. I also have a form frmTest. I have tried different ways of implementing the cascading combo boxes but cannot seem to get it to work. I would like to be able to select a country from one combo box and have the state combo box values dependent on the country combo box and the same with county and city. I am including the database attachment. I am using Access 2010. Thanks in advance for any help. BTW I have attempted to read other posts regarding similar problems but still did not make a progress.

 

[bob fitz]

If you could post your db in 2003 mdb format I (and many others) may be able to help.

 

[rexmorgan]

Sorry about the delayed reply. I was just getting off work when I noticed your post. I have now converted the database into mdb (2003) format. Again thanks in advance for any assistance.

 

[bob fitz]

Please post again with db attached

 

[rexmorgan]

I must be more tired than I realized. This time with attachment.

 

[bob fitz]

Please take a look at your the attached db which I have ammended and which I think works now as you require. The changes I have made are:

I have commentented out the code you had for the After Update event of cboCountries and added one line:

Me.cboState.Requery

I have set the RowSource property of cboStates to:

SELECT tblStates.ID, tblStates.States, tblStates.StatesAbbrev, tblStates.Country FROM tblStates WHERE (((tblStates.Country)=[Forms]![frmTest]![cboCountry]));

I have also changed the ColumnCount and ColumnWidths properties.

 

[rexmorgan]

Many thanks for the assistance bob. I am still unclear as to why the

Private Sub cboCountry_AfterUpdate()

Me.cboState.RowSource = "SELECT tblStates.States FROM tblStates WHERE tblStates.Country = " & Me.cboCountry & " ORDER BY State"
Me.cboState = Me.cboState.ItemData(0)
Me.cboState.Requery

End Sub

code did not work. I thought I has seen an example similar to that work elsewhere. Oh well, I shall chalk it up as lesson learned. Thanks again! Great Work!

 

[bob fitz]

Hi

I don't know if it is the only problem, but I can see that the syntax is not correct in:

Me.cboState.RowSource = "SELECT tblStates.States FROM tblStates WHERE tblStates.Country = " & Me.cboCountry

This would work if the value of Me.cboCountry was numeric, which in your case it is not. You would need something like:

Me.cboState.RowSource = "SELECT tblStates.States FROM tblStates WHERE tblStates.Country = '" & Me.cboCountry & "'"