Error Handling messing up Input Box

[jtm013]

Hello,



I have a form which allows users to edit details of specific records.
Subsequently, in the On Open event an input box requests the user to enter the record number they require.
Users were complaining of error code 3075, which was resulting from users (stupidly) pressing enter without bothering to input anything into the input box.
Subsequently, I set up error handling to display a msgbox reminding the user to enter their required record number, and then reload the input box so that the user could try again.

The Problem-
Somehow when I did this I made it so that pressing the cancel button on the input box also triggers the error message, and this results in an irritating loop of clicking cancel>error msgbox>trying to click cancel again>error msgbox again>etc.

I have provided my code below, any and all help is appreciated I am still fairly new to VBA.

Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Err_Ness
Dim ControlNumber As String


1: ControlNumber = InputBox("Please enter your lowest Control Number", _
         "Input Required", "001")


DoCmd.OpenForm "frm_pmTensileTest", acNormal, , "ControlNumber = " & ControlNumber
Exit_Ness:
    Exit Sub
Err_Ness:
    Select Case Err.Number
        Case 3075
            MsgBox "Please enter a valid control number."
            Resume 1
           
    End Select


End Sub

[pbaldy]

I'd wrap the OpenForm in a test. Since your value appears to be numeric:

If IsNumeric(ControlNumber) Then
  DoCmd.OpenForm "frm_pmTensileTest", acNormal, , "ControlNumber = " & ControlNumber
End If

You can add an Else clause to give the user a message if you want.

[jtm013]

So use an if statement in place of my error handling protocol? Sorry I'm still new to VBA.

[pbaldy]

I subscribe to the school of thought that you prevent errors when you can, the error handler is for unexpected errors. This one is easily anticipated and handled with program logic. You can put a GoTo in the Else clause as well, though that would cause your loop again.

[drexasaurus]

In the error handling for no selection I would add something like this:

Dim resp As VbMsgBoxResult
resp = MsgBox("Do you want to try input again?", vbYesNo)
If resp = vbNo Then
Exit_Ness
Else
Resume 1
Else

[jtm013]

Okay pbaldy, I am onboard with this approach.

I have amended the code as it appears below.
I still need it to loop back to the original input box after displaying the error message.
I just used 'Resume 1' to accomplish this in the error handling before, how would I do that now. Thanks!

Private Sub Form_Open(Cancel As Integer)
Dim ControlNumber As String


'This requests the user enter the desired control number
1: ControlNumber = InputBox("Please enter your lowest Control Number", _
         "Input Required", "001")


If IsNumeric(ControlNumber) Then
    DoCmd.OpenForm "frm_pmTensileTest", acNormal, , "ControlNumber = " & ControlNumber
        Else
            MsgBox "Please enter a valid control number.", vbOKOnly, "Data Required"
            
End If

End Sub

[pbaldy]

As I mentioned, you can use GoTo. That will still get you in that loop, so you may want to add a yes/no message box as in post 5. I typically wouldn't bother, I'd just make them click the button or whatever again.

[jtm013]

As I mentioned, you can use GoTo. That will still get you in that loop, so you may want to add a yes/no message box as in post 5. I typically wouldn't bother, I'd just make them click the button or whatever again.

If this was an onclick event I wouldn't bother either, but it is an onOpen event and after the error msgbox displays it is simply opening the form to a random (near as I can tell) record, which isn't going to work.
Also the GoTo method, as you mentioned, continues the looping problem when clicking the cancel button.

[pbaldy]

This code is in the open event of the same form (frm_pmTensileTest)? If so, I don't think it will work this way. The OpenForm wouldn't do anything, since the form is already opening. I'd probably set the filter property, and perhaps Cancel = True if they don't enter a valid number, which should stop the form from opening.

[jtm013]

So I realized I was being dense and went ahead and moved this code to the on click event of the switchboard button for this form.

But is there really no way to make the cancel button simply close out the input box?
I guess I don't understand why the cancel button isn't...well...cancelling.

So now it looks like this:

Private Sub Command2_Click()
Dim ControlNumber As String


'This requests the user enter the desired control number
1: ControlNumber = InputBox("Please enter your lowest Control Number", _
         "Input Required", "001")


'This actually opens the selected record
DoCmd.OpenForm "frm_pmTensileTest", , , "ControlNumber = " & ControlNumber
DoCmd.Close acForm, "frm_welcomescreen"
     
End Sub

[June7]

Try an If Then Else structure. Example from my db:

    Do
        strTestTime = InputBox("Enter a time value for test number 800 - Special Testing.")
    Loop Until IsNumeric(strTestTime) Or Len(strTestTime) = 0
    If Len(strTestTime) > 0 Then
        CurrentDb.Execute "UPDATE Tests SET TimeCharge=" & CDbl(strTestTime) & " WHERE LabNum='" & strLabNum & "' AND TestNum='800'"
    Else
        'User pressed cancel
        MsgBox "Logout cancelled.", vbOKOnly, "Cancel"
    End If

[pbaldy]

I'd still use that IsNumeric() test, which cancelling the input box will fail.

[jtm013]

My bad that last bit of code I pasted was not correct. I was still using the IsNumeric() test.

I'd still use that IsNumeric() test, which cancelling the input box will fail.

Anyway, June7 inspired me to simply make my msgbox 'cancel friendly' so now I think it will be okay for the folks who will be using it daily (famous last words).

I'm going to go ahead and marked this solved, since I think this will be sufficient.

Final code ended up looking like this:

Private Sub Command2_Click()
Dim ControlNumber As String


'This requests the user enter the desired control number
1: ControlNumber = InputBox("Please enter your lowest Control Number", _
         "Input Required", "001")


'This actually opens the selected record
If IsNumeric(ControlNumber) Then
    DoCmd.OpenForm "frm_pmTensileTest", , , "ControlNumber = " & ControlNumber
    DoCmd.Close acForm, "frm_welcomescreen"
        Else
            'User pressed cancel
            MsgBox "No Control Number, form open cancelled.", vbOKOnly, "Missing Data"
End If

End Sub

Thanks for all the assistance!

[pbaldy]

Glad you got it sorted. That's basically what I suggested in post 2.