combo box fill in

[kaypee19]

I would like to know how to fill in a combo box with certain information related to information chosen in a different combo box. For example, when filling out an address you select a state from a drop down box. If I select Pennsylvania from the state drop down box, I would like only Pennsylvania zip codes to show up in my Zip Code drop down box.

 

[CautionMP]

You can make this happen by using a SQL statement that references the current form to find the value for the WHERE clause as the RowSource for your combobox:

SELECT tblData.State, tblData.ZipCode FROM tblData WHERE (((tblData.State)=[Forms]![frmCurrentForm]![ComboBoxState])) ORDER BY tblData.ZipCode;

Keep in mind that with this method if you choose a state then pull down the Zip combobox and then change the state your Zip combobox will not update. To make the Zip combobox update everytime the state is changed create an AfterUpdate event for the State combobox similar to this:

Private Sub Locate_Template_by_AfterUpdate()
  With CodeContextObject
    .comboboxZip.RowSource = "SELECT tblData.State, tblData.ZipCode FROM tblData WHERE (((tblData.State)=[Forms]![frmCurrentForm]![ComboBoxState])) ORDER BY tblData.ZipCode;"
  End With
End Sub

Hope this helps