Adding a combo box 1

[oliver10]

The director of our IS department has given me an existing database that someone made but no longer works here. What he wants is to add zip codes to the existing state_lkup table- when the user selects a state the second action will place the zipcode corresponding to this state to appear in the zip section. I have drawn a blank - and need a suggestion on how to succeed in this action.
Thanks, Patti

 

[Psychpt]

I'm not sure what you're trying to do.
There are many Zip codes for each state.
Do you want that after a state is selected only the right zip codes will be available to choose from? or
Do you want once they select a zip code the proper state will automatically fill in?

Either way the easiest would be to have a table with zip code and state.

HTH

PsychPt@Hotmail.com

 

[tlbroadbent]

Patti,

Starting with these assumptions:

1- You want to add a combo box that will show all the zip codes for a state that the user has selected.
2- The databse contains a table with zip codes and states.
This really should be separate from the state lookup table.
3- The states are listed in a combo box on the lookup form.
The user can select the state from the drop down box.
This combo box is named combo1. The form is named StateLookup.

Using query designer, create a query that selects State and Zipcode from the zipcode table. On the criteria line under State add forms!StateLookup!combo1. Save the query as qListZipByState.

Add another combo box. Let's call it combo2. The record source for this combo box will be the query, qListZipByState.

Add an Event Procedure to combo1 in the After Update event. Events are found in the combo box properties under the Events tab. The Event Procedure should be a subroutine in VB code. The sub routine your create should look like the following.

Private Sub Combo1_AfterUpdate()
Combo2 = ""
Combo2.Requery
End Sub

This code will cause the new combo box to requery the table using the newly selected state.

I've left out a lot of detail. If you are not familiar with Access forms, VB code, event procedures and/or queries, I recommend reading about them in Access help so you can understand what I've just said. Terry

http://members.home.net/tlbroadbent/index.htm