Placing a Primary Key in Another Table When Adding a New Record

[DavidW]

I have two tables - Hoods and Transactions. When an update is made to
the Hoods table, usually the Qty column, the update is written to the
Transaction table. Sometimes a new record is added and recorded in the
Transactions table. The tables are updated from entries the user makes
in textboxes on a form.

The Hoods table has the following columns:
HoodsID - the primary key and an auto number
Aisle
Sect
Box
Fabric
V_Color
Qty

The Transactions table has the following:
TransID
BoxID
OldQty
NewQty
Initials
Comment
Adjustments

The BoxID of the Transactions table should be set to the HoodsID of
the corresponding record in the Hoods table.

The first With block below will update or add a record to the Hoods
table. That part works. The second With block below will write the
transaction to the Transactions table. It works fine when updating an
existing record. The Transactions table is on a subform with a parent
child relationship of HoodsID = BoxID. The problem is when adding a
new record. How would I capture the HoodsID from the record that was
just added and place it in the BoxID field of the Transactions record?
Obviously, the line of code "!BoxID = HoodsID" will not work for
adding a new record.

I hope I supplied all the relevant information without going overboard.
Thanks for any help.

Private Sub btnSave_Click()
With Form_Add_Box.RecordsetClone
.AddNew
!Aisle = txtAisle.Value
!Sect = txtSect.Value
!Box = txtBox.Value
!Fabric = txtFabric.Value
!V_Color = txtV_Color.Value
!Qty = txtQty.Value
.Update
End With

With Me.Transactions.Form.RecordsetClone
.AddNew
!BoxID = HoodsID
!OldQty = Qty.Value
!NewQty = Qty.Value
!Initials = txtInitials.Value
!Comment = txtComment.Value
!Adjustments = Qty.Value
.Update
End With
End Sub

 

[Allen Browne]

Hi David

Firstly can I suggest that you move this code from the command button's
click event into the AfterUpdate event procedure of the *form*. That way,
the code will execute regardless of how the record is saved (e.g. by closing
the form, pressing Shift+Enter, choosing Save Record on the Records menu,
applying a filter, changing the sort, reassigning the RecordSource,
performing a Requery, closing Access, etc, etc.)

If this data is stored in JET tables (in an Access database), the new
HoodsID is assigned as soon as you begin adding a new record. If it is in a
different back end (such as SQL Server), the ID is not assigned until the
record is saved. Either way, the ID is available in Form_AfterUpdate, so
that should solve your issue.

The part I did not understand is:
!OldQty = Qty.Value
!NewQty = Qty.Value
It would seem that the OldQty and NewQty would always be the same with this
code? If that's the problem, you may want to examine the OldValue of Qty.
Unfortunately, that's no longer available in Form_AfterUpdate, so you will
need to read it in Form_BeforeUpdate, and apply it in Form_AfterUpdate. That
means you need somewhere to actually store the value between the events, so
you need a form-level variable.

1. In the General Declarations section of your form (at the top, with the
Option statements):
Dim mvarOldQty As Variant

2. In the BeforeUpdate event of the form:
mvarOldQty = Me.Qty

3. In the AfterUpdate event of the form, include:
!OldQty = mvarOldQty

If you are interested in a more comprehensive logging (including deletions),
see:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

 

[DavidW]

Thanks, Allen. I'll give your suggestions a try. It will probably be
tomorrow. The part of the code you don't understand is actually a
mistake:
!OldQty = Qty.Value
!NewQty = Qty.Value

The NewQty is actually equal to the Qty.Value +/- an adjustment taken
from a textbox when updating the quantity on an existing record. On a
new record OldQty will be zero and NewQty will be whatever is entered
by the user. I will probably have to add an If statement to care of
that.

I'll let you know when I get it to work.