
Signature
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
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.
> > When I select the arrow of the combo box, a list with two fields
> > opens up. One field is the ID (i.e., 1234) and the other field is the
[quoted text clipped - 17 lines]
> Then change the design of the table to remove the Description field. You don't
> need it.
Rick Brandt - 31 Dec 2004 00:24 GMT
> I'm very new at this and I may be going about it all wrong, but:
>
[quoted text clipped - 14 lines]
> I think I'm making this harder than it needs to be. Maybe I don't
> have it set up correctly.
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.

Signature
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Ed Robichaud - 31 Dec 2004 00:40 GMT
Study Rick's reply again. You only need the ItemCode (not name/description)
in your "Additional Charges" table. There is NO need to store the item
name/price/color/etc. more than once. An Item table will contain all that
info. Use a query to link 2 or more tables together for any needed
report/form, or as Rick suggests, use an unbound text control on your form
to display the description. Make its control source, the second column of
your combo box, or use the Dlookup function to read/display the related
info.
-Ed
> I'm very new at this and I may be going about it all wrong, but:
>
[quoted text clipped - 43 lines]
>> don't
>> need it.