Windows Warning Pop up Not Stopping Code - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Windows Warning Pop up Not Stopping Code

Robert Freeman

8 months ago

Hi all. I would appreciate some help with this one please. I have produced some attendance reports following Richard's videos and all is working fine ... except!
Firstly, here's the vba that is running the report production:
----------------------------------------------
Private Sub RunAppendQBTN_Click()

        On Error Resume Next
        Me.Requery
        DoCmd.OpenQuery "AttendanceAppendQ"

        MsgBox "The new date has been added to the records." & vbNewLine & vbNewLine & _
        "You can now update the records by clicking the 'Open Attendance Form for Editing' button", vbInformation, "Update Succeeded."


End Sub
---------------------------------------------------
It runs fine and produces what I am looking for however I get two 'MS Windows' warning messages - see image uploads. I don't want to turn warnings off because the information in the warnings is useful to the report originator.
However, when the warning pop up and the originator selects NO (on either of the warnings) the code does not update any records (correctly) but is does continue to run the code, thus producing the message box which is superfluous (and inaccurate).
I'd appreciate advice on how to stop the code running under these circumstances.

Thanks. Bob Freeman.

	Robert Freeman OP @Reply 8 months ago

	Robert Freeman OP @Reply 8 months ago

	Robert Freeman OP @Reply 8 months ago

	Adam Schwanz @Reply 8 months ago
You could turn the warnings off, give the warning yourself, and control the code yourself with either a messagebox or a Custom MsgBox `If MsgBox("Warning Message Here about updating records", vbYesNo) = vbYes Then DoCmd.SetWarnings False 'Run Query, Messagebox, rest of code DoCmd.SetWarnings True Else 'Dont update anything Exit Sub End If`

	Robert Freeman OP @Reply 8 months ago
OK, that should work I guess but is it possible to get a value from the database and include that value in the message box - I've never tried to do that before. i.e. as image 2 - 252 rows (which is the number of active members in the DB)? If it is possible, has Richard covered it somewhere?

	Adam Schwanz @Reply 8 months ago
Sure, just use DLOOKUP or DCOUNT or whatever you want to find the value, and concatenate it. In your case, probably something like this `Dim X X = DCount("*", "CustomerT") If MsgBox("You are about to update " & X & " records", vbYesNo) = vbYes Then 'stuff End If`

	Robert Freeman OP @Reply 8 months ago
OK, many thanks. I'll give it a go and see what happens.

Robert Freeman OP @Reply

8 months ago

Hi Adam. With a bit of customisation it works perfectly, thank you so much for your help. FYI here's what I ended up with.
----------------------------------------------
Private Sub RunAppendQBTN_Click()
    Dim A
    A = DCount("IsActive", "MemberDetailsT", "IsActive=True")

     If MsgBox("You are about to append " & A & " records to the Attendance table. Do you want to continue?", vbYesNo) = vbYes Then

    DoCmd.SetWarnings False

    DoCmd.OpenQuery "AttendanceAppendQ"

        MsgBox "The new date has been added to the records." & vbNewLine & vbNewLine & _
        "You can now update the records by clicking the 'Open Attendance Form for Editing' button", vbInformation, "Update Succeeded."

    DoCmd.SetWarnings True
    Else
    Exit Sub
    End If

End Sub

	Kevin Robertson @Reply 8 months ago
Robert I would explicitly declare the variable type. You know it's going to be a whole number. `Dim A As Integer` or `Dim A As Long` Variables

	Robert Freeman OP @Reply 8 months ago
OK, thanks Kevin.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View