Help with vbRetryCancel - defining vbCancel Range Check

[thr33xx]

Hello all,

I am still learning VB so I apologize if this comes across as a mindless request. I am implementing a range check for a data entry database. I am asking the user to input the month. If they enter anything >12 then the msgbox pops up, with vbRetry clearing the invalid value and requesting the user input a valid value. The user also has the option to select vbCancel. If the user selects vbCancel from the msgbox, I would like the values for all fields of that record to be cleared or "nulled" so that the user may re-enter in the correct values for all fields. Below I have included the VB code I have so far. The break in red is where I believe the vbCancel will appear. Unfortunately, I am having a difficult time coding this. Any help would be greatly appreciated! Thanks

If Me.NewMonth > 12 Then
If MsgBox("The month entered is invalid, select retry and enter a valid month", vbRetryCancel) = vbRetry Then
Me.NewMonth = Null
'i think this is where vbcancel will go?'
Exit Sub
End If
End If

 

[John Big Booty]

Try;

If Me.NewMonth > 12 Then
     If MsgBox("The month entered is invalid, select retry and enter a valid month", vbRetryCancel) = vbRetry Then
          Me.NewMonth = Null
     Else
          Cancel = True
          Exit Sub
     End If
End If

 

[thr33xx]

I have tried this, but it comes back with a

Compile error:
Variable not defined

Try;

If Me.NewMonth > 12 Then
     If MsgBox("The month entered is invalid, select retry and enter a valid month", vbRetryCancel) = vbRetry Then
          Me.NewMonth = Null
     Else
          Cancel = True
          Exit Sub
     End If
End If

 

[John Big Booty]

Does one of the lines of code get highlighted when you get the error?

 

[gemma-the-husky]

probably just newmonth or me!newmonth

not me.newmonth

-----
setting newmonth to null may or may not be appropriate - it depends whether you have a bound or an unbound form.

 

[John Big Booty]

Me.ControlName worked in my sandbox

 

[gemma-the-husky]

Strange - I thought it was bang for a control, not dot.

Maybe its to do with the underlying recordset. I think we had this discussion revcently, to be honest.

 

[John Big Booty]

Now that you mention it; I am getting a distinct feeling of deja vu

 

[boblarson]

Strange - I thought it was bang for a control, not dot.

It can be dot for either depending on how it is set up. But I usually use the dot since Intellisense will work with that.

So, I believe we have not established which event this is on so if it isn't in the Before Update event or the On Unload event where there is a Cancel As Integer defined then using

Cancel = True

will generate an error unless you have declared a variable named Cancel in the procedure. So the question to thr33xx is - Which event do you have this on?

 

[thr33xx]

"Cancel = " is the area being highlighted during debugging.

I have decided to attach the entire code which I am working with. For clarification, this is being setup as an ONCLICK add record event.

 

[boblarson]

That's what I said - it isn't in a procedure that is like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

so you should just GET RID OF IT completely and use:

If Me.NewMonth > 12 Then
     If MsgBox("The month entered is invalid, select retry and enter a valid month", vbRetryCancel) = vbRetry Then
          Me.NewMonth = Null
     Else
[B][COLOR=red]         Exit Sub[/COLOR][/B]
     End If

End If[/code]

 

[thr33xx]

Hi Bob,

Thanks for the suggestion! It semi worked!

Here is the code I ended up using - Had to add another "Exit Sub" simply because when I selected "Retry" using your code, it continued to add the record with the month as null.

If Me.NewMonth < 1 Or Me.NewMonth > 12 Then
     If MsgBox("The month entered is invalid, select retry and enter a valid month", vbRetryCancel) = vbRetry Then
          Me.NewMonth = Null
          Exit Sub
     Else
         Exit Sub
     End If
End If

Thanks for the help guys! Really do appreciate it! Thank you again!

That's what I said - it isn't in a procedure that is like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

so you should just GET RID OF IT completely and use:

If Me.NewMonth > 12 Then
     If MsgBox("The month entered is invalid, select retry and enter a valid month", vbRetryCancel) = vbRetry Then
          Me.NewMonth = Null
     Else
[B][COLOR=red]         Exit Sub[/COLOR][/B]
     End If

End If[/code]