AutoLookup, Dlookup, I don't care . . . just need something to wor

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've been trying to create a database and I am stuck on one part of my form
that seriuosly affects the efficiency of it.

Here it is:

On my ORDER form, I have a combo box called "d_pack". I want any
information that is called up in this field to automatically be saved in a
table called

CUSTINFO under the field "d_pack". However, on the ORDER form, "d_pack" is
a combo box that gets its information from a separate table called DED_PACK,
which contains an identical field called "d_pack".

So here is the problem. In this DED_PACK table, I have 3 fields. "d_pack",
"d_pack_price", and "d_pack_setup". When I

choose select one of the records from the combo box on the ORDER form (bound
to the "custinfo" table where all the information will be stored), I want it
to

automatically populate the "d_pack_price" textbox on the form with the
"d_pack_price" info associated with the selection in the "d_pack" combo box.
Anyone understand my question or have a clue how to do it?

I've researched a lot online and looked through books, but everything tends
to be so incredibly jargon heavy that it's difficult to understand, so
keeping explanations plain and simple will help a lot.


I'd really appreciate any help I can get.



Table and Form Layout:
CUSTINFO TABLE
Name
Date
d_pack
d_pack_price


DED_PACK TABLE
d_pack
d_pack_price
d_pack_setup



ORDER FORM (bound to CUSTINFO Table)
d_pack (combo box) [gets information for drop down list from DED_PACK table]
d_pack_price (text box) [Want to automatically retrieve "d_pack_price"
information from associated "d_pack" record]
 
You don't mention what field you plan to have the combo box save its value
to. Further, in a relation database, you don't actually need a design that
copies all of the data. In fact, all you need to save is the d_pack id. Once
this pack id is saved to you order form, then you an use sql, reprots etc
etc etyc etc to grab those other additional values from the DED_PACK table.

The above concept, and idea is what we call data normalizing, and simply is
a fact term for DO NOT COPY REDUNDANT DATA OVER AND OVER.

However, it is possible that over time the price of d_pack will change..and
if we don't copy the current price to the order..then over time we will
loose information..and old order will not price correctly if you
change/update the price in DED_PACK table. So, sometimes, you may, and
SHOULD copy the price data. So, having mentioned the whole idea behind
database is to NOT copy data..lets assume that you need this information
saved in the order form. The solution is easy.


First, Use the wizard to build the combo box. Make sure you include all 3
fields in this combo box. The order of the columns is important that you
select during the wizard.

We now get a combo box with 3 columns:

d_pack, d_pack_print, d_pack_setup

And, of course, this combo box is going to be bound to field d_pack in the
order form.....right?

Now, in the after update event of the combo box, we simple set the values of
the 2 fields (remember, the combo box is saving, storing d_pack value for
us).

The code to copy the other values from the combo box is:

me!OrderPrice = me.MyComboBox.column(1)
me!OrderPackSetup = me.MyComboBox.column(2)

Thus, you only need two lines of code to do the whole process. However, if
that pricing data will not change..then you should not, and do NOT need to
copy the price and packsetup fields. As a clarification issue, the column()
property of the combo box is "zero" based, and that simply means that the
first column starts at 0, and not one....
 
Back
Top