Help With Automation

  • Thread starter Thread starter Len
  • Start date Start date
L

Len

Hi folks,

Not familiar with Access but have been tasked to setup a "Repair" database.
The setup was pretty easy except:

On the form out of numerous fields three are essential
1)Zip Code 2)City 3)State

There are two databases:
Repairs & ZipCodes

Need to create a relationship and the proper controls to allow a user to
type in a 5 character Zip Code in the "Repairs" Form
then
Hit Tab
then
Have Access 2000 enter the City and State from the ZipCode Table when the
inputed numbers in the ZipCode field on the "Repair" Form match the 5 digit
set of numbers from the ZipCode Table!

I didn't think it would be so difficult to get a DB program to do what it
was designed to do!!

Ask me how to build a computer, configure operating systems and setup a
network and I'm fine... ask me to get a database to enter info in two
fields: City and State automatically and it seems like I'm lost.

Any assistance would be appreciated!

Len
 
Len said:
Hi folks,

Not familiar with Access but have been tasked to setup a "Repair" database.
The setup was pretty easy except:

On the form out of numerous fields three are essential
1)Zip Code 2)City 3)State

There are two databases:
Repairs & ZipCodes

Need to create a relationship and the proper controls to allow a user to
type in a 5 character Zip Code in the "Repairs" Form
then
Hit Tab
then
Have Access 2000 enter the City and State from the ZipCode Table when the
inputed numbers in the ZipCode field on the "Repair" Form match the 5 digit
set of numbers from the ZipCode Table!

I didn't think it would be so difficult to get a DB program to do what it
was designed to do!!

Ask me how to build a computer, configure operating systems and setup a
network and I'm fine... ask me to get a database to enter info in two
fields: City and State automatically and it seems like I'm lost.
Click to expand...

First, a direct answer.

One could use code in the AfterUpdate event of the ZipCode control on the
form.

Me!City = DLookup("City", "ZipCodes", "ZipCode='" & Me!ZipCode & "'")
Me!State= DLookup("State", "ZipCodes", "ZipCode='" & Me!ZipCode & "'")

However; there are two problems with this conceptually.

(1)
There is not a one-to-one correspondence between ZipCode and City. What
should the program do when the same Zip has more than one City matching?

(2)
Perhaps your table is a significant subset that actually does have a
one-to-one correspondence from Zip to City and it is not likely to change
down the road. In that case then there would be no need to even store the
City and State with each repair record as it would be redundant.

You would just use the DLookup() statements above directly in the
ControlSource of the two other TextBoxes to *display* the City and State
rather than storing the values redundantly with each repair record. You
could also do this with a subform or by adding the City and State to the
underlying RecordSet by basing the form on a query joining the two tables
instead of just the Repairs table alone.
 
Back
Top