MS Access question

Factual Questions
1

I need to figure out how to fill one form field in based on the value of another form field. Here’s the situation:

I have two tables: State and Region

The State table has: StateID (an autonumber), StateName, and RegionName (which is a lookup to the region table)
The Region table has: RegionID (an autonumber) and RegionName

Each state is part of one region, and a region can have many states. On my form, I want to be able to select a State from a drop-down box, and have the Region appear in another form field. I successfully created a drop-down box for the State field, but I can’t figure out how to fill in the Region field after selecting a state. Does anyone know how to do this? TIA!

–FCOD

2

Not that this’ll necessarily help, but why are you joining the tables based on the RegionName rather than the RegionID?

3

I’m not, but in Access it appears like that.

–FCOD

4

This looks like it might be somwhat similar to what you’re trying to achieve.

The example uses DLookup to retrieve the value, which is sometimes frowned upon as crude, but is quite appropriate when a single value is to be returned - if you were doing it the other way - selecting region and wanting a list of the states contained therein, then you could dynamically create a bit of JetSQL with the selected value inserted as one of the criteria.

5

The easiest way is to create a State combo box and a Region text box. The Region text box will refer to a column of the State combo box.

Combo box: Select the state from a multi-column combo box, and make sure the data source of the State combo box includes the Region as one of the columns. Make the Region column 0" wide so it doesn’t show. So the data source of the combo box includes both the State and the Region tables, with the following columns:

StateIDD;StateName;RegionName

With column widths: 0";2";0"

Region text box: For the region, just create a text box that refers to the Region column of the State combo box. It’s data source should be something like:

=cboState.Column(2) (Remember that the column counts starts with “0,” so the first column will be 0, the second will be 1, and so on).

Every time the user selects a state from the combo box, the text box will return
the region.

You’ll probably want to set the Locked property of the text box to TRUE and the enabled property to FALSE so the user doesn’t think he can type something into it.

6

I swear I can code better than I type. :smack:

7

Yorick’s solution is perfect if you don’t need to store the region code in the record. (You shouldn’t, if your tables are properly normalized, since you could always look it up in the tables for any query.) If, for some reason, you do, then you’ll need to leave the data source for that region field to the table column you need to store the data in, and put in some VB code to update it. In the On Update event for the state combo box, you would put:

Me.cboRegion = me.cboState.column(2)

That’ll store the region code value in your field every time the state value is changed.

8

Thank you, Poor Yorick! That’s exactly what I needed. I don’t need to store the Region again, as it’s already part of the State table. I’m actually quite knowledgable in database design and SQL, it’s just some of the Access stuff I have trouble with.

Thanks everyone!

–FCOD