Entering data in 2 fields from 1 combo box showing 2 columns

[Guest]

I have a combo box pulling vendor name and vendor number. How can I have it
put the respective info. in 2 different fields on my form? PS. I still
don't know code so if the answer lies there, you need to tell me exactly
where to enter any code. Thanks.

 

[Guest]

In the After Update event of your Combo Box, create an event procedure
(right-click on the ComboBox then go to Properties -> Event tab -> Click on
the drop down arrow by After Update then select [Event Procedure] -> click on
elipses (...))

For the AfterUpdate sub

Me.Text1.Value = Me.ComboBox.Column(0)
Me.Text2.Value = Me.ComboBox.Column(1)

Substitue your names for the text boxes 1 & 2 and ComboBox.

 

[Guest]

Rich:

Firstly you should ask yourself whether you should do so. Taking the
following two scenarios the answer is No in one case, Yes in the other:

1. Say you have a table Customers with columns CustomerID, FirstName,
LastName etc., and a table Orders. In addition to columns OrderID, OrderDate
etc. the latter should have a foreign key column CustomerID, but not
FirstName or LastName columns. That would be redundancy and leave the door
open to possible update anomalies as you could have the same CustomerID in
different rows in Orders, but with different names. The names are always
available by joining the tables. In this scenario the Orders form would have
a combo box bound to the CustomerID column with a RowSource

SELECT CustomerID, FirstName, LastName
FROM Customers
Order BY LastName, FirstName;

By hiding the first column (OrderID) the combo box would show the FirstName
value when a selection is made. To show the LastName field you'd have an
unbound text box on the form with a ControlSoiurce such as:

=cboCustomerID.Column(2)

The Column property is zero based so Column(2) is the third column, LastName.

2. In this scenario you have another table OrderDetails which includes a
field ProductID to which a combo box is bound. The control would have a
RowSource:

SELECT ProductID, ProductName, UnitPrice
FROM Products
ORDER BY Product;

By hiding the first column again the control would show the ProductName
selected. This time, however, you WOULD have a UnitPrice field in the
OrderDetails table as the unit prices in the Products table will change over
time, but you want to store the price at the time of the order with each
order. So you have a text box on the form bound to a UnitPrice field in the
OrderDetails table and in the AfterUpdate event procedure of the cboProductID
combo box assign a value to the bound text box:

Me.UnitPrice = Me.cboProductID,Column(2)

Ken Sheridan
Stafford, England