Want form to stay open after clicking OK on Warning Message

[rtt]

I have a form with a DblClick Event that opens another form to enter data and update. The double click works fine and the other form opens with the focus set on the field requiring the input.

When I enter a number that is not within the range and click Update, the OnClick event for the update button executes an update SQL command and then the BeforeUpdate event runs a validation. If the entry is not within the specified range, a message box reminds the user of the required range. When the user clicks OK on the message box, the message box closes but the form also closes.

I want the user to click "OK" on the message box and the message box close but leave the form open with the set focus on the same field it was on when it first opened. The code for the update button on click and the before update is below:

Private Sub cmdUpdate_Click()
On Error GoTo cmdUpdate_Click_Err


'Turn Off Warnings
DoCmd.SetWarnings False


     'Update the Test Results Table with the Value in the Soil Ph Text Box
            DoCmd.RunSQL "UPDATE [testResults]" & _
        "SET [Reported Conc (Calib)] = Forms![frmUpdateWP]![txtSoilPh]" & _
        "WHERE [TestYear] = 'Forms![frmUpdateWP]![txtYr-Sample]' And [Sample ID] = 'Forms![frmUpdateWP]![txtSampleID]'"
    
    'Close the Message Box
   
            DoCmd.Close , ""
    
      
    'Turn Message Box asking if you want to update the record back on
    DoCmd.SetWarnings True
      
 
cmdUpdate_Click_Exit:
    Exit Sub


cmdUpdate_Click_Err:
    MsgBox Error$
    Resume cmdUpdate_Click_Exit
End Sub

Below is the code for the before update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
'Check for WP range between 4 and 8 and Give Error If in range, then Hide the Message Box


    If Forms![frmUpdateWP]![txtSoilPh] < 4 Or Forms![frmUpdateWP]![txtSoilPh] > 8 Then
        MsgBox "WP Range Must be Between 4 and 8"
        'Forms![frmUpdateWP]![txtSoilPh].Undo
        Cancel = True
        Me.txtSoilPh.SetFocus
       Exit Sub
    End If
  
End Sub

This has me stumped. I am still a novice with VBA. I think that it has to do with where the DoCmd.Close is in the cmd_UpdateClick event. If my code needs to change, please show me how it should change. Thanks for the help.

[ItsMe]

You can comment out code by adding an apostrophe at the beginning of the line. Try

' 'Close the Message Box

' DoCmd.Close , ""


and see what happens

[rtt]

You can comment out code by adding an apostrophe at the beginning of the line. Try

' 'Close the Message Box

' DoCmd.Close , ""


and see what happens

No, that didn't work. When I do that, the message box doesn't show up and nothing happens. I have to click on cancel, then the message box shows up and then when I click OK, everything closes.

[ItsMe]

I did not test it but I see the Docmd.Close line doing nothing more than closing the current form. Not sure why commenting this line is causing the msgbox to display.

What is the text that the message box displays, the msgbox that is not showing when you commented the line?

The only line of code I see with a msg box is
MsgBox "WP Range Must be Between 4 and 8"

[John_G]

You have the check for an invalid value for [txtsoilPH] in the wrong place. It should be in the onClick event fot the update button, at the top before you do the update:

If Forms![frmUpdateWP]![txtSoilPh] < 4 Or Forms![frmUpdateWP]![txtSoilPh] > 8 Then
MsgBox "WP Range Must be Between 4 and 8"
Me.txtSoilPh.SetFocus
Exit Sub
End If

You don't need the Undo or the Cancel.

As has been pointed out, your docmd.close is closing the current form, not the message box. As far as I know, a message box cannot be closed with VBA code - you have to click OK.

[Missinglinq]

I've read this thread a number of times, and get dizzier and dizzier with each reading! You speak of updating a Table using a SQL Statement, but then you use code in the Form_BeforeUpdate event to validate some data! Is this a Bound Form or is it Unbound?

If it's Unbound (as it sounds from the use of SQL) the Form_BeforeUpdate event, and its code, should never fire.

