dg said:
Hi, I am working with Access 2003. The method I seek is to, after
creating a sub form; I would like to enter a part number which in
turn would populate "Description" "Price" and "Unit of Measure" sub
form fields. The information that populates the fields would
transfer to the Table the sub form is base.
Normally you would only want to do this with time sensitive data (in your case
price). Other data for a part number should be "looked up, retrieved, and
displayed" on your form, but you should not be making redundant copies of this
data in the new record.
Use a ComboBox for PartNumber entry and have it include additional (hidden)
columns for Description, Price, and Unit of Measure. These values can them be
accessed using the column property of the ComboBox. To *display* a value use a
TextBox with a ControlSource similar to...
=PartNumber.Column(n)
....where n is the zero-based ordinal position of the column you want to display.
To *store* a value use the AfterUpdate event of the ComboBox to grab a column's
value and copy it to bound TextBox on your form...
Me.Price = Me.PartNumber.Column(1)
Here is what you base your decision on. If any of these base values are changed
in the part number table and you come back to this record AFTER those changes
have been made what do you want to see? The new value or the value that existed
when the record was created?
For price you almost certainly want to see the price at the point in time that
the record was created, so you would use the second method, but what about the
description? If your company decides to make a change to the description of the
PartNumber wouldn't you want to see that same new description when looking at
all records? If so, then you should use the first method to simply display the
description from the Parts table rather than copying it.
