Populating multiple fields in a table (from a combo box) automatic

[Guest]

I am a pharmacist and am creating a database to help improve inventory
control. To do so, I have started with a table (Drugs by NDC) that has an
autonumber (primary key), the NDC number (a unique code that corresponds to
each individual drug), and the drug's description (name and strength) from
which I pull my inventory information. There are over 3000 entries in this
table.

The inventory project that I am working on will involve tracking drugs that
we run low on. As such, I have created a form that I use to input instances
where this happens that assigns the said event to another table (entitled Qty
Owed Table). This form contains fields for a unique number for the event
(primary key), the date of the event, the NDC# (which actually gives me a
combo box/look-up wizard view of both the NDC# and drug description from my
first table), a supplier (only two options), and the total qty owed.

While this form looks up the NDC# and the drug description, since I assigned
a combo box/look-up wizard for the NDC# field in the first table, I only get
the NDC# displayed in the resulting table. Ideally, I would like to have two
fields in the resulting table...both an NDC# field and a Drug Description
field...where the Drug Description field automatically populates when the
corresponding NDC# is selected in the NDC# field's combo-box. I cannot,
however, figure out how to make this happen. Although the two pieces of
information exists side-by-side in my first table (Drugs by NDC), I cannot
make them correlate automatically in my other table.

Although I realize that the first table (Drugs by NDC) has assigned each of
the drugs a new unique number through the primary key field, seeing both NDC#
and the drug description in my second table would be extremely useful when I
am creating reports and analzying data.

Does anybody know how to make this happen? Do I need to approach this from
a different angle? Any help is appreciated.

Thanks!!

 

[tina]

yes, approach it from a different angle:

1) get rid of the Lookup field in the first table, by opening the table in
Design view, selecting the field, clicking on the Lookup tab in the bottom
half of the window, and changing the DisplayControl property to Textbox. for
more information, see http://www.mvps.org/access/lookupfields.htm.

2) don't do any data entry in the tables themselves; do it via forms. (note
that you can use a combo box control *in a form* to select the NDC# from the
first table (as you described in your post), no problem; it does not cause
any of the problems that lookup fields *in tables* do.)

3) do not store the NDC number or the drug description in the QtyOwedTable;
that would be a violation of normalization rules, and is poor design.
instead you should store only the primary key from the table DrugsbyNDC, in
the QtyOwedTable. in the form, you can use an unbound textbox control to
*display* (not store) the description of the selected drug. in a query, you
can link the QtyOwedTable and DrugsbyNDC tables on the drug primary
key/foreign key pair, and include the NDC# and description in the query
output. this makes it easy to include the data in a report.

hth

 

[Guest]

Tina,

Thank you for the response. Your response has been very helpful and I can
see how the different angle you are suggesting will be much better. I have
been trying to get the unbound text box in my form to show the drug
description. Unfortunately, I have been unsuccessful at getting it to
display this information. Do you have any other suggestions as to how to get
the unbound text box to display the drug description information?

Thanks again, you have been very helpful!!

 

[tina]

yes, i deliberately didn't go into a lot of detail, figuring you'd ask about
anything you wanted more info on.

i'm assuming 1) you're using a form to enter data in the QtyOwedTable, 2)
you have a field in QtyOwedTable to store the primary key value of the
selected drug record from the DrugsbyNDC table, 3) you have that field bound
to a combo box control in the form, and 4) the RowSource (*not*
ControlSource) of the combo box control is table DrugsbyNDC - or a query
based on that table.

okay, in the combo box RowSource, make sure the drug description field is
included; you can set its' width in the control's ColumnWidth property to
zero so the description doesn't show in the droplist. as an example, let's
say the combobox control is named cboDrug, and it has three columns:
primary key field, NDC number, and drug description. combo box columns have
a zero-based index, so to refer to the first column in an expression, you'd
write
cboDrug.Column(0)
to refer to the second column, it would be
cboDrug.Column(1)
and so on.

so, per the example i outlined above, to show the selected drug's
description in an unbound textbox control in the form, add the following
expression to the textbox control's ControlSource property, as
=cboDrug.Column(2)

hth