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 based on the user selections from unbound textboxes. 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.

Take your variables first, before updating the recordsets. Then just reuse it as needed. You shouldn't pass non-variables to your recordsets. Take all these as variables first, then pass the appropriate variable to the recordset update. Reuse the HoodsID where ever you are getting it from. If it is not here, make it a global variable, set and take the value, and reset the value to "" when not in use.


Private Sub btnSave_Click()

Dim HoodsID As Integer

HoodsID = Me.HoodsID '?

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 = iHoodsID
!OldQty = Qty.Value
!NewQty = Qty.Value
!Initials = txtInitials.Value
!Comment = txtComment.Value
!Adjustments = Qty.Value
.Update
End With
End Sub


mmcdonal