Using VBA instead of Validation Rule

[Mearntain]

I have been trying to use to a validation rule on 2 fields, and then using the OnError event to tell the program what to do if the validation rule fails. I could not get this to work and tried several different ways. I am thinking the best way to do this is to make this happen with VBA alone, and throw the validation rule from the table out.



Heres what I need to do:

I have 1 textbox in a form that needs to be monitored- MO_ID

If the user enters a number greater than 99999 and less than 999999, I need a pop up message box to appear, default the MO_ID text box to being blank, and then go to the control box MO_ID for re-entry.



Here's my best attempt at the code, but every time it sends the pop up box and never will go to the else statment, regardless of what was in the textbox:

If Me.MO_ID > 99999
       MsgBox("You can not enter an employee number here!")
       Me.MO_ID = ""
       DoCmd.GoToControl(MO_ID)
Else
      GoTo Main_Code
End If
End Sub

Thanks for any input!

[June7]

I don't build validation in tables - only on forms.

Is MO_ID a text type field.

[Mearntain]

I'm starting to feel the same way and don't plan on using the validation rule anymore myself.

MO_ID is an Integer field

[Mearntain]

I just noticed a typo (I said it backwards earlier)- basically, the only values for MO_ID to be ok is 99999 < MO_ID < 999999

[June7]

If Me.MO_ID < 99999 Or Me.MO_ID > 999999 Then

Why not a combobox with LimitToList set to Yes?

[Mearntain]

Combo box won't work, I need it to be as described in my OP. This field is entered via a barcode scanner with automatic carriage return programmed in.

[Mearntain]

Some reason my original code that I posted still doesn't work properly. What would be the best event to use this after? I was using When Lost Focus.

[June7]

Don't know why combobox wouldn't work with barcode scanner.

Try BeforeUpdate event.

[Mearntain]

Ill give that a shot with that event and see what happens. If i used a combobox, I would need over 1 million possibilities to appear in the drop down menu.

[Mearntain]

I tried running the code with the before update event, and it only partially works for me. It give the msgbox pop up as its supposed to, but it does not return to the text box and it does not clear the data from the text box either. It gives the error:

Run-time error '-2147352567 (80020009)

The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing the Microsoft Office Access from saving data in the field.

How do I prevent this error from popping up? Also, how do I make it properly default the text box back to being blank if it is beyond the desired range of the results. I thought I had the correct code for that to happen, but apparently not.

[June7]

Post your revised code for analysis. Review http://www.blueclaw-db.com/access_ev...foreupdate.htm

[Mearntain]

If I try this on BeforeUpdate for the Form, nothing works at all. The code below (for the field on the form) only works partially and gives the error that I posted above.

Private Sub Text0_BeforeUpdate(Cancel As Integer)

If Me.Text0 > 99999 Then
       MsgBox ("You can not enter a MO number here!")
       DoCmd.Close acForm, "Query Form"
       DoCmd.OpenForm "Query Home"
       Cancel = True
Else
      MsgBox ("Sample Code - Working Properly")
      
End If
End Sub

[June7]

What does 'partially' mean - under what conditions does it not work and what happens?

Code looks good to me.

If you want to provide db for analysis, follow instructions at bottom of my post.

[ssanfu]

I prefer to use the Case construct, instead of IF() in cases like this.

Private Sub Text0_BeforeUpdate(Cancel As Integer)
    Dim MinNum As Long
    Dim MaxNum As Long
    Dim EnteredNum As Long


    MinNum = 99999
    MaxNum = 999999

    '    EnteredNum = Me.MO_ID
    EnteredNum = Me.Text0

    Select Case EnteredNum
        '99999 < MO_ID < 999999
        Case Is < MinNum, Is > MaxNum
            'Outside the limits, so
            msgbox ("You can not enter a MO number here!")
            DoCmd.Close acForm, "Query Form"
            DoCmd.OpenForm "Query Home"
            Cancel = True
            
        Case Else
            'Inside the limits, so
            msgbox ("Sample Code - Working Properly")
    End Select

End Sub

However, you said

MO_ID is an Integer field

and that the minimum number is GT 99,999 and the maximum number is LT 999,999.

Given that:
Integer variables are stored as 16-bit (2-byte) numbers ranging in value from -32,768 to 32,767
Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647

, shouldn't the field type for "MO_ID" be a Long Integer??? The code might execute, but the number couldn't be stored in an integer field.

I'm just sayin........

[Mearntain]

What does 'partially' mean - under what conditions does it not work and what happens?

Code looks good to me.

If you want to provide db for analysis, follow instructions at bottom of my post.

The only problem I am having is that if the text box has a value that is not acceptable for the code, I would like it to defualt that text box to being blank and then return the control back to that box. If I try to do the code for that, it gives an error, regardless of whether I use my original code or add it in to yours.

I have been trying to do it like this:

Private Sub Text0_BeforeUpdate(Cancel As Integer)
    Dim MinNum As Long
    Dim MaxNum As Long
    Dim EnteredNum As Long


    MinNum = 99999
    MaxNum = 999999

    '    EnteredNum = Me.MO_ID
    EnteredNum = Me.Text0

    Select Case EnteredNum
        '99999 < MO_ID < 999999
        Case Is < MinNum, Is > MaxNum
            'Outside the limits, so
            msgbox ("You can not enter a MO number here!")
            Me.text0 = ""
                   DoCmd.GoToControl "text0"
            Cancel = True
            
        Case Else
            'Inside the limits, so
            msgbox ("Sample Code - Working Properly")
    End Select

End Sub

But it doesnt default to being blank and doesnt go to the control, not sure why