Microsoft Access
--
Questions
--
Followers
Top Experts
Finding MAX value in a recordset in Access VBA
I am trying to pull back a recordset in VBA to determine the highest Event ID in the table so I can increment by 1 to add the next. Â The EventID field is numeric. Â If I SELECT * instead of MAX(EventID) I do pull back all the records which I can tell by using recordcount. Â When I use MAX I only pull back the 1 record, which I expect but I can't get the value that is in Event ID. Â It is telling me "Item not found in this collection" but EventID is in the RS. Â Here is the code:
strSQL = "Select MAX(EventID) from [tblEvents] where EventStatus = '" &Â strEventStatusSearch &Â "'"
    Set rs = db.OpenRecordset(strSQL)
    intMAXeventID = rs!EventID
strSQL = "Select MAX(EventID) from [tblEvents] where EventStatus = '" &Â strEventStatusSearch &Â "'"
    Set rs = db.OpenRecordset(strSQL)
    intMAXeventID = rs!EventID
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
What about just:
  DMax("[EventID]","tblEvent
mx
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Use Dmax in place of Max? Â What about the Where clause? Â I need that to filter the recordset.
The MyEventID worked - I have no idea why but that came back with the answer
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Microsoft Access
--
Questions
--
Followers
Top Experts
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.