I am using Access 2007 under MS Win 10 x64 to manage an inventory. I have an Issue form (frmVolAssetIssuingForm), which works fine and creates a record in tblAssetsLoaned with the following fields: LoanID, AssetID, BorrowerID, UserIDOut, LoanDate, DueDate, UserIDIn, ReturnedDate, PrimaryLoc, SecondaryLoc and TertiaryLoc. The issuing transaction fills in all the fields, except UserIDIn and ReturnedDate, which await the asset’s return.
My problem is with the "Return Asset" transaction. When the Borrower returns the asset, the return form (frmVolLoanedAssetsBack), which has the Record Source "SELECT tblAssetsLoaned.*, tblAssets.OnHand FROM tblAssets INNER JOIN tblAssetsLoaned ON (tblAssets.AssetsID= (tblAssetsLoaned).AssetID) AND (tblAssets.PrimaryLoc=(tblAssetsLoaned.PrimaryLoc) AND (tblAssets.SecondaryLoc=(tblAssetsLoaned).Secondar yLoc) AND (tblAssets.TertiaryLoc=(tblAssets Loaned).TertiaryLoc); is opened.
This form has the field: LoanID (Unbound) plus a repeat of the fields from the issuing form. The Unbound LoanID is named Combo23, which has the Row Source "SELECT (tblAssetsLoaned.LoanID, tblAssetsLoaned.* FROM tblAssetsLoaned;" The form also has an "After Update" [Embedded Macro] as "SearchForRecord" with Arguments: (, First, = "(LoanID) = " & Str(Nz([Screen].[Active Control],0). Finally, the form has the "On Change [Event Procedure], which is
Private Sub Combo23_Change()
‘ Set value of receiving User to current UserID logged on
[UserIDIn] = lngUser
‘ Set value of ReturnedDate to today’s date
[ReturnedDate] = Date
‘ Restore OnHand status to "Yes"
‘ [tblAssets.OnHand] = "X"
End Sub
Now when I key in (or select from a pull-down menu) the LoanID associated with this transaction, I am expecting the remainder of the fields to be populated; however, this does not happen I would appreciate any help with resolving what step(s) I am missing here. Thanks.