Unusual Procedure Call

[LeAnn]

Hi,

I have Access 2003. I have a strange event occuring that I tracked down to
my Exit procedure below. This is a button on my Admin menu. It sometimes
causes the Admin Form_Load event to run. This is a problem because that
event is checking a global variable. The close wipes out the variables and
thus is causing an error - leaving Access open in the background. This
occurs only if the user clicks close immediately after the useing the Purge
buttons (one example below). Interestingly enough the Form_Load event is not
being called if I open the database using the SHIFT key and click the purge
button. Any ideas why this is happening? Simple answer - remove the close
button but was curious if anyone had any ideas.

Thanks
LeAnn

'********************************************************
Private Sub cmdExit_Click()

If MsgBox("Are you sure you want to exit the program?", vbYesNo +
vbExclamation, "Exit Database") = _
vbYes Then DoCmd.Quit acQuitSaveNone

End Sub

'*******************************************************
Private Sub cmdPurgeUnits_Click()
On Error GoTo ErrorHandler

Dim db As DAO.Database, rst As DAO.Recordset
Dim strSQL As String, lngRec As Long

Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT * FROM tblUnits WHERE CreateDate < #" &
DateAdd("d", -30, Now) & "#")
If rst.EOF = True Then
MsgBox "There are no records to purge.", , "No Records"
GoTo Exit_Sub
Else
If MsgBox("Are you sure you want to purge units older than 30 days?", _
vbYesNo + vbExclamation, "Purge Data") = vbYes Then
DoCmd.Hourglass True
rst.MoveLast
lngRec = rst.RecordCount
Else
GoTo Exit_Sub
End If
End If

strSQL = "DELETE FROM tblUnits WHERE Box_Date < #" & DateAdd("d",
-30, Now) & "#"

If DbExec(strSQL) = False Then
MsgBox "Samples were not purged. Please see the database
administrator.", vbOKOnly + vbExclamation, "Error"
GoTo Exit_Sub
Else
MsgBox lngRec & " units purged.", vbOKOnly + vbInformation,
"Purged"
End If

Exit_Sub:
DoCmd.Hourglass False
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:
MsgBox "Error #" & Err.Number & " - Description: " &
Err.Description, vbOKOnly + vbExclamation, "Error"
Resume Exit_Sub
End Sub

 

[Dale Fye]

LeAnn,

It may have something to do with the fact that you are deleting records from
the table while you still have a recordset open. Since the domain functions
are optimized for this type of thing, have you considered using the DCOUNT
function to get the number of records that match your criteria, instead of
opening a recordset.

strCriteria = "[CreateDate] < #" & DataAdd("d", -30, Date) & "#"
lngRec = DCOUNT("ID", "tblUnits", strCriteria)
if lngRec = 0 then
msgbox no records
elseif msgbox("Are you sure", vbYesNo + vbExclaimation, "PurgeDate") = vbYes
then
strSQL = "DELETE ..."
if DbExec(strSQL) = False then
msgbox "Samples not purged"
else
msgbox lngRec & " units purged"
endif
endif

Another thought would be that there might be some code in the Forms Unload
or Close event.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[LeAnn]

Thanks Dale, for some reason I always forget domain functions (probably cuz I
don't like them much!). Your suggestion is much cleaner. It didn't however
solve the issue. My close event only has the Quit statement (see below). I
placed msgboxes at various points to determine what and when things were
being called. It appears that the close event invokes the form load event
under this very specific condition. My fix - place a hidden text box and set
it's value when "Close" is clicked. The form load event exits it if that
value is found in the text box.

Life is sometimes a mystery.

LeAnn,

It may have something to do with the fact that you are deleting records from
the table while you still have a recordset open. Since the domain functions
are optimized for this type of thing, have you considered using the DCOUNT
function to get the number of records that match your criteria, instead of
opening a recordset.

strCriteria = "[CreateDate] < #" & DataAdd("d", -30, Date) & "#"
lngRec = DCOUNT("ID", "tblUnits", strCriteria)
if lngRec = 0 then
msgbox no records
elseif msgbox("Are you sure", vbYesNo + vbExclaimation, "PurgeDate") = vbYes
then
strSQL = "DELETE ..."
if DbExec(strSQL) = False then
msgbox "Samples not purged"
else
msgbox lngRec & " units purged"
endif
endif

Another thought would be that there might be some code in the Forms Unload
or Close event.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hi,

I have Access 2003. I have a strange event occuring that I tracked down to
my Exit procedure below. This is a button on my Admin menu. It sometimes
causes the Admin Form_Load event to run. This is a problem because that
event is checking a global variable. The close wipes out the variables and
thus is causing an error - leaving Access open in the background. This
occurs only if the user clicks close immediately after the useing the Purge
buttons (one example below). Interestingly enough the Form_Load event is not
being called if I open the database using the SHIFT key and click the purge
button. Any ideas why this is happening? Simple answer - remove the close
button but was curious if anyone had any ideas.

Thanks
LeAnn

'********************************************************
Private Sub cmdExit_Click()

If MsgBox("Are you sure you want to exit the program?", vbYesNo +
vbExclamation, "Exit Database") = _
vbYes Then DoCmd.Quit acQuitSaveNone

End Sub

'*******************************************************
Private Sub cmdPurgeUnits_Click()
On Error GoTo ErrorHandler

Dim db As DAO.Database, rst As DAO.Recordset
Dim strSQL As String, lngRec As Long

Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT * FROM tblUnits WHERE CreateDate < #" &
DateAdd("d", -30, Now) & "#")
If rst.EOF = True Then
MsgBox "There are no records to purge.", , "No Records"
GoTo Exit_Sub
Else
If MsgBox("Are you sure you want to purge units older than 30 days?", _
vbYesNo + vbExclamation, "Purge Data") = vbYes Then
DoCmd.Hourglass True
rst.MoveLast
lngRec = rst.RecordCount
Else
GoTo Exit_Sub
End If
End If

strSQL = "DELETE FROM tblUnits WHERE Box_Date < #" & DateAdd("d",
-30, Now) & "#"

If DbExec(strSQL) = False Then
MsgBox "Samples were not purged. Please see the database
administrator.", vbOKOnly + vbExclamation, "Error"
GoTo Exit_Sub
Else
MsgBox lngRec & " units purged.", vbOKOnly + vbInformation,
"Purged"
End If

Exit_Sub:
DoCmd.Hourglass False
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:
MsgBox "Error #" & Err.Number & " - Description: " &
Err.Description, vbOKOnly + vbExclamation, "Error"
Resume Exit_Sub
End Sub