Community archive

Ask something
Question Locked

Form Auto Populate Access 2007

0 helpful
2 replies

by frosttr

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I know someone out there has the knowlege/suggestions to help with this. There are probably answers in the forums, someplace. The problem with that is that I have to get this completed and tested before weeks end...and really don't have several hours to read through a lot of forums looking first for one that matches mine, then a response that answers my question.
PLEASE! Can someone out there provide me with an answer?
Ok guys, I know someone out there has a simple solution to this, and I'm at wits end how to do it. I'm actually pretty good in Access, outside of VBA. Just need a viable solution to this scenario.

I have a table with lookup values in several fields. This table in turn is in a form for data entry. Because of the amount of data to be entered, and to reduce typos, I need the fields in the form to auto populate with specific data.

The problem with this is that I have one field, which we'll call Region, and is a 1 to many relationship. So, if a user types in NA (North America), it should show values for U.S., CAN, MEX. Once they select the appropriate sector, it then filters down in another field for state selection.

For example...I select NA, I will see the choices for US, CAN, MEX in the sector field. I select US from that field, and I will then see a list of states specific only to the US in the states field.

As mentioned, all the fields listed above are in the table with lookup boxes, and the fields are in the form as drop down lists.

Can anyone help me to set this up in a simple fashion (without VBA!)?

Thanks much!

2 replies

by dave o

First, you will need to write some VBA, a whole 4 lines.

Second, do all your data entry on a form. Don't let users enter data directly into a table or they'll enter anything they can. With a combo box on a form, you can limit the data they enter to what is in your lookup tables.

It sounds like you already have the table to store the records you will be entering. You'll need 3 tables for your lookups: tblRegion, tblCountry, and tblState. The Region table needs only one field: RegionName. The Country table should have two fields: CountryName and RegionName. The State table should have two fields: StateName and CountryName. The tables should be split that way for lookups to work properly - when you pick a region, you want just related countries to display in the country combo box, and when you pick a country, you want just related states to show in that combo box.

On your form, put 3 Comboboxes named cboRegion, cboCountry, and cboState. The RowSource for cboRegion should be "tblRegion". The rowsource for cboCountry should be "Select CountryName from tblCountry where RegionName = [cboRegion]". This will return just countries for that region. The rowsource for cboState should be "Select StateName from tblState where CountryName = [cboCountry]". This will return just states for that country. Set LimitToList for each combo box to Yes so they use only your table data.

In order to make sure the combo boxes get updated if the user changes their mind, you need just a bit of code. Add an AfterUpdate event procedure for two of the combo boxes:

Private Sub cboRegion_AfterUpdate()
Me.cboCountry = "": Me.cboState = ""
Me.cboCountry.Requery
End Sub

Private Sub cboCountry_AfterUpdate()
Me.cboState = ""
Me.cboState.Requery
End Sub

That is crude and the bare minimum. But it will work. And I don't think you can get by with anything less. If you try to do everything from one table, you'll end up with records with a region of Africa and a country of Mexico and a city of Montreal. Not what you want!

This discussion is closed to new replies.