Autopopulate then save in a table

[GLEberts]

Hi,
I would like to ask for some direction on what would be the best way to
(1) Autopopulate a combo box to text boxes - used for a customer list
(2) Then have the information saved in the table?

NOTE: I have already tried this method with 50% success - it works but does not save to the table.
control source of the text box - comboboxname.co lumn(n)

First Question? What is the best method to to autopopulate then save to a text box?
Second Question? Should I run the combo box off of a query?

Thanks for your help
Gary

[PianoMan64]

Hi,
I would like to ask for some direction on what would be the best way to
(1) Autopopulate a combo box to text boxes - used for a customer list
(2) Then have the information saved in the table?

NOTE: I have already tried this method with 50% success - it works but does not save to the table.
control source of the text box - comboboxname.co lumn(n)

First Question? What is the best method to to autopopulate then save to a text box?
Second Question? Should I run the combo box off of a query?

Thanks for your help
Gary

Gary, I need to have more information as to why you're wanting to do what you describe. I can't understand under any reasoning of mine, why that would ever be considered as an option?

Can you explain what it is that you're trying to do? Also include any table and query structures that you've created already so I'm able to re-create your senerio.

Thanks,

Joe P.

[GLEberts]

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.colu mn(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

[PianoMan64]

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.colu mn(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.

[GLEberts]

Good Morning,
I appreciate your reply with an explaination instead of just script. I showed me the "why" instead of just the "how"

This is what I have done.

(1) Table - "tblZipcode s" - which has "Zipcode" as the (PK)
(2) Combo box on the form - "cboZip" with the control source set to "Zip/Providence that is bound to the form.
I am having a challange putting the rest together without getting an error.

Next I made a unbound txt box for the city
The next step is what I believe I am doing incorrect.
Where to insert the code "DLOOKUP"
I am putting it in the control source in the text box "City"
Then clicking on "EventProcedure " in the BeforeUpdate
With the info above could you please steer me in the correct direction.
I really am starting to enjoy this and have looked into some classes at a local community college. I can see already that in order to do this you need to have some type of prfessional education to understand the concepts of what to do. To start up blind like I am doing is nuts.
Thanks for your Help!
Gary

[PianoMan64]

Good Morning,
I appreciate your reply with an explaination instead of just script. I showed me the "why" instead of just the "how"

This is what I have done.

(1) Table - "tblZipcode s" - which has "Zipcode" as the (PK)
(2) Combo box on the form - "cboZip" with the control source set to "Zip/Providence that is bound to the form.
I am having a challange putting the rest together without getting an error.

Next I made a unbound txt box for the city
The next step is what I believe I am doing incorrect.
Where to insert the code "DLOOKUP"
I am putting it in the control source in the text box "City"
Then clicking on "EventProcedure " in the BeforeUpdate
With the info above could you please steer me in the correct direction.
I really am starting to enjoy this and have looked into some classes at a local community college. I can see already that in order to do this you need to have some type of prfessional education to understand the concepts of what to do. To start up blind like I am doing is nuts.
Thanks for your Help!
Gary

yes, you can simply put the DLOOKUP in the control Source of the control, just make sure that you have a conditional statement so that it doesn't show #ERROR before you populated the Zipcode field with a valid zipcode from the ZipCodes table.

Example that will go into the control source for the city control

 
=IIF(IsNull[ZipCode]),"",DLOOKUP("City","Zipcodes","[Zipcode]=" & [Zipcode]))

just paste that in to the city control.

Same with the state as well:

 
=IIF(IsNull([Zipcode]),"",DLOOKUP("State","Zipcodes","[ZipCode]=" & [Zipcode]))

that's all you have to do. You don't need to do any event updating. It will take care of it when you populate the zipcode field.

Hope that helps,

Joe P.

[GLEberts]

Thanks for you help - this allowed me to move forward.
Thanks
Gary