Autofill

[ukmale65000]

I only have one table in my database (customers), i am trying to design a form that when i select a customer name from a combo box then the address box and post code boxes on the form autofill from the table. I have searched all over and there seems to be a way of doing it if you have two tables and create relationships but no advise if you only have one table. As there are only a few fields within the table i.e. customer name, customer address, customer post code, collection address it seems a lot of work to create 4 separate tables. Is there a solution?

[Bob Fitz]

If the form is bound to the customers table then you could use the wizard to create a combo box on the form that will find the record selected in the combo box. If the form is unbound you could include all the needed fields in the SELECT statement used to populate the combo box but hide them if you wish and then set the Control Source property of each text box to the appropriate column of the combo box.

[RuralGuy]

Try adding a ComboBox to your form with the cbo wizard and select option #3.

Oops...a little too slow.

[ukmale65000]

Ok so i have created a combo box for the dealers name, now do i need to create a text box underneath this and use the select option mentioned by Bob Fitz to show the delaer address and if so where do i place this select function is it vba code and if so is it placed on one of the functions of the text box i have mentioned. Im so new to this its hard to understand when someone explains stuff technically, even so i am very grateful for the help

[Bob Fitz]

Ok so i have created a combo box for the dealers name, now do i need to create a text box underneath this and use the select option mentioned by Bob Fitz to show the delaer address and if so where do i place this select function is it vba code and if so is it placed on one of the functions of the text box i have mentioned. Im so new to this its hard to understand when someone explains stuff technically, even so i am very grateful for the help

Is the form bound to a table/query? As this probably sound like "technical jargon" to you, just take a look at the forms "Record Source" property. If it is blank, then the form is unbound, if it is not blank, the form is bound to the table or query stated in the property setting.

[RuralGuy]

Did you use my suggestion? Did you select Option #3?

[ukmale65000]

Ok when i have the form bound it works very well so thanks for the info, now i have a slightly different problem. I have two tables Collect and Deliver when i originally followed your instructions the form was bound to collect and the autofill as i said worked. Now on the same form i wanted to add a set of boxes for delivery address, however because i am bound to the collect table the autofill obviously dosent work on the delivery side because as far as i know you cant bind to two tables, so here is my question. Do i extend the collect table to have delivery fields in it and then create two combo boxes one for collection one for delivery and use the working method, or do i unbind the form and somehow get it all to work another way? Hope this isnt to confusing.

[ukmale65000]

I have included the database if that helps anyone. The problem im having is with the "Enter New Job" form, you will see ive tried to apply the help ive been given above, but im getting more and more confused. Now if i enter details on the collection side it all works fine, then when i pick a dealer from the combo box on the delivery side, it fills in the details on both the collection side and the delivery side and vice versa, hopefully including the database will help.

[Bob Fitz]

Can you post a copy in A2003 mdb format

[ukmale65000]

Hopefully this is the format you asked for

[Bob Fitz]

Yes it is and I can open it fine. Problem is I don't understand what its purpose is. Can you tell us what the db is actually for.

[ukmale65000]

The database is for a car delivery company. We collect from dealer a and deliver either to dealer b or a customers house. We record the salesman requesting the move plus we record mileage fuel used and the driver allocated to the job. As part of admin we also record if the job is closed if the driver has been paid and if the job details have been entered on the accounting package.
Hope this is clear

[Bob Fitz]

I'm not sure that your db structure is quite right. I will try to look at it more closely tomorrow.

[ukmale65000]

Not sure if this will make things clearer, i have in excel what i am trying to convert to a database

[Bob Fitz]

Unfortunately I have not have as much free time today as I thought I would. However, I did look at the db you posted. IMHO your tables need to be changed and some relationships between them defined. The attached db is a copy of your db, in which I have created some new tables with relationships and forms. Open the form "frmTransfers" and see how it populates text box controls on the form with the dealers address details. Please post back if you have any questions.