Get this lesson’s code from my GitHub.
What’s a DAO recordset? It’s a ‘set’ of records you define the way you want in Access. You use recordsets to pull information from what might be a ginormous amount of information in a database, so that you are working with a smaller set of data at one time. You don’t want to put anything in memory that you don’t have to.
Info Boxes
Here’s what a function with a DAO recordset looks like. Note I have now put a comment block at the top of it. Now, if you just use one-off functions and have just a couple sitting around, you may not think this is necessary. However, if you are planning to put together a database or a system where you might have many functions and/or macros, my opinion is, it’s a good idea to put at the top some information about the function so that you and others looking back on it later have a little info about it. So most functions from here on out will have that blurb.
Function fDAORecordsetDemo() '===================================== ' Name : fDAORecordsetDemo ' Author : Erica L Ingram ' Copyright : 2019, A Quo Co. ' Call command: Call fDAORecordsetDemo ' Description : function description, special notes ' list here any required arguments '=================================== Dim rstCrtDts As DAO.Recordset Dim rstOLPP As DAO.Recordset Dim sShipDate As String Dim sTrackingNo As String
Explanation
Next, we’re going to open a recordset. A recordset is, if you will, a set of records. In Microsoft Access, you need to define what records it should put in the set. It can be a full SQL statement or the name of a table or query in your database. You do that like this:
Set rstCrtDts = CurrentDb.OpenRecordset _
("SELECT ID, ShipDate, TrackingNo FROM CourtDates")
Set rstOLPP = CurrentDb.OpenRecordset("OLPP")
In the first line, we used a SQL statement as the source of our recordset. In the second example or third line, we used the name of a table as the source.
The following code tells it ‘if it’s not at the end of the recordset or if there is at least one record, then do this’
If Not (rstCrtDts.EOF And rstCrtDts.BOF) Then
'For each CourtDates.ID
The next line tells the function to go to the first record in the recordset
rstCrtDts.MoveFirst
This line tells the function, “Do the following until you hit the end of the recordset.”
Do Until rstCrtDts.EOF = True
Next, we will edit the recordset:
rstCrtDts.Edit
rstCrtDts.Fields("ShipDate").Value = sShipDate
rstCrtDts.Fields("ID").Value = sCourtDatesID
rstCrtDts.Update
If you want to add a new record to the recordset instead of editing the current record:
rstCrtDts.AddNew
rstCrtDts.Fields("ShipDate").Value = sShipDate
rstCrtDts.Fields("ID").Value = sCourtDatesID
rstCrtDts.Update
Now, you may have noticed we had originally opened TWO recordsets. We are now getting to the second one we opened, OLPP, which does some of the same things we did to the first record and we won’t go over:
If Not (rstOLPP.EOF And rstOLPP.BOF) Then
rstOLPP.MoveFirst
Do Until rstOLPP.EOF = True
'makes an entry in comms history table
Call pfCommunicationHistoryAdd("TrackingNo")
'perform functions on current recordset
rstOLPP.MoveNext
Now, this is where it loops to the next record in OLPP if there is one in the current DAO recordset. It will go through all of the OLPP records before moving on to the next CourtDates record.
Loop
Here, we are clearing variables, but if they aren’t objects, clearing is not always necessary:
sShipDate = "" sCourtDatesID = "" sTrackingNo = ""
And now, we move to the next rstCrtDts record in the recordset.
rstCrtDts.MoveNext
This is what the function will do if the recordset has 0 entries or if it reaches the end of the recordset, hence ‘else’.
Else
End If
Loop
Okay, we just looped to the next rstCrtDts record. Now, if there were no records in the recordset to begin with, here’s what it would do:
Else
MsgBox "There are no more packages to process."
End If
When it has completed, a message box appears and then we close the recordset, being sure to clear the objects we used in the function.
MsgBox "Finished searching for tracking numbers." rstOLPP.Close Set rstOLPP = Nothing rstCrtDts.Close 'Close the recordset Set rstCrtDts = Nothing 'Clean up End Function
Full Code
Function fDAORecordsetDemo()
'============================================================================
' Name : fDAORecordsetDemo
' Author : Erica L Ingram
' Copyright : 2019, A Quo Co.
' Call command: Call fDAORecordsetDemo
' Description : function description, special notes
' list here any required arguments
'============================================================================
Dim rstCrtDts As DAO.Recordset, rstOLPP As DAO.Recordset
Dim sShipDate As String, sTrackingNo As String
Set rstCrtDts = CurrentDb.OpenRecordset("SELECT ID, ShipDate, TrackingNo FROM CourtDates")
Set rstOLPP = CurrentDb.OpenRecordset("OLPP")
If Not (rstCrtDts.EOF And rstCrtDts.BOF) Then 'For each CourtDates.ID
rstCrtDts.MoveFirst
Do Until rstCrtDts.EOF = True
rstCrtDts.Edit
rstCrtDts.Fields("ShipDate").Value = sShipDate
rstCrtDts.Fields("ID").Value = sCourtDatesID
rstCrtDts.Update
rstCrtDts.AddNew
rstCrtDts.Fields("ShipDate").Value = sShipDate
rstCrtDts.Fields("ID").Value = sCourtDatesID
rstCrtDts.Update
If Not (rstOLPP.EOF And rstOLPP.BOF) Then 'For each row in OLPP
rstOLPP.MoveFirst
Do Until rstOLPP.EOF = True
Call pfCommunicationHistoryAdd("TrackingNumber") 'makes an entry in comms history table
'get tracking number and ship date
'update courtdates shipdate and tracking number come back
rstOLPP.MoveNext
Loop
sShipDate = ""
sCourtDatesID = ""
sTrackingNo = ""
rstCrtDts.MoveNext
Else
End If
Loop
Else
MsgBox "There are no more packages to process."
End If
MsgBox "Finished searching for tracking numbers."
rstOLPP.Close
Set rstOLPP = Nothing
rstCrtDts.Close 'Close the recordset
Set rstCrtDts = Nothing 'Clean up
End Function
Epilogue
Being able to traverse records in a database is very important because it is the basis for generating portions of Word documents automatically or being able to automatically place information in a transcript, Word document, or brief. To generate information and place it automatically in your Word documents, transcripts, or briefs, you will need to be able to pull the information necessary to produce the document into a recordset, which you can then use to mail merge a template you might have.
Next up, we’ll be discussing if-then-else statements such as the one I explained in this example.
**While we have made every effort to provide accurate information, A Quo Co. and Erica L. Ingram assume no responsibility or liability for any errors or omissions in the content of this blog. The information contained in this blog is provided on an “as is” basis with no guarantees of completeness, accuracy, usefulness, or timeliness.