TempVars Access 2003 equivalent for the Lending Library template

[doive1231]

Hello,



I am trying to recreate the Access 2007 MS Lending Library template in Access 2003.

The 2007 version uses the expression "TempVars" in the Asset field (Default Value =[TempVars]![ItemID]) of the Check out form to temporarily store the value of the asset I believe so that the asset is not available to check out in the Asset List form once the Check Out form data is completed.

I have read that TempVars is a new expression in Access 2007 and was wondering what the equivalent expression would be in Access 2003? I'm thinking I should declare the asset number as a variable in VB so that it can update the Asset List form to show the asset as not available. Any help would be much appreciated.

Thanks, Dave

[pbaldy]

What exactly are you trying to do? I'd be more likely to calculate available or not from transactions, but that would depend on your table structure. For instance, if you had a transaction table that included item, checkout date/time and return date/time, then a query that found all records with no return date/time tells you everything that is out. An unmatched query of that against all items tells you what is available.

[doive1231]

Hello Paul,

Thanks for your reply. I am using Access 2010.

Yes, the Check Out form's record source is the table Transactions that includes the fields you mention. The Asset (ID) in this table is the many linked to the Asset table where it is unique.

The Asset List form is a list of all the assets showing whether or not they are available to check out based on a query called Assets with Transactions.

Clicking the (screen only hyperlink) Action field on the Asset List form (from the query this is Action: IIf([Contact Name]="" Or [Contact Name] Is Null,"Check Out","Check In") runs an embedded macro that sets the Status to Not Available or Overdue (if the return date has passed) - the query's Status field is Status: IIf([Contact Name]="" Or [Contact Name] Is Null,"Available",IIf([Due Date]<Date(),"Overdue","Not Available")). The macro On Click converted to VB is:


'------------------------------------------------------------
' Action_Click
'
'------------------------------------------------------------
Private Sub Action_Click()
On Error GoTo Action_Click_Err

If (IsNull(ID)) Then
Beep
Exit Sub
End If
TempVars.Add "ItemID", ID
If (Nz([Contact Name]) <> "") Then
DoCmd.OpenForm "Check In", acNormal, "", "[Transactions].[Asset]=[TempVars]![ItemID] And [Transactions].[Checked In Date] Is Null", acEdit, acDialog
End If
If (Nz([Contact Name]) = "") Then
DoCmd.OpenForm "Check Out", acNormal, "", "1=0", acEdit, acDialog
End If
DoCmd.Requery ""


Action_Click_Exit:
Exit Sub

Action_Click_Err:
MsgBox Error$
Resume Action_Click_Exit

End Sub

(although it said there were errors converting the Asset List form's macros to VB in Access 2010).

Again there is the TempVars expression in the macro which appears too as a not visible field in the Check Out form. Selecting the Contact Name from a value list enables a button on the Check Out form so that the borrower is displayed alongside the item/asset finally on the Asset List form when closed.

[pbaldy]

The OpenForm line is doing this type of thing:

BaldyWeb wherecondition

but using TempVars (which I have to admit not to have used). I would just use a form reference.

[doive1231]

Paul, thanks again for your reply. I have actually used your wherecondition page before and it was a great help. I'm not massively up to speed with Access but I will look at the form reference. Is declaring a variable for the asset etc the way to go?

[doive1231]

Sorry, ignore the last bit of my last post. Just woke up.

[pbaldy]

Happy to help; glad you got it sorted out.