Joe P.
First off you have probably guessed that I am in database 101 design. One of my strenghts I beleive is to ask the experts and not move forward until it is working properly. I have spent some time researching basics of databases and have discovered that I have first made a mistake in saving the same information in different tables. with that said I can explain what I am trying to acheive.
I. Customer list with a combo box for (City,State and Zip)
A. I wanted to be able to autopopulate the city, state and zip from a drop
down box from "City" then autopopulate state and zip.
B. How I have done that so far is create a combo box bound to "cboCity"
C. In the text box city and state I used =lablename.column(n)
D. In form view the correct information is displayed but the state and city
is not saved in the table.
II. What I then wish to do is make other forms that pull the customer
information and insert it as a combox box that I only have to click on the
customer name and the informaion is also autopopulated into it.
Thanks for your reply and any constructive adivce is appreciated
Gary
Ok Gary,
I'm going to pass along some concepts to you, that you're going to need to understand before we proceed any further.
1. When you create a customer list, there is going to be a Primary Key value for each of those customer. i.e. a Account number for each customer. You include things in the table that are going to be unique to that customer. Name, address, zipcode, phone, cell, email, etc.
2. if you have create a list of zipcodes with City and state already defined, then using the Zipcode as the Primary key, then you would simply store the Key value (i.e. the zipcode) in the customer table, and lookup the rest of the information. That way you don't have to store the value of the city and state in the customer table. you simply look it up by the zipcode.
So now that we have that in place, you may be asking, how do i get it to display the city and state for a given zipcode?
well that is a good question. there is a function within access called dlookup()
this function is used to populate information based on something that is entered (in your case) in another field.
the syntax for that is:
-
DLOOKUP("The name of the field you want to display","Table Name","Criteria to locate the particular item")
-
-
----------------------------------------------------------
-
example:
-
----------------------------------------------------------
-
me.City = DLOOKUP("cboCity","Zipcodes","[ZipCode]=" & me.zipcode)
-
Where me.city refers to the control on your form that is called City, and the me.zipcode refers to the combo box control that is named Zipcode.
then you simply use the onEvent Procedure BeforeUpdate, and it will update the field City.. and you do the same thing for the state, just replace the city field with the state field in both your form and your table that has the zipcodes in it, and you can display both city and state.
Hope that helps,
Joe P.
51
