Msgbox Cancel

[Robbyp2001]

Can anyone help me? I have created funtion that will run a series of queries in order to populate a number of records. I have created a message box which I hoped would give the option to cancel the process if needed. I produced a straighforward OK or Cancel message box, but even when clicking the 'cancel' option, the database goes ahead with the process anyway.

What am I doing wrong?

Anyway, here's the process if anyone can help,

Thanks for taking the time to read this

Oh, I'm using Access 2010

Rob

Private Sub Command21_Click()
retvalue = MsgBox("This Action Will Apply ALL Reports to the Selected Student. Continue?", vbOKCancel)
DoCmd.SetWarnings False
DoCmd.OpenQuery "Update Existing Student Records Step 1", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 2", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 2/5", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 3", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 4 Term 1 Report 1", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 1", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 2", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 2/5", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 3", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 4 Term 1 Report 2", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 1", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 2", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 2/5", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 3", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 4 Term 2 Report 1", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 1", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 2", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 2/5", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 3", acViewNormal, acEdit
DoCmd.OpenQuery "Update Existing Student Records Step 4 Term 2 Report 2", acViewNormal, acEdit
DoCmd.Close acForm, "Select Existing Student to Apply Reports", acSaveNo
DoCmd.SetWarnings False
retvalue = MsgBox("Completed!", vbExclamation)
End Sub

 

[pbaldy]

You've set a variable to the result of the message box. You need to test that variable and only proceed if they clicked on OK.

 

[Robbyp2001]

Dear Paul

Many thanks for your prompt reply. I can see that you are clearly correct. An argument is required in order to have the option to cancel. Unfortunately my understanding of code is rather weak. I have searched to find something similar in order to have a look at the code and how it is constructed. Unfortunately I have had no luck.

The button should simply give the option to progress the operation, or cancel the operation at the users will. It’s a safety net in case they mistakenly select the wrong record option.

I suspect that this is fairly simple for someone with a knowledge of code and I’d be very grateful if you could show me what I need to do to accomplish this.

Regards

Rob

 

[stopher]

Take a look at this. For simplicity I tested for OK rather than cancel. So if OK is clicked, the code in the IF - END IF block runs. For anything else (including cancel), the block is skipped.

Private Sub Command21_Click()

retvalue = MsgBox("This Action Will Apply ALL Reports to the Selected Student. Continue?", vbOKCancel)
If retvalue = vbOK Then
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Update Existing Student Records Step 1", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 2", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 2/5", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 3", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 4 Term 1 Report 1", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 1", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 2", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 2/5", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 3", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 4 Term 1 Report 2", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 1", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 2", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 2/5", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 3", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 4 Term 2 Report 1", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 1", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 2", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 2/5", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 3", acViewNormal, acEdit
    DoCmd.OpenQuery "Update Existing Student Records Step 4 Term 2 Report 2", acViewNormal, acEdit
    DoCmd.Close acForm, "Select Existing Student to Apply Reports", acSaveNo
    DoCmd.SetWarnings False
    retvalue = MsgBox("Completed!", vbExclamation)
End If

End Sub

hth
Chris

 

[r.harrison]

Hi,

You could also check between each query to see if a value has been returned. Create a form that looks a little like a msgbox and have a cancel button on it. Then have this set a return value.

Then check the variable before running each query.

Hope that makes sense, a little early for me!

 

[Robbyp2001]

Many thanks Stopher, you're a star! It works perfectly and so simple too. Why couldn't I see it? Ah...well. Thanks too to R Harrison for your suggestion. I'll certainly investigate.

Rob