Reference table and form text box MS Access VBA

Question

Pardon the code below. I am a VBA novice. I am looking to upon clicking a form button, inform customers that they must make another library selection if the item they have chosen is checked out. I determine something is checked out if the "Check In Date" for the most recent date in the "1Transaction" table is NULL. Note for every check in and check out, a new record is created in the 1Transaction table and every record (whether check in or check out) will have the check out date info. So the logic, take the most recent date for the lease (book) number and if there is no return date then it is still checked out. The code below is meant to make the references and return a message box in VBA but I am stuck. I understand logically what I require but I know my VBA syntax is very off. Thanks.

Private Sub Check_Out()

 If [1Transactions].[Asset].Value = Me.Lease_Num
  And DMax([Tables]![1Transactions].[Check Out Date])
  And [Tables]![1Transactions].[Check In Date] = NULL
 Then MsgBox "The requested documents are currently checked out"
 End If
 DoCmd.OpenForm "Check In"

End Sub

Note:

1Transactions = Table holding all check in/out data
Me.Lease_Num = value pulled from combo box that user fills out to provide "lease number" (book code) they are interested in checking out.

dbmitch dbmitch · Accepted Answer · 2016-06-07T01:12:56

Okay - instead of trying to modify every line of your code, I think it's better to use a Parameter Query to simple check if the item is out now

First create a query using your transactions table - modify the SQL to below and save it as "qdfLease"

PARAMETERS [What Lease Num] Text ( 255 ); SELECT TOP 1
[1Transactions].Asset, [1Transactions].[Check Out Date],
[1Transactions].[Check In Date] FROM 1Transactions WHERE
([1Transactions].Asset = [What Lease Num]) And ([1Transactions].[Check
In Date] Is Null) ORDER BY [1Transactions].[Check Out Date] DESC;

Modify the code in your sub to:

Dim qdf             As DAO.QueryDef
Dim rs              As DAO.Recordset
Dim strLeaseNum     As String

strLeaseNum = nz(Me.Lease_Num,"")
Set qdfLease = CurrentDb.QueryDefs("qdfLease")
qdfLease.Parameters("What Lease Num") = strLeaseNum
Set rs = qdfLease.OpenRecordset(dbOpenDynaset, dbReadOnly)
If rs.EOF Then
    ' Item is Checked In
Else
    ' Item is Checked Out
End If
rs.Close
Set rs = Nothing

Reference table and form text box MS Access VBA

Note:

2 Answers