Adding a cancel button to MsgBox

[Chad]

Hello, I have some code in my forms unload event and this code brings up an
Msgbox if there are null fields in my records shows me what the ID numbers
are and closes the form and opens frmSwitchboard. Right now it has Just the
"OK" button to do this but I want to add a "cancel" button so instead of
closing the form and opening the frmSwitchboard it keeps the current form
"frmMainDB" open. How and what would I add to the code? Thanks!!!!

Private Sub Form_Unload(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intX As Integer
Dim strIDs As String

Set db = CurrentDb()

Set rs = db.OpenRecordset("qryFindNullRecords", dbOpenSnapshot)
If rs.RecordCount > 0 Then

With rs

..MoveLast
..MoveFirst

intX = .RecordCount

Do Until .EOF

strIDs = strIDs & .Fields("ID") & vbCrLf

..MoveNext

Loop

End With

MsgBox "There is information missing in " & intX & " record(s) that hasn't
been entered within todays" & vbCrLf _
& "records or yesterdays records. The record ID's are" & vbCrLf & vbCrLf _
& strIDs & vbCrLf _
& "Please follow up on missing data!", vbOKOnly + vbExclamation, "Missing
Information"

End If

rs.Close
Set rs = Nothing
Set db = Nothing
Forms![frmSwitchboard].Visible = True

End Sub

 

[fredg]

Hello, I have some code in my forms unload event and this code brings up an
Msgbox if there are null fields in my records shows me what the ID numbers
are and closes the form and opens frmSwitchboard. Right now it has Just the
"OK" button to do this but I want to add a "cancel" button so instead of
closing the form and opening the frmSwitchboard it keeps the current form
"frmMainDB" open. How and what would I add to the code? Thanks!!!!

Private Sub Form_Unload(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intX As Integer
Dim strIDs As String

Set db = CurrentDb()

Set rs = db.OpenRecordset("qryFindNullRecords", dbOpenSnapshot)
If rs.RecordCount > 0 Then

With rs

.MoveLast
.MoveFirst

intX = .RecordCount

Do Until .EOF

strIDs = strIDs & .Fields("ID") & vbCrLf

.MoveNext

Loop

End With

MsgBox "There is information missing in " & intX & " record(s) that hasn't
been entered within todays" & vbCrLf _
& "records or yesterdays records. The record ID's are" & vbCrLf & vbCrLf _
& strIDs & vbCrLf _
& "Please follow up on missing data!", vbOKOnly + vbExclamation, "Missing
Information"

End If

rs.Close
Set rs = Nothing
Set db = Nothing
Forms![frmSwitchboard].Visible = True

End Sub

Use the MsgBox() function, not just MsgBox, and vbYesNo instead of
vbYesOnly to return the value of which button was clicked.
.......
if MsgBox("There is information missing in " & intX & " record(s) that
hasn't been entered within todays" & vbCrLf _
& "records or yesterdays records. The record ID's are" & vbCrLf &
vbCrLf _
& strIDs & vbCrLf _
& "Please follow up on missing data!", vbYesNo + vbExclamation,
"Missing Information") = vbYes then
' Do the Yes thing here
Else
' Do the no thing here
End If
.......

 

[Tony Toews [MVP]]

FWIW I'd far soon put the message in a string and pass it to the
msgbox function.

strMsg = "There is information missing in " & intX & " record(s) that
hasn't been entered within todays" & vbCrLf _
& "records or yesterdays records. The record ID's are" & vbCrLf &
vbCrLf _
& strIDs & vbCrLf _
& "Please follow up on missing data!"
If msgbox(strmsg, vbOKOnly + vbExclamation, "Missing
Information") = vbok then
' Do the Yes thing here
Else
' Do the no thing here
End If
.......

IMO it's much easier to figure out what's going on.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/