getting multiple fields from another table using a combo-box

D

Dale

how would i go about using a combo-box to auto-fill certain fields in my form
from another table? i have to do this without altering the current table
structures as well.

heres an example of what i mean:

(combo box on form for table 1) = certain value

looks in (table 2) for certain value's row
returns columns in certain values row into the appropriate cells in (table 1)
 
J

John Vinson

how would i go about using a combo-box to auto-fill certain fields in my form
from another table? i have to do this without altering the current table
structures as well.

heres an example of what i mean:

(combo box on form for table 1) = certain value

looks in (table 2) for certain value's row
returns columns in certain values row into the appropriate cells in (table 1)
Click to expand...

The first question you should ask yourself is... do I *REALLY* want to
violate relational database design principles by storing data
redundantly in a second table?

If the data exists in Table2, it should NOT be copied into Table1
unless you have some very clear reason to do so (such as, for example,
that you want to capture a point-in-time snapshot of the data in
Table2). Ordinarily you would just store a link (using the combo's
bound column), and then use a Query to link to Table2 for the other
fields.

If you just want to DISPLAY the fields (without storing them
redundantly) you can set the Control Source property of textboxes on
the form to

=comboboxname.Column(n)

where n is the *zero based* subscript of the column that you want to
display.

John W. Vinson[MVP]
 
D

dale

hmm. that wont work unfortunately -- what i'm doing is trying to use an
auto-fill to fill in specifications (from another table) for a product on a
workorder, then store the completed workorder in its own table for future
reference. hope that clarifies what i'm trying to do a bit better.
 
J

John Vinson

hmm. that wont work unfortunately -- what i'm doing is trying to use an
auto-fill to fill in specifications (from another table) for a product on a
workorder, then store the completed workorder in its own table for future
reference. hope that clarifies what i'm trying to do a bit better.
Click to expand...

Not really.

What Entity defines the specifications? Are specifications an
attribute of a Product, or of a Workorder?

That is, might the specifications be different for every workorder,
even if the workorders are all for the same product? Or should the
specifications for a particular product all be the same?

If you're assuming that you must copy the fields into the workorder
table in order to provide "future reference" - you may not actually
need to do so; if you store the ProductID you can very easily create a
Query joining the Workorder table to the Products table.

If I'm misunderstanding what Specifications are... you can "push" the
values from a Combo into bound fields in the combo's AfterUpdate event
with code like:

Private Sub cboProductID_AfterUpdate()
Me!txtThisSpec = cboProductID.Column(2)
Me!txtThatSpec = cboProductID.Column(3)
End Sub

using the zero-based Column property. But - unless the specifications
for a Workorder should *default* to the specifications for the
product, but allow the user to change them at whim, however they wish,
then I'd suggest that you do NOT need to do so.

John W. Vinson[MVP]
 
D

dale

thanks for the suggestions John, but i've decided to bite the bullet, tell th
boss to 'suck it up' and re-create his dB so i can use simple lookups to
auto-fill his forms for him (in proper segregated tables, instead of one big
mess). it'll mean re-entering all of this years data, but thats ok :)
 
J

John Vinson

thanks for the suggestions John, but i've decided to bite the bullet, tell th
boss to 'suck it up' and re-create his dB so i can use simple lookups to
auto-fill his forms for him (in proper segregated tables, instead of one big
mess). it'll mean re-entering all of this years data, but thats ok :)
Click to expand...
Don't jump off a cliff here!

Append queries will let you salvage the existing data. There should be
NO need to reenter it.

And... don't use Lookup Fields. And don't use Table Datasheets for
data entry.


John W. Vinson[MVP]
 
Top