Help!!! Autolookup

  • Thread starter Thread starter Mike Farmer
  • Start date Start date
M

Mike Farmer

I am only reposting my message to change the subject so that it is clear
that I am seeking help. I know that it takes time for a response. Thanks!!!

Mike

I am attempting to have certain fields automatically filled in (City,
State) when I enter the zip code. I have a number of tables that are
populated by the form and I also have a table that contains: zip code,
city, state, county. I've tried following the various instructions but
nothing works. I also want to be able to enter a doctors name and have
the form automatically fill in the designation and specialty. This
information is also contained in another simple table. No Primary Keys
used. Everything I read talks about "One To Many Relatioships" but
that does not seem applicable here as all of the specied info is one
table. Any help would be much appreciated.

Regards,

Mike
 
---------- Mike Farmer said:
I am attempting to have certain fields automatically filled in (City,
State) when I enter the zip code. I have a number of tables that are
populated by the form and I also have a table that contains: zip code,
city, state, county. I've tried following the various instructions but
nothing works. I also want to be able to enter a doctors name and have
the form automatically fill in the designation and specialty. This
information is also contained in another simple table. No Primary Keys
used. Everything I read talks about "One To Many Relatioships" but
that does not seem applicable here as all of the specied info is one
table. Any help would be much appreciated.
Click to expand...

Mike,

this goes usually like this (example for zip code):

1. All tables should have a primary key, preferably an autonumber, to
uniquely identify each record. After all, doctor names can be
duplicated, IIRC zip codes also.

2. Create a query to select the primary key, zip code, city, state and
country (in this order) from the zip table.

3. Create a combo and set the RowSource property to the above query.
You would further need:
Bound column: 1 (that's the primary key)
Column number: 5
Column widths: 0;1;1;1;1 (or whatever appropriate, only the first
column should remain 0 which makes it invisible). This way the combo
will display the zip code as the first (choosable) column.
LimitToList: Yes

4. create text boxes for city, state and country. Set Enabled to No.

5. Set the combo's AfterUpdate event property to [Event procedure].
Click on the ellipsis at the right and write the following code in the
procedure:

With Me
'Check if user choose something in the combo
If Not IsNull(![NameOfCombo]) Then
'A zip code was chosen
![NameOfTextBoxCity] = ![NameOfCombo].Column(1)
![NameOfTextBoxState] = ![NameOfCombo].Column(2)
![NameOfTextBoxCountry] = ![NameOfCombo].Column(3)
Else
'The combo is empty
![NameOfTextBoxCity] = Null
![NameOfTextBoxState] = Null
![NameOfTextBoxCountry] = Null
End If
End With

HTH

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
I am only reposting my message to change the subject so that it is clear
that I am seeking help. I know that it takes time for a response. Thanks!!!

Mike

I am attempting to have certain fields automatically filled in (City,
State) when I enter the zip code. I have a number of tables that are
populated by the form and I also have a table that contains: zip code,
city, state, county. I've tried following the various instructions but
nothing works. I also want to be able to enter a doctors name and have
the form automatically fill in the designation and specialty. This
information is also contained in another simple table. No Primary Keys
used. Everything I read talks about "One To Many Relatioships" but
that does not seem applicable here as all of the specied info is one
table. Any help would be much appreciated.
Click to expand...

Relationships DO matter here. You're missing a key point about how
databases *work*.

The City, State, and County fields SHOULD NOT EXIST in this table.
The Specialty and Designation fields SHOULD NOT EXIST in this table.

You are making the very common assumption that you must have all the
information you want to see as fields in one single table. This
assumption is incorrect!

Instead, store *JUST* the Zip, and just the Doctor's primary key ID
(yes, every single table should have a primary key), in this table.
Then create a Query joining your table to the zipcode table by Zip,
and pull up the city and state from the zipcode table; join the
Doctors table by DoctorID, and pull up the doctor's name, specialty,
etc. from the Doctors table. This Query can be used as the source of a
Form or a Report; you don't need to store the information redundantly
in two separate tables.
 
John, thanks for your response. With lots of help from another expert,
I've gone in a different direction and I have been able to get
autolookup to work.

I'm still puzzled by your "should not exist" comments. maybe I failed to
explain myself properly.

The data base I'm working on has many tables, one of whch is a "Zipcode"
table containing over 4,000 zip codes along with the corresponding city,
state & county.

I have a "form" with many fields which polulates other tables. Three of
the fields are City, state & zip. When I go to fill out the form and I
come to the "address" information, I wanted to be able to enter a zip
code and "grab the city & state from the table. The same with the
physicians information. I wanted to be able to enter a physician's nane
and then grab the "specialty & designation from the physicians table. I
probably confused things by mentioning the the "zip codes" and the
"doctors specialty and desination" fields. These are two separate
tables, neither containing the same information.

Thank you for your kindness in responding. Now I have a new problem for
my next post.

regards,
Mike
 
Emilia, thank you so much for you help. The solution worked perfectly,
however another problem has now cropped up. I'll post that separately,

Regards,

Mike
Emilia Maxim wrote:

Mike,

this goes usually like this (example for zip code):

1. All tables should have a primary key, preferably an autonumber, to
uniquely identify each record. After all, doctor names can be
duplicated, IIRC zip codes also.

2. Create a query to select the primary key, zip code, city, state and
country (in this order) from the zip table.

3. Create a combo and set the RowSource property to the above query.
You would further need:
Bound column: 1 (that's the primary key)
Column number: 5
Column widths: 0;1;1;1;1 (or whatever appropriate, only the first
column should remain 0 which makes it invisible). This way the combo
will display the zip code as the first (choosable) column.
LimitToList: Yes

4. create text boxes for city, state and country. Set Enabled to No.

5. Set the combo's AfterUpdate event property to [Event procedure].
Click on the ellipsis at the right and write the following code in the
procedure:

With Me
'Check if user choose something in the combo
If Not IsNull(![NameOfCombo]) Then
'A zip code was chosen
![NameOfTextBoxCity] = ![NameOfCombo].Column(1)
![NameOfTextBoxState] = ![NameOfCombo].Column(2)
![NameOfTextBoxCountry] = ![NameOfCombo].Column(3)
Else
'The combo is empty
![NameOfTextBoxCity] = Null
![NameOfTextBoxState] = Null
![NameOfTextBoxCountry] = Null
End If
End With

HTH

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
Click to expand...
 
Back
Top