Is there a way to auto-populate fields?

[balderman]

I have a Customer's table that has customer contact information such as last name, first name, address, phone number, etc....I also have a work orders table that I am using to create a form for a work order. I am going to create a form for the customer profile (the customer contact information) and I will have the work orders for each customer linked to the customer profile. My question is, is there a way to auto-populate the customer information from the customer profile that is needed on the work order? For example, if it is a new customer, we would enter the customer's information in the Customer Profile form and then somehow from there we would go to generate a work order, now instead of retyping the name, address and phone number all over again, is there a way where if we just type in one piece of data, like the phone number, it will auto populate the name and address fields and then all we would have to do is fill in the other information that is required on the work order form? This information needs to be saved, not for display only.

Or, if it is an existing customer that we already have in the database, we would just type in their phone number (which I am using as the primary key) and it would auto populate the rest of the customer information?

Thanks in advanced for your help.

[June7]

If you do data entry on a form/subform arrangement, the customer info would display on the main form and the work order entry would be in the subform.

Phone numbers and names make very poor unique identifiers. Phone numbers can change and names can be shared (many John Smith).

Suggest autonumber as PK. This field and its corresponding foreign key field in work order table serve only to link records. It has no meaning to users. Use other fields to do search and filter - review http://allenbrowne.com/ser-62.html.

If you need to print a work order document, build a report that uses a query that joins the tables as the report RecordSource.

[pbaldy]

If needed:

BaldyWeb - Autofill

[balderman]

OK, I will look into this to see if it works. Regarding the PK, the reason I used the phone number is because I already have an auto number field for the work order # and it says you can't have more than one auto number field in a table....

[balderman]

Ugh! I can't seem to get the relationship of the customers table and the work orders table to be a one-to-many relationship. I guess I don't understand it very well. I created an ID field in the customer's table and I made it the PK and I made the work order number the PK in the work orders field but when I do the relationship it still says it's only one-to-one which won't then allow me to create a form/subform.

Here are my fields--

Customer's Table: ID, Last Name, First Name, Phone Number, Street Address, City, State, Zip Code, E-mail, Fax Number, Business Name, Alt Phone 1, Alt Phone 2

Work Orders: At the top I have Yes/No boxes to indicate the type of service being done and other pertinent information that the technicians need: Service, Contract, Res HVAC Con, Com HVAC Con, Res GEN Con, Com GEN Con, A/c, Heat, Electric, Refrigeration, Generator, Residen, Comm, Ind, Trailer, House, New Customer, Quote, Incomplete, Paperwork needed...the other fields are Date, Work Order No, Appt Date, Last Name, First Name, Phone Number, Street Address, City, State, Zip Code, Phone Number, Alt Phone 1, Alt Phone 2, Equipment Info, Directions, Complaint, Comments

I have taken several access classes and worked with it before but now I just feel like an idiot I guess, nothing is working the way I want it to and I guess I need help as if I know nothing lol

Thanks in advanced.

[Minty]

Remove all the customer information from your work order table and add one field CustomerID.
This is where you store the ID field from your customers table. Don't store it all twice.

I would also suggest changing the ID field name to CustomerID - so you know which ID field you are referring to.
Date is a reserved word and an access function - change it CreatedDate or DateEntered or similar.

I would also have a separate table of work types, and store those in a table (tblWOWorksRequired?) associated with the Work Order ID, rather than tick boxes.
If you start doing another type of work you will have to redesign your queries forms and reports to accommodate it.

[pbaldy]

See if this helps:

Fundamentals of Relational Database Design -- r937.com

[balderman]

The reason I have the tick boxes is because that is how my boss wants it....he wants them to show up at the top of the work order when printed out.....he wants it to be as easy as possible for others to understand and use as well because most of the people in the office here are not very computer savvy....So you are suggesting that I create them in a separate table and maybe create a list box in the work order form?

[balderman]

OK another issue....I have the Work Order No field as Auto-number in the work order table so I can't also add the customerID field with auto-number as well....what should I do?

[Minty]

You can still have them appear on the top of the work order paperwork, but that's not how you would store them.
You could concatenate the selected work types into a single string, or make a subreport listing them etc. etc. You are able to retain the "look" of most paper forms, but without the pre-printed restrictions that apply.
So yes by all means have a list box that only shows the selected work type stored against the WorkOrder ID, If you only ever use 3 or 4 max per job then make it big enough for 5 or 6 and the rest of your printed report can go around it.

It normally only takes a bit of lateral thinking to make things look good on paper.

[Minty]

OK another issue....I have the Work Order No field as Auto-number in the work order table so I can't also add the customerID field with auto-number as well....what should I do?

It's not an autonumber in the work order table. It's what is called a Foreign Key (FK), you store it as a number. You would normally select it from a drop down combo on a form and its record source would be the Customers table.
The combo would display the Customer name, and possibly a zip code, but you only store the FK - the CustomerID in the WorkOrder Table.

[balderman]

ooohhhh ok....I see what you are saying now....I thought they had to be the exact same data type in order to create the relationship! Ok, let me go do some messing around and see how much more I can screw this up LMAO! Thanks!

[June7]

Autonumber is a long integer type so the corresponding foreign key field must also be long integer type.