tvh said:
I'm very new at this and I may be going about it all wrong, but:
The table is part of the form. It's a table at the bottom of the
form for various "additional charges" and there's only a limited
amount of space. Therefore I put a scrollable table in the form.
Sometimes there are no additional charges; sometimes there are
several. The table consists of an Item Code, Item Description and #
of Units. The Item Code was being used as the Primary Key in the
table because there should never be two identical Item Codes. Also,
the Item Code is an important bit of info that needs to be displayed
along with the Description and Quantity on the form. I changed the
layout of the table so that there's an Auto Number as the Primary
Key. But that still doesn't solve my delimna. How can I set it up
so that when I use a combo box, two fields are updated instead of
one?
I think I'm making this harder than it needs to be. Maybe I don't
have it set up correctly.
"Rick Brandt" wrote:
You are and you don't
You have two tables with a relationship between them. One is the table that is
the source of your ComboBox list. It holds the ItemCode and Description of
possible entries in the second table "additional charges". The first table
already contains everything you'll ever want to know about the possible entries
for additional charges. When you want to add an additional charge to the second
table ALL you need is the ItemCode. Anything else you want to see about that
charge can be retrieved from the first table so you have no need to copy that
data to the second table.
Let's take a more traditional example; the Sales Order. I have a table of
Customers with fields CustomerID, CustomerName, CustomerAddress, etc.. I also
have two tables for Orders. One for the OrderHeader and one for OrderLineItems.
In my Main Order form I need to record which customer placed the order and this
is accomplished with a ComboBox. The ComboBox displays a list of CustomerNames,
but actually stores the CustomerID value. I might want to *SEE* all kinds of
data about the customer on the order form, but the only piece of data that I
*STORE* in the OrderHeader table is the CustomerID. All other data that I show
on the form is retrieved from the Customers table and displayed. If I enter an
order in June and then the customer calls me with a change of phone number I can
go look at that old order from June and I will see their NEW phone number
because I am looking it up from the Customers table and not saving it as part of
the Order Record.
Likewise as I am entering LineItems for the Order I can use a ComboBox to select
PartNumbers from my PartNumbers table. I might wish to *SEE* the part's
description on the form, but I can do that by retrieving it from the PartNumbers
table. I do not need to save it as part of the LineItems table. Here again. if
the description for a particular part number were to be changed, I would see
that new descripton in all of my existing orders when I look at them without
having to run some update query to change a bunch of copies of that description
that I had made earlier.
Now for the exceptions to this rule (you knew that was coming right?). Suppose
one of the pieces of data in the Customers table was something like
CurrentDiscountRate. That is time-sensitive data that I DO need to save as part
of the order. If an order is looked at several months later, I need to see the
DiscountRate at the time the order was placed, not whatever their current rate
might be. Similarly with a LineItem's price. If the PartNumbers table includes
a price field I need to copy that data into my order so I can make a recording
of what the price is NOW.
So...when working with related tables like this you need to decide which pieces
of data need to be copied because of their time-sensitive nature and which ones
should be looked up from the source table so the newest version is always
displayed.
To *COPY* a piece of data I would run code in the AfterUpdate event of the
ComboBox that grabs the data from the additional column(s) and places it into
bound TextBoxes on the form like...
Me.DiscountRate = Me.CustomerID.Column(4)
or
Me.PartPrice = Me.PartNumber.Column(2)
To *DISPLAY* a piece of data I would have unbound TextBoxes that just pull the
data from the additional colums in the ComboBox and display them with a
ControlSource like...
=Me.CustomerID.Column(1)
or
=Me.PartNBumber.Column(1)
There are actually several methods to do the "retrieve and display". For the
customer data for example it might be better to actually have a subform that
displays the customer data linked to the CustomerID ComboBox instead of using a
bunch of separate TextBoxes to display the data.
