Assign value to field from other concatinated fields

[SherryScrapDog]

I have a Main table where I store Country, State, County and Locality codes.
I have another Codes table where I store the names for these sets of codes.
I originally tried to use the same codes in the Codes table and made the set
of 4 fields the primary key. But then I couldn't figure out how to relate
the 4 fields in the Main table with the 4 fields (primary key) in the Codes
table. So I went to plan b. I concatinated the codes together into one
field in the Codes table (I entered them this way). In my Main table, I
created a new field called 'Code' and related it to the Codes table. I used
a query to populate the new field with the concatinated 4 fields and this
worked like a charm (I'm dealing with converting an old database into Access,
so I have records to start with). Now, when they enter or update a record
(on a form), I would like this field to automatically update with the
concatinated fields. I have found questions similar and have tried various
things and nothing happens. My code field remains blank. Any help or
suggestions? I'm not very experienced with macros or VBA (which are some of
the things I've tried, along with just putting =Country&State ect. into the
control source), so specific instructions might be needed for me to
understand exactly what to do.
Thanks so much if you can help! Sherry

 

[Dymondjack]

I could be completely wrong here (I don't claim to be an expert at this), but
it seems as though the real problem might be the relational design of the
tables and fields you are trying to use.

According to general design practice, your Country, State, County and
Locality should all be in separate tables, with the Code and Description for
each stored in each ones respective table. Example:

tblCountry has these two fields
Code (Primary Key)
Description (United States of America)

tblState would have these three fields:
Code (Primary Key (the State Code, ex NY))
CountryCode (Foriegn Key for which Country the State is in)
Description (New York)

tblCounty would have these three fields
Code (Primary Key)
StateCode (Foriegn Key for which State the County is in)
Description (Albany Country)

At this point, you would set a 1toMany for Country(1) to State(Many),
State(1) to County(Many), ect ect....

Like I said I'm no expert here by any means, and I'm not sure what it is
exactly you are trying to accomplish, but this would be a standard way of
setting up relational fields, rather that putting all of the information into
1 or 2 tables and trying to relate more than 1 field in each. Thought it can
be done like that, I try to make it a rule for myself to use only 1 field for
a primary key.

The beauty of this is, If you have a ctlCounty on your form and someone
selects the county, you dont need to have them choose the State or Country
also, because access can find that information based on that fact that there
is only one of each for each county available.

 

[SherryScrapDog]

Thanks for the response! This is what I found in the data: there could be a
county of (for example) 'AL' and the actual county name could be Allegan
county if the State is MI, or it could be Algers if the State is IN. The
country of USA has a state of CA (California) and the country of CAN has a
state of CA (Carleton). So I quickly found that a straight match for each
field would not work. (This is a geneology inventory system and references
different countries and states.) I'm not sure if the method would support
this (and you can correct me if I'm wrong!). For sure, selecting a county
and populating the Country and State would not work because it could be
multiple choices. Any other suggestions??
thanks lots! Sherry

 

[SherryScrapDog]

I got this to work. For new records, I finally got an expression builder to
work for the default property. For changed records, on the 'Before Update'
for the form, I used code for an event that says Me.Code=Me.Country &
Me.State & Me.County & Me.Locality. Now my records update automatically.
I'm thinking maybe the default property on the Code field might not be
necessary because the Before Update event probably does the same thing. I
just haven't tried removing the default yet to test. If anyone sees anything
wrong with this or has a better way, I'd love to hear!
thanks, Sherry