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
|
|||
|
|||
How Do I Use a Combo Box to Update Two Form Fields at the Same Tim
Currrently I am using the same combo box select query in a form and varying
the bound colums to pull information out of one database to enter it into two separate fields in another database. Is it possible to use a select query for a form that will fill in the current field with one value and automatically fill in the second field with its companion value. Example: "part number 12345" and part name "gold widget". I would like to run the select query on the part number and have the part number added to the part number field and without running the query a second time having the part name automatically added to the part name field. The part name field is the next field on the form. Access 2003. |
#2
|
|||
|
|||
How Do I Use a Combo Box to Update Two Form Fields at the SameTim
JBledsoe,
If you mean the form is based on a table which includes 2 fields where there is data in another table, then it is almost certain that this is not a good table design. There should be only one field in the second table to identify the related data from the first table - this is one of the principles of relational database design. If you have part number 12345, you will always know that this refers to gold widget by reference to the first table, and it is invalid to redundantly re-store the part name in the second table. If, on the other hand, you want to enter the key data into the second table via the form, and then have the related data from the first table *displayed* on the form, this is another matter. There are a number of approaches to this. Some of them are discussed in this article... http://accesstips.datamanagementsolu...biz/lookup.htm -- Steve Schapel, Microsoft Access MVP JBledsoe wrote: Currrently I am using the same combo box select query in a form and varying the bound colums to pull information out of one database to enter it into two separate fields in another database. Is it possible to use a select query for a form that will fill in the current field with one value and automatically fill in the second field with its companion value. Example: "part number 12345" and part name "gold widget". I would like to run the select query on the part number and have the part number added to the part number field and without running the query a second time having the part name automatically added to the part name field. The part name field is the next field on the form. Access 2003. |
#3
|
|||
|
|||
How Do I Use a Combo Box to Update Two Form Fields at the Same
The support table lists many parts (records) by listing each part number in
one field and part name in the next field of the same record. I want to combo list the records in this support table via a select query to enable me to enter the record information (part number and part name) into two fields of a second table (with the same names as the support table uses) at the same time rather than having to do it separately for each field. I hope my nomenclature is consistent here -- I know what I want to and hope I am expressing it clearly. The end goal is to be able to have the buyer with part numbers and part names all in the same record of the second table and to reduce the part associated key strokes by one half. -- JBledsoe "Steve Schapel" wrote: JBledsoe, If you mean the form is based on a table which includes 2 fields where there is data in another table, then it is almost certain that this is not a good table design. There should be only one field in the second table to identify the related data from the first table - this is one of the principles of relational database design. If you have part number 12345, you will always know that this refers to gold widget by reference to the first table, and it is invalid to redundantly re-store the part name in the second table. If, on the other hand, you want to enter the key data into the second table via the form, and then have the related data from the first table *displayed* on the form, this is another matter. There are a number of approaches to this. Some of them are discussed in this article... http://accesstips.datamanagementsolu...biz/lookup.htm -- Steve Schapel, Microsoft Access MVP JBledsoe wrote: Currrently I am using the same combo box select query in a form and varying the bound colums to pull information out of one database to enter it into two separate fields in another database. Is it possible to use a select query for a form that will fill in the current field with one value and automatically fill in the second field with its companion value. Example: "part number 12345" and part name "gold widget". I would like to run the select query on the part number and have the part number added to the part number field and without running the query a second time having the part name automatically added to the part name field. The part name field is the next field on the form. Access 2003. |
#4
|
|||
|
|||
How Do I Use a Combo Box to Update Two Form Fields at the Same Tim
On Wed, 20 Dec 2006 12:14:00 -0800, JBledsoe
wrote: Currrently I am using the same combo box select query in a form and varying the bound colums to pull information out of one database to enter it into two separate fields in another database. Is it possible to use a select query for a form that will fill in the current field with one value and automatically fill in the second field with its companion value. Example: "part number 12345" and part name "gold widget". I would like to run the select query on the part number and have the part number added to the part number field and without running the query a second time having the part name automatically added to the part name field. The part name field is the next field on the form. Access 2003. You should NOT be storing the part name redundantly in your second table AT ALL. Instead, set the Control Source of the second textbox to =comboboxname.Column(1) to see the second column (it's zero based) of the combo's rowsource query. John W. Vinson[MVP] |
#5
|
|||
|
|||
How Do I Use a Combo Box to Update Two Form Fields at the Same
JBledsoe,
Yes, I think I understood what you wanted from your original question. And thanks for the additional clarification. The point I am trying to make is that what you are wanting to do, while possible, is incorrect. The second table should not contain two fields which are the same as the fields in the support table. This is an invalid table design. The part name field should be removed from the second table. It is unnecessary and inadvisable. You can certainly have the data from both fields shown in your combobox's drop-down list. This combobox will be bound to the part number field in the second table. The goal then, is for the part name to be automatically shown on the form, based on the selection made in the combobox. As mentioned in the article, this can be done via either: - including the support table in the query that the form is based on - using a calculated field within the Row Source of the combobox, in order for the combobox to display a concatenated value that includes the part number and part name - using a DLookup() function in the Control Source of an unbound textbox on the form - referencing the Column property of the combobox in the Control Source of an unbound textbox on the form Hope that makes it clearer. -- Steve Schapel, Microsoft Access MVP JBledsoe wrote: The support table lists many parts (records) by listing each part number in one field and part name in the next field of the same record. I want to combo list the records in this support table via a select query to enable me to enter the record information (part number and part name) into two fields of a second table (with the same names as the support table uses) at the same time rather than having to do it separately for each field. I hope my nomenclature is consistent here -- I know what I want to and hope I am expressing it clearly. The end goal is to be able to have the buyer with part numbers and part names all in the same record of the second table and to reduce the part associated key strokes by one half. |
Thread Tools | |
Display Modes | |
|
|