Which Loop do I use?

[sjl]

Now trying to close Access

I now want to have VBA or a Macro to close Access when it is finished running this.

What I have working (but not satisfactorily) is a command-line macro sitting in Window's Scheduled Tasks. This (Access) macro Opens a Form in Access, submitting the VBA code (below) and then Quits. But, it Quits Access too quickly. I can't figure out how to get a time feature into the macro (like 30 seconds). And, I tried inserting code at the end of my Sub (see red, below) but neither Form nor Access closes.

Do I have some nomenclature wrong....or am I better off using another Macro to close the db and Access?

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("qryEmail_Notif_NIEHS")

Dim dtOne As Date
Dim dtDue As Date
Dim strproject As String
Dim strprotocol As String
Dim strstudymgr As String

dtOne = Date 'todays date

Do While Not rst.EOF
dtDue = rst!dtmNPacketDue
strproject = rst!strBrochureName
strprotocol = rst!strNIHProtocolNum
strstudymgr = rst!strSMName


If dtDue > dtOne + 120 Then

DoCmd.SendObject , , , "xxxx@nnn.gov", , , "NIEHS Due Date Approaching", _
"The NIEHS packet due date for " & strproject & ", " & strprotocol & ", is: " & vbCrLf & vbCrLf & dtDue _
& vbCrLf & "which is XX weeks from today. Please make a note of it." _
& vbCrLf & vbCrLf & "IRB coordinator" & vbCrLf & "Elizabeth O'Connell", False

End If
rst.MoveNext
Loop
rst.Close 'does not close Form
db.Close 'does not close Access
End Sub
​

 

[pbaldy]

You can use this to make the code wait 30 seconds:

http://www.mvps.org/access/api/api0021.htm

The code to close Access is

DoCmd.Quit

The lines you highlight close those objects, not Access itself.

 

[sjl]

thanks pbaldy! that does it.

Curious, is it advisable to close the rst and db....or just as good to just DoCmd.Quit ?

thanx
sjl

 

[boblarson]

You want to close the recordset variables and set them equal to NOTHING as that will release them from memory. Access is supposed to do that automatically for you, but it is best to make sure that it happens instead of relying on it to happen automatically.

 

[sjl]

Bob,
Thanks!
I've added the red, below. Is that good tidying up?
sjl

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("qryEmail_Notif_NIEHS")

Dim dtOne As Date
Dim dtDue As Date
Dim strproject As String
Dim strprotocol As String
Dim strstudymgr As String

'Turn on error handling
On Error GoTo Cleanup


dtOne = Date 'todays date

Do While Not rst.EOF
dtDue = rst!dtmNPacketDue
strproject = rst!strBrochureName
strprotocol = rst!strNIHProtocolNum
strstudymgr = rst!strSMName


If dtDue > dtOne + 120 Then

DoCmd.SendObject , , , "xxxx@nnn.gov", , , "NIEHS Due Date Approaching", _
"The NIEHS packet due date for " & strproject & ", " & strprotocol & ", is: " & vbCrLf & vbCrLf & dtDue _
& vbCrLf & "which is XX weeks from today. Please make a note of it." _
& vbCrLf & vbCrLf & "IRB coordinator" & vbCrLf & "Elizabeth O'Connell", False

End If
rst.MoveNext
Loop

Cleanup:
'Release all objects
Set rst = Nothing
Set db = Nothing
On Error GoTo 0


rst.Close
db.Close
DoCmd.Quit


End Sub​

 

[boblarson]

Nope, you have it slightly reversed:

It SHOULD be -

Cleanup:
'Release all objects
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
On Error GoTo 0

DoCmd.Quit

 

[sjl]

Hey Bob,
thanks for the correct order. it does make sense.
sjl

 

[pbaldy]

I've read that doing both is overkill, so I typically only set them to nothing. As Bob said, you do want to do it explicitly rather than hope Access does it for you.