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 · Accepted Answer · 2016-06-07 01:12:56Z

1

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

answered Jun 7, 2016 at 1:12

dbmitch

5,4064 gold badges27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lyle Over a year ago

Thanks. This makes sense!

dbmitch · Accepted Answer · 2016-06-06 23:11:02Z

1

You have [1Transactions].[Check Out Date].Value = Me.Lease_Num

Is that what you want to compare the lease_num to? It sounds like it should be a different field name in [1Transactions]

I would change this

[Tables]![1Transactions].[Check In Date] = NULL

to this

IsNull([Tables]![1Transactions].[Check In Date])

What are you trying to compare here

DMax([Tables]![1Transactions].[Check Out Date])

answered Jun 6, 2016 at 23:11

dbmitch

5,4064 gold badges27 silver badges39 bronze badges

3 Comments

Lyle Over a year ago

[1Transactions].[Check Out Date].Value = Me.Lease_Num Here I am trying to subset the 1Transactions Table where the lease number is equal to that which was selected by the customer in a combo box ( Me.lease_num) - DMax([Tables]![1Transactions].[Check Out Date]) here I am trying to pull the maximum date for the lease number that was selected in the combo box ( Lease_Num) - Thanks

dbmitch Over a year ago

What I'm saying is ... 1Transactions must have a LeaseNum field doesn't it? Why don't you use that to compare to your Lease_Num combo box? Right now you are comparing it to [1Transactions].[Check Out Date].Value - which I assume is a date and not a library number

Lyle Over a year ago

I see what you are saying. Apologies. Long day. I am actually referencing the Assets field in [1Transactions] . Typo on my end.

Collectives™ on Stack Overflow

Reference table and form text box MS Access VBA

Note:

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Note:

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related