Clear and Repopulate Combo Boxes (1 Viewer)

[GMSRE]

Hello

I am using Access 2007. I have two combo boxes called state and county. When I select a state it will populate county combo box with counties that are associated with the state selected. I also created a command button on my form called “Clear Entry”. I used the code County .RowSource = "". It cleared the lists of counties in the county combo box. When I selected a new state from the state combo box for example called California, the county dropdown list would not repopulate. The header for the county dropdown list also disappeared. The requery code in State_AfterUpdate does not work. The only way for the county list to be able to repopulate, was to close the form and open it again. What code would I need to be able repopulate the County combo box. Your help will be greatly appreciated. Thanks. GMSRE

Private Sub cmdClearEntry_Click()
Me.State = Null
Me.County.RowSource = ""
End Sub
Private Sub State_AfterUpdate()
Me.County = Null
Me.County.Requery
Me.County = Me.County.ItemData(0)
End Sub

 

[John Big Booty]

Perhaps the sample posted here will give you some ideas.

 

[GMSRE]

The database I downloaded was very helpful. Thanks so much.
GMSRE

 

[GMSRE]

I am stuck again.

I was able to repopulate the County combo box, but I was not able to repopulate the Community combo box. When selecting a state from the State combo box it will repopulate the County combo box based on the state selected. When I select a county from county combo box it does not repopulate the Community combo box based on the county selected.

I used the code in the database link you provided me a couple of days ago. This code works when selecting a state from the State combo box to populate the county combo box listed below.

Private Sub State_AfterUpdate()
Me.County = Null
Me.County.Requery
Me.County.RowSource = "SELECT CountyTable.CountyID, CountyTable.County, CountyTable.CountyID, CountyTable.State " & _
"FROM StateTable INNER JOIN CountyTable ON StateTable.StateID = CountyTable.StateID " & _
"WHERE (((StateTable.State)=[forms]![ReportForm]![State])) ORDER BY CountyTable.County;"
Me.County = 0

End Sub


I tried to use the same type of code listed below to populate the Community combo box based on the county I selected. It doesn’t work and I can’t figure why. Your help will be greatly appreciated.

Thank you.

GMSRE


Private Sub County_AfterUpdate()
Me.Community = Null
Me.Community.Requery
'Me.Community.RowSource = "SELECT CommunityTable.CommunityID, CommunityTable.Community, CommunityTable.CommunityID, CommunityTable.County, CommunityTable.CountyID" & _
"FROM CountyTable INNER JOIN CommunityTable ON CountyTable.CountyID = CommunityTable.CountyID " & _
"WHERE (((CountyTable.County)=[forms]![ReportForm]![County]))" & "ORDER BY CommunityTable.CommunityID;"
Me.Community = 0

End Sub

 

[Xproterg]

I am stuck again.

I was able to repopulate the County combo box, but I was not able to repopulate the Community combo box. When selecting a state from the State combo box it will repopulate the County combo box based on the state selected. When I select a county from county combo box it does not repopulate the Community combo box based on the county selected.

I used the code in the database link you provided me a couple of days ago. This code works when selecting a state from the State combo box to populate the county combo box listed below.

Private Sub State_AfterUpdate()
Me.County = Null
Me.County.Requery
Me.County.RowSource = "SELECT CountyTable.CountyID, CountyTable.County, CountyTable.CountyID, CountyTable.State " & _
"FROM StateTable INNER JOIN CountyTable ON StateTable.StateID = CountyTable.StateID " & _
"WHERE (((StateTable.State)=[forms]![ReportForm]![State])) ORDER BY CountyTable.County;"
Me.County = 0

End Sub


I tried to use the same type of code listed below to populate the Community combo box based on the county I selected. It doesn’t work and I can’t figure why. Your help will be greatly appreciated.

Thank you.

GMSRE


Private Sub County_AfterUpdate()
Me.Community = Null
Me.Community.Requery
'Me.Community.RowSource = "SELECT CommunityTable.CommunityID, CommunityTable.Community, CommunityTable.CommunityID, CommunityTable.County, CommunityTable.CountyID" & _
"FROM CountyTable INNER JOIN CommunityTable ON CountyTable.CountyID = CommunityTable.CountyID " & _
"WHERE (((CountyTable.County)=[forms]![ReportForm]![County]))" & "ORDER BY CommunityTable.CommunityID;"
Me.Community = 0

End Sub

You have a quote in from of me.community.rowsource ('Me.Community.RowSource ). Other than that, you need to make sure that you set a rowsource before you requery. The requery even is used to requery the current rowsource, so if there's nothing in there, nothing will be returned. Hope that helps!

Private Sub County_AfterUpdate()

Me.Community = Null
Me.Community.Requery

Me.Community.RowSource = "SELECT CommunityTable.CommunityID, CommunityTable.Community, CommunityTable.CommunityID, CommunityTable.County, CommunityTable.CountyID FROM CountyTable INNER JOIN CommunityTable ON CountyTable.CountyID = CommunityTable.CountyID WHERE (((CountyTable.County)=[forms]![ReportForm]![County])) ORDER BY CommunityTable.CommunityID;"

Me.Community = 0

End Sub

 

[GMSRE]

I appreciate your quick response. I copied and pasted the code and it still doesn't work. Thanks.

 

[Xproterg]

Don't suppose you could post a copy of your database so we know exactly what we're working with?

 

[GMSRE]

Hi Xproterg,

Attached is the database. Click on the reports button. The state comb box populates the county. The county is supposted to populate community.

Thank you. GMSRE

 

[GMSRE]

Hi Xproterg,

I am not sure if you got this message last week. Attached is the database. Click on the reports button. The state comb box populates the county. The county is supposted to populate community, but it does not work. I don't know what is wrong with my VBA code.

Your help will be greatly appreciated. Thank you. GMSRE

 

[GMSRE]

Hi Xproterg,

I posted the database as requested 8/1/11. Did you have a chance to look at the database see why the county combo box will not populate the commmunity combo box? Thank you.

GMSRE