If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Field Auto Populate
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 |
#2
|
|||
|
|||
Field Auto Populate
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 -- Ken Snell MS ACCESS MVP "Glenn Heavens" wrote in message news:Qp6uc.615$pX3.474@clgrps12... 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 |
#3
|
|||
|
|||
Field Auto Populate
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. -- Steve Schapel, Microsoft Access MVP Ken Snell wrote: 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 |
#4
|
|||
|
|||
Field Auto Populate
Good point, Steve!
-- Ken Snell MS ACCESS MVP "Steve Schapel" wrote in message ... 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. -- Steve Schapel, Microsoft Access MVP |
#5
|
|||
|
|||
Field Auto Populate
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" wrote in message ... 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. -- Steve Schapel, Microsoft Access MVP Ken Snell wrote: 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 |
#6
|
|||
|
|||
Field Auto Populate
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. -- Steve Schapel, Microsoft Access MVP Glenn Heavens wrote: 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 |
Thread Tools | |
Display Modes | |
|
|