Defining field for subform

[Dan]

I have some unbound fields on an Orders form that gather information,
validate it, and then define a new record when the user moves to the
OrderDetails subform. The code I have in the OrderDetails subform's Enter
method is as follows:

<CODE>
Dim DBS As DAO.Database
Dim rstNewOrder As DAO.Recordset
Dim NewOrderID As Integer

Set DBS = CurrentDb
Set rstNewOrder = DBS.OpenRecordset("tblOrders", dbOpenDynaset)

With rstNewOrder
.AddNew
!CustID = strCustID
!OrderDate = CDate(strOrderDate)
!ShipDate = CDate(strShipDate)
.Update
.Bookmark = .LastModified

NewOrderID = !OrderID

.Close
End With

Set rstNewOrder = Nothing
Set DBS = Nothing
</CODE>

I need to set the OrderID as a field in the OrderDetails table.

1. Is this the way to retrieve the new OrderID from the newly-created Orders
table?

2. How do I set the OrderID in the OrderDetails subform to this value?

Thanks!

 

[Allen Browne]

Would you like the simple answer, or the difficult one?

(a) The simple answer requires no code at all. Just use a form bound to the
Orders form, with a subform bound to the OrderDetails table. Access will
sort out the foreign key value you. Zero effort. Zero code. For validation,
use the BeforeUpate event procedure of the form (not control.)

(b) If you want to waste your efforts reinventing the wheel when Access does
all this for you, you can read the new AutoNumber from the Recordset before
you Update (if it's an Access table.) Alternatively, you can get it just
like your NewOrderID does (though why you declared this as an Integer, I
don't understand: needs to be Long.)

As for how to assign it to the subform, a continuous unbound subform is not
going to work. Go back to (a).