Capture current value in related field

[sbg2]

Hello, I have been working with databases for approximately 15 years but
haven't worked much with Access. I can't figure out how to do a very simple
thing in Access and I am hoping someone can help me out.

Say I have 2 tables Item and Order_LineItem setup with a one-to-many
relationship on the ID_Item field in both tables. Table fields listed below.

Table Name: Item
ID_Item
Code
Description
Price

Table Name: Order_LineItem
ID_Order_LineItem
id_Item
Code
Description
Price
Qty

The id_Item field in Order_LineItem uses a value list of all the ID_Item(s)
in the table Item. After selecting a value from the list for Order_LineItem.
id_item I want the following fields to be set to the current related value
stored in the Item table:

Code
Description
Price

In other words Order_LineItems entered today will contain the current pricing.
But when I increase the prices 6 months down the road I want new orders to
contain the new pricing but the old orders to retain the old pricing.

Can someone please show me how to do this?

Thanks in advance.

 

[Jeff Boyce]

Adding the Price to the record will record the price-at-that-time, no matter
how many times you change the item price.

By the way, why is "Description" showing in both tables? Is that the same
field, or ...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[sbg3]

Sorry, had to create a new forum account because I can't access my work email
from where I am currently sitting.

"Adding the Price to the record will record the price-at-that-time, no matter
how many times you change the item price."

Right, but my question was on how to physically record the price.

"By the way, why is "Description" showing in both tables? Is that the same
field, or ...?"

For this example Description is the same in both tables. In the LineItems
table the Description field needs to be editable.

So I found out how to do this through an OnUpdate Event Procedure placed on
the id_Item ComboBox. Do I have to write code everytime I want to store
related values in my primary table? Maybe there's a wizard somewhere? Is
there anyway to attach the update to the fields that need to be set with the
related data rather than having to attach it to a ComboBox? Seems like a lot
of work for such a commonly used feature.


Private Sub Code_AfterUpdate()

On Error GoTo Err_Code_AfterUpdate

Dim strFilter As String
strFilter = "id_Item = " & Me!id_Item

Me!Description = Code.Column(2)
Me!Price = Code.Column(3)

Exit_Code_AfterUpdate:
Exit Sub

Err_Code_AfterUpdate:
MsgBox Err.Description
Resume Exit_Code_AfterUpdate

End Sub

 

[Jeff Boyce]

Storing redundant data ("Description") in more than one table is NOT a
common feature ... it is rarely necessary and can lead to
synchronization/data integrity issues.

And if one version is editable, then how do you resolve which version is the
"correct" version?

You might need to store the "description" more than once if you were using a
spreadsheet, but not in a relational database like Access.

If you have a form on which you are selecting an Item (e.g., from a combo
box), you can use the combo box's AfterUpdate event to "write" the price to
your form's Price control.

Your code might look something like:
Me!txtMyPriceControl = Me!cboMyItemComboBox.Column(x)
where "x" is the n-1 column from the query that feeds your combo box. Note
that this requires that you include Price as one of the columns in your
combo box's query/source.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[sbg2 via AccessMonster.com]

Thanks for the replies Jeff. I am quite familiar with relational databases
and understand synchronization/data integrity issues. If the field name
"Description" bothers you insert your field of choice that would need to be
transfered from a related table (BillingAddress, ShippingAddress,
PaymentTerms etc.). That is the commonly used feature of which I was speaking.


You confirmed my fear that using MyControl.Column would require that the
values must be a column in the control. At times I will require a large
number of fields to be copied from a related table (don't panic). Someone
told me that using MyControl.Column was quicker than using DLookup but it
appears I will have to go back to a DLookup.

As I said I'm new to Access. I need to put aside my first reaction to the way
"lookups" are accomplished in Access. I will try to reserve forming an
opinion until it is a little more informed.

Thank you for taking the time to help.