Combo boxes based on a cryptic code

[So Call Me Crazy]

I have a table that contains a cryptic code that represents a state/county
combination. On my form, I want to display combo boxes that allow the user
to pick the state and then the county in a non-cryptic form that will then
build the needed code. Also, if a customer is on an existing record, what I
would like is the non-cryptic state and county in text boxes that are
actually in front of the combo boxes.

I had this working with a query as the record source for the entire form. I
ran into primary key issues that I suspected were the result of the tables in
the query when I was trying to add a new record to the main table. So, now
I'm trying to accomplish this with the main table as the record source for
the form, and just coding the control source for the combo boxes.

Thanks!

 

[Evi]

Please could you explain what you mean by ' I have a table that contains a
cryptic code that represents a state/county'?

"On my form, I want to display combo boxes that allow the
user to pick the state and then the county in a non-cryptic form that will
then
build the needed code. "

Do you mean that you want your state combo box to filter your country combo
box so that it only shows the country in that state?

If yes, see below
http://www.simply-access.com/UsingComboBoxFilterAnotherComboBox.html

Evi

 

[So Call Me Crazy]

The main table contains a field called a "locale code". The locale code is a
combination of a state abbreviation and a numeric value that represents a
county in that state. For example, El Paso county in Colorado is stored as
"CO041". The state portion is not a problem, however, I don't expect the
user to know that 041 represents El Paso county. So, I want them to be able
to pick from a couple of combo boxes. First pick the state -- Colorado.
Next pick the county -- El Paso. My form should then be able to build the
locale code for the table -- CO041.

I actually have text boxes in front of the combo boxes to display the
existing data from a record, or the result of the combo box selection.

Also, if on an existing form, I want those text boxes to display the result
of the translation of CO041 to Colorado, El Paso county.

Thanks!

 

[Evi]

SCMC, I'm not sure if I need to increase my Ginseng ration or you just have
the knack for making something simple, sound complicated.
You SEEM to be saying that a locale code is a combination of a code for the
name of the state + a number code for the name of a County in that state. .

*If* this is true, then the Locale Code would not normally be a field in
your table - it could be produced by a query.


If a County is always only in one State, here is the database structure you
will
need is:

Have a table called TblState with these fields

StateID (primary key, autonumber)
State (Colorado)
StateCode (CO)

A Table called TblCounty with

CountyID
County (El Paso)
CountyCode (041)
StateID ( a number field, linked from StateID in TblState)

In your form (FrmCode), add a combo based on TblState containing StateID,
State and StateCode - in that order.
Click on Properties and on the 'Other' tab, next to Name, type
cboState

Add a second combo based on TblCounty containing CountyID, County,
CountyCode, StateID.
Name this combo cboCounty. While you are in Properties, click on the Data
tab and just right of Row Source to open up a Query.

Under StateID, in the Criteria row, type

Forms![FrmCode]![cboState]


(this ensures that cboCounty is filtered by whatever is in cboState)

Add a text box to your form/

in it, type

=cboState.Column(2) & cboCounty.Column(2)

(this combines the contents of the 3rd columns of both combos - ie the
codes)

Click back on cboState and in Properties, on the Data tab, next to After
Update, choose Event Procedure and open a code page.

Just above where it says

End Sub

type

Me.cboCounty.Requery

Evi