Field Auto Populate

[Glenn Heavens]

I have an Access Database with two tables. In Table 1 I have Company
Name and Vendor number and other information. In Table 2 I have a Company
Name using a Lookup in a drop down box, and that works. But what I want is
when a user selects the Company name in Table Two I want the Vendor number
in Table 2 to auto populate with the number associated with the Company Name
in table one. I do not wish the user to have access to Table 1 as is will
be locked. The user will not be able to add a company . Does anyone have
any ideas?
I am using Microsoft Access 2002 (10.6501.6626) SP3. Thanks in Advance

 

[Ken Snell]

Do this with a form. Do not work directly in tables. And do not use a Lookup
option for a field in the table....use a combo box on the form instead.

Forms give you this ability to "auto populate" -- tables don't.

See this article on The ACCESS Web for how to do this on a form:
http://www.mvps.org/access/forms/frm0058.htm

 

[Steve Schapel]

I agree with Ken. But in addition, it is not correct to have both the
Company Name and Vendor Number in both Table 1 and Table 2. Assuming
the Vendor Number is the primary key field in Table 1, then Table 2
should only have the Vendor Number field as a foreign key to identify
the related Company record, and you should therefore remove the Company
Name field from Table 2. Hope this makes sense.

 

[Ken Snell]

Good point, Steve!

 

[Glenn Heavens]

Steve:

I have to have this type of setup within the database, as the people who
are going to be using it do not know the vendor but do know the company
name. How would you associate this properly. I want the Vendor field in
Form 2 for several reasons. Is there another way to do this?
Thanks in advance

 

[Steve Schapel]

Glenn,

In designing a database, it is prudent to keep the table structure, and
user interface questions, as separate as possible, as quite different
principles apply. My comments were related to the table design. Yes,
when it comes to your form, you will want to have the Vendor Number and
the Company Name. One way this is normally done is to have the Table 2
Vendor Number field bound to a combobox on Form 2. The Row Source of
this combobox is Table 1 (or a query based on Table 1), with its Column
Count property set to 2 so you can see both the Vendor Number and the
Company Name when you drop down the combobox list on the form. There
are many ways to set it up from here, depending on various
circumstances, but here's one... when the selection is made in the
Vendor Number combobox, from the list that shows both the number and the
name, it is the Vendor Number that will show in this control, and then
have another unbound textbox, with its Control Source set to:
=[Vendor Number].[Column](1)
.... which will then display the Company Name for the company selected in
the Vendor Number combobox.

Another approach is to base the form on a query which includes both
tables, joined on the Vendor Number field from both, in which case the
Company Name field from Table 1 is available for display on the form,
according to the Vendor Number entered. There are some considerations
with this approach, for example make sure it is the Vendor Number field
from Table 2 not Table 1 which is included in the query and form, and
also need to make the Company Name control on the form Enabled = No and
Locked = Yes.

In other words, the goal is to have the tables structured according to
relational design principles, and normalisation principles, and then
design the form to provide a user-friendly functionality.