DH010010 said:
I have a form that i wan to display information from two tables. I
have the bulk of the form done however there is one section im stuck
on. i have a combo box that when i select an item from it it will
auto fill in a few arears of my form. can this be done??
The question is not if it can be done (it certainly can) but whether it
should be done. In most cases it is a bad idea.
One does not normally "copy" data between related tables. You just add the
foreign key value so that can be used to "look up" the related data for
display in forms and reports or for use in queries.
For example; the classic Sales Order form. You have a drop down list for
customers. This stores the CustomerID number in the Order record. You
might very well want to SEE the Billing address data for that customer on
the form, but you should not copy it into fields stored as part of the Order
record. You should retrieve it from the Customer table with a lookup
mechanism (there are several).
One simple lookup mechanism that is very flexible is to include in the
ComboBox RowSource for the CustomerID all of the fields for the Billing
address (hidden if you prefer). Then you can have TextBoxes on the form
that use ControlSource expressions like...
=CustomerID.Column(1)
=CustomerID.Column(2)
etc..
This will cause those TextBoxes to diplay the data related to the CustomerID
currently selected in the ComboBox, but that data is not actually saved as
part of the Order record.
Having said that, there are cases where it IS appropriate to copy data into
the Order record. Those usually fall into two categories; data that can be
optionally overridden and data that is time-sensitive. If I lookup an older
order for a customer I probably still want to see their current Billing
address so that would normally be a lookup. However; consider data like the
ShipToAddress and DiscountRate. These actually do need to be retrieved from
the Customers table and stored in the Order record for two reasons.
First, both are time-sensitive to the specific order. If I look at an older
order record I don't want to see the customer's current DiscountRate, I need
to see the rate at the time the order was processed. Therefore I need to
save it as part of the order record. Second, either might need to be
overridden on specific orders. In these cases the looked up value is merely
the default value for new orders being entered, but can be changed for any
specific order to a different value. Again , that requires that the data be
stored as part of the order rather than being looked up on-the-fly.
For data that needs to be copied, you could again add those fields to the
CustomerID ComboBox RowSource only now you use code in the the AfterUpdate
event to *copy* the values in those hidden columns into your other
TextBoxes...
Me.DiscountRate = =CustomerID.Column(5)
Me.ShipToCity =CustomerID.Column(6)
Me.ShipToState =CustomerID.Column(7)
etc..
Now that you have copied that data into bound TextBoxes on your form they
will be saved as part of the Order record.
So, you have to decide whether the look up or copy method is appropriate for
your situation and can use the appropriate example method above.