Of course, it could just be me...my back is being particularly bad, today, and I have taken quite a bit of pain medicine!

Linq ;0)>

[ItsMe]

I've read this thread a number of times, and get dizzier and dizzier with each reading!...

You are not alone. Sorry to hear you are not feeling well and hope you recover soon.

[Missinglinq]

Thanks for your well wishes...but, unfortunately, it's been like this for 8 years now. Not going to get better, but, on the up side, it's why I can spend 12-16 hours a day here and on four or five other Access forums! Keeps my mind from going the way of my spine!

Also, very glad that my confusion is not drug-induced!

Linq ;0)>

[rtt]

I've read this thread a number of times, and get dizzier and dizzier with each reading! You speak of updating a Table using a SQL Statement, but then you use code in the Form_BeforeUpdate event to validate some data! Is this a Bound Form or is it Unbound?

If it's Unbound (as it sounds from the use of SQL) the Form_BeforeUpdate event, and its code, should never fire.

Of course, it could just be me...my back is being particularly bad, today, and I have taken quite a bit of pain medicine!

Linq ;0)>

I hope that you are feeling better today. I am still trying to learn VBA so I may be going about this all wrong. frmUpdateWP is bound to a query that returns (1) Year (2) SampleID (3) Element and (4) The value that is updated by the user. The Year and Sample ID are pulled from a form that is bound to a cross tab query. The Element is "WP" and it is in the query.

I moved the test to before the update query. If I put a value that is out of range it stops it and gives the message box. If I close the message box and click cancel, it gives the error message again, I close the message box and the form closes as expected. Normally a user would not click cancel on the form but would correct the entry and hit update again. That all works fine.

I originally had the code to validate as part of an IF THEN ELSE statement and saw some message boards that suggested validating with a before update event.

I am about to the point of throwing in the towel. If anyone offers tutorial via screen sharing, send me a private message.

 Private Sub cmdUpdate_Click()
On Error GoTo cmdUpdate_Click_Err


'Turn Off Warnings
DoCmd.SetWarnings False


 If Forms![frmUpdateWP]![txtSoilPh] < 4 Or Forms![frmUpdateWP]![txtSoilPh] > 8 Then
MsgBox "WP Range Must be Between 4 and 8"
Me.txtSoilPh.SetFocus
Exit Sub
End If


     'Update the Test Results Table with the Value in the Soil Ph Text Box
            DoCmd.RunSQL "UPDATE [testResults]" & _
        "SET [Reported Conc (Calib)] = Forms![frmUpdateWP]![txtSoilPh]" & _
        "WHERE [TestYear] = 'Forms![frmUpdateWP]![txtYr-Sample]' And [Sample ID] = 'Forms![frmUpdateWP]![txtSampleID]'"
    
     'Close the Form
   
            DoCmd.Close , ""
      
    'Turn Message Box asking if you want to update the record back on
    DoCmd.SetWarnings True
      
 cmdUpdate_Click_Exit:
    Exit Sub


cmdUpdate_Click_Err:
    MsgBox Error$
    Resume cmdUpdate_Click_Exit
End Sub






Private Sub Form_BeforeUpdate(Cancel As Integer)
 
 'Check for WP range between 4 and 8 and Give Error If in range, then Hide the Message Box


    If Forms![frmUpdateWP]![txtSoilPh] < 4 Or Forms![frmUpdateWP]![txtSoilPh] > 8 Then
        MsgBox "WP Range Must be Between 4 and 8"
        'Forms![frmUpdateWP]![txtSoilPh].Undo
        Cancel = True
        Me.txtSoilPh.SetFocus
       Exit Sub
    End If


 End Sub

[ItsMe]

...Want form to stay open after clicking OK on Warning Message...

Comment out the Docmd.Close line of code or simply delete it.

[Missinglinq]

The Messagebox warning is popping twice because you have it in the cmdUpdate_Click() event and in the Form_BeforeUpdate event!