Tables with auto fill

[cdcsolutions]

Good day! I know this should be easy but for the life of me am stuck on how to link these two tables. This is what I would like to do:

Table one Eg. Vendor, Service Type, Location
Table two, Eg. Date, Store, Cost, Vendor, Service Type, Location
What I would like is to create the table so that when creating a form the following occurs: When I enter the Vendor I would like to have automatically fill in the Service Type and Location.
I created two tables as listed above and created a combo box which shows the Vendor and associated info but how do I make the Service Type & Location populate when choosing the Vendor. I want to ensure that when the Vendor is selected you cannot change the Service Type or Location. Help? Thanks!

 

[pbaldy]

You mean in a form? I'd do this:

http://www.baldyweb.com/Autofill.htm

I never let users work directly in tables or queries, just forms and reports.

 

[cdcsolutions]

Hi! You are absolutely correct in saying that I should be doing this in a form. I'm frustrated because it's been awhile since I've done this and I know it can be done.
In one table I have I have several fields that require data input each time. In another table I will list an Item #, Model, Make. What I want to do is create a form that will allow me to enter the Item# and then the associated Make and Model will populate in the next two fields. I am creating this in Access 2003 using the Wizard for the Form. Please give me light on how to go into Design mode and link eg. Item 123 and have the proper or associated Make and Model automatically enter from the table or should I be creating another form to enter that info then somehow link? I hope you can understand what I am trying to do. If I need to do something in the tables, please advise. It's the form I want to create for users to input. The Item#, make and model will be used in more than one entry but I don't want that info to change. Help? Thanks a bunch!

 

[pbaldy]

Make sure your combo row source returns all 3 fields, then follow the directions in the link.

 

[cdcsolutions]

I just got back to this wonderful creation. A break well needed. I checked the combo box and all is good. Let's start again. I honestly think it's something quite simple but I'm making it worst than what it is.
Table one (Named Employees) ID# Sin# First Name, Last Name Phone #
Table two (Named Job) Id#, Job Type, Rate of Pay, Start Date
Table three (Named Work site) Work Site, Town, Supervisor,Sin#
(in table three I created a combo box that when the sin# is selected, the first name, last name & phone number show up but only the SIN stays, the rest is hidden)
What I want to do is create a form and will fill in the following:Work Site, Town Supervisor. When I select the SIN#, I want the associated First Name, Last Name, & phone number to also come up in the fields. I can get the combo box coming up with the info but how do I get all the info to stay and in the fields I want.

I just don't want to retype the First Name, Last Name, Phone etc. every time I select the sin#. A simple example for my needs is a store that types in your phone number and all the associated data pops up such as your address. Help! Miss Frustrated. Thanks.

 

[pbaldy]

Did you notice the link I posted above? That's exactly what it tells you how to do.

 

[cdcsolutions]

I don't know where to find the link you've sent??

 

[pbaldy]

This one, in post 2:

http://www.baldyweb.com/Autofill.htm

 

[cdcsolutions]

Yes, I did receive it. I am going to go through your steps and see if I can get my end to work. Thanks!!

 

[Pat Hartman]

The attached sample database shows you three methods for accomplishing your goal. Two methods just show the data and the third method actually copies the data to the form's RecordSource. Let me reiterate, option 3 is VERY RARELY used. You NEVER want to duplicate data in a properly normalized database. However, there are reasons for copying data. A typical example occurs in an Order Entry application. The unit price is stored in the product table but when we create an order item detail record we want to copy the price -AT THAT POINT IN TIME. If we don't do this, then we can never create an invoice or a historical report because when we join to the product table we will always get the current price rather than the price that was effective when the order was placed. The Order entry application actually has a second occurance of this type of "copy". The customer record contains the customer's billing address. When he places an order, we need his shipping address. Since the addresses are commonly the same, we copy the billing address from the customer table and store it as the shipping address in the order table unless the customer wishes to ship to a different location. This just saves typing for the order entry clerk.