Cancel Insert Statement if value is not within a set range - record inserted anyway

[rtt]

I have a form that opens to collect a value entered by the user in the field labeled, "txtSoilPh". When clicking on "Add" command button, the Before Update event checks to determine if the value is within the correct range. If it is not, a message box opens advising user of the required range. When I close the message box, the insert statement continues to execute and updates the database anyway. I am still learning VB for Access so any help would be appreciated. Below is the code that I have so far for the form.

'------------------------------------------------------------
' cmdAdd_Click
'
'------------------------------------------------------------
Private Sub cmdAdd_Click()
On Error GoTo cmdAdd_Click_Err


'Turn Off Warnings
DoCmd.SetWarnings False


 'Append the Test Results Table with the Value in the Soil Ph Text Box
    DoCmd.RunSQL "INSERT INTO [TestResults]" & _
          " VALUES (Forms![frmAddWP]![txtYr-Sample] , Forms![frmAddWP]![txtSampleID], 'WP', Forms![frmAddWP]![txtSoilPh])"


'Turn Message Box asking if you want to update the record back on
    DoCmd.SetWarnings True


 'Close the Message Box
    DoCmd.Close , ""
    
cmdAdd_Click_Exit:
    Exit Sub


cmdAdd_Click_Err:
    MsgBox Error$
    Resume cmdAdd_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![frmAddWP]![txtSoilPh] < 4 Or Forms![frmAddWP]![txtSoilPh] > 8 Then
        MsgBox "WP Range Must be Between 4 and 8"
        Me.Undo
        txtSoilPh.SetFocus
      
 End If
End Sub


Private Sub Form_Close()
Forms!frmRptCrosstab.Requery
End Sub


Private Sub Form_Current()
txtSoilPh.SetFocus
End Sub

[pbaldy]

I might put the append in an Else clause of the before update code, so you know it only runs with a valid value.

[NTC]

...... or move the Before Update code to be the primary IF statement in your On Click event......

[rtt]

Thank you both for the help. I have moved the code to the OnClick event and it appears to work as expected. Now, I have one more thing that I am wanting it to do. If I enter a value that is outside the range and I click on the message box with "OK", I want the form to stay open and the cursor to focus in the txtSoilPh. Can you help with that? Examples would be appreciated.

Thank you again.

Private Sub cmdAdd_Click()
On Error GoTo cmdAdd_Click_Err


'Turn Off Warnings
DoCmd.SetWarnings False


'Check for WP range between 4 and 8 and Give Error If in range, then Hide the Message Box


    If Forms![frmAddWP]![txtSoilPh] < 4 Or Forms![frmAddWP]![txtSoilPh] > 8 Then
        MsgBox "WP Range Must be Between 4 and 8"
        Cancel = True
        
    Else
    'Append the Test Results Table with the Value in the Soil Ph Text Box
    DoCmd.RunSQL "INSERT INTO [TestResults]" & _
          " VALUES (Forms![frmAddWP]![txtYr-Sample] , Forms![frmAddWP]![txtSampleID], 'WP', Forms![frmAddWP]![txtSoilPh])"
      
 End If




'Turn Message Box asking if you want to update the record back on
    DoCmd.SetWarnings True


 'Close the Message Box
    DoCmd.Close , ""
    
cmdAdd_Click_Exit:
    Exit Sub


cmdAdd_Click_Err:
    MsgBox Error$
    Resume cmdAdd_Click_Exit


End Sub

[pbaldy]

I would have stayed with the before update event as a more reliable place to do validation, but whatever floats your boat. Move the close into the Else clause and add a set focus line in the appropriate place:

Me.TextboxName.SetFocus

[NTC]

....and this code doesn't look correct:

'Close the Message Box
DoCmd.Close , ""

that command would be closing the main form......

[rtt]

Thank you again. I did notice that code at the bottom that was closing my form and have corrected that. I have it working now. I kept it in the OnClick event because it seemed to work much faster there than in the before update. I will take a look at that in more detail later on. I appreciate the help. Have a great day.

[pbaldy]

No problem, and welcome to the site by the way!

[rtt]

I thought that I had this worked out but I still have a small glitch and I would also like any suggestions on the best way to code this. I have a form that contains a value for pH. If the user needs to change the value, a doubleclick in the field opens a form with the focus on the pH value. The user can change the value and click Update. If the value is within the range the update goes through. If the value is not within the range, a message box shows advising that the number is out of range. Clicking OK takes the user back to the form with the focus on the pH value. In the example below Clicking update opens a message and when I click OK it goes back to the image below.



All of this works fine, but I found that when I click, "cancel" the form closes but the database updates with the incorrect value. Also, pbaldy recommended that I use the beforeupdate method.

The Code below shows The cmdCancel event, then the cmdUpdate event. The majority of my code is currently in this event. The BeforeUpdate event has no code in it now. I tried putting the check code in the beforeupdate event but it would freeze up and not process. I am still learning vba so any help with examples would be greatly appreciated.

Private Sub cmdCancel_Click()
On Error GoTo cmdCancel_Click_Err

     DoCmd.Close , ""

cmdCancel_Click_Exit:
    Exit Sub

cmdCancel_Click_Err:
    MsgBox Error$
    Resume cmdCancel_Click_Exit

End Sub


Private Sub cmdUpdate_Click()
On Error GoTo cmdUpdate_Click_Err


'Turn Off Warnings
DoCmd.SetWarnings False

 '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
    Else
    
     '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 , ""
    End If
      
    '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)


End Sub

[pbaldy]

Thus my statement in post 5.

There's no validation happening now when you click on your cancel button. You can add similar code to it too, but the before update event catches both. Not sure why you'd have a performance issue with it there. That event is very commonly used for data validation.

[rtt]

Thus my statement in post 5.

There's no validation happening now when you click on your cancel button. You can add similar code to it too, but the before update event catches both. Not sure why you'd have a performance issue with it there. That event is very commonly used for data validation.

OK, thanks. I have tried this below. It does respond just as quickly and now when I click the OK button on the message box that tells me that I'm out of range, the form closes. The data does not change. So, I'm very close now, I think. Again, I will say that I'm learning this and so feel free to mark up the code or offer an example.

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






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
        
    End If
   




End Sub

[John_G]

Hi -

Looking at the code in post #4 above:

The code is attached to a command button, not a form event. In this case, the "Cancel" after the error check means nothing - there is no event to cancel, so the code keeps running.

Replace the Cancel = True with Exit Sub after the Msgbox error message, because you do not want to proceed if the data is not valid.

HTH

John