Screening Inputted Values in Control - Runtime Error 94 Invalid Use of Null

[dgj32784]

Programming novice here...but I'm slowly gaining steam. I have a form with two controls on it for the user to enter the geographic coordinates of the location they are at when collecting information in the field. The controls are named "intXCoordinate" and "intYCoordinate". I've tried to get versions of the code below to work on a couple different events, and came across a forum discussion that advocated the On Exit event. So that is where I'm at now. Basically, I want the user to get a message box if they have entered a value that is outside the geographic range for the state. FYI - There is an input mask on the source field to ensure format consistency. If the user enters a value outside the range, then the message box will pop up and the value that was entered into the control will be cleared. This appears to work with the following code. However, if I open the form and try to go to Design View, I get the Runtime Error ‘94’ saying Invalid Use of Null. I’ve tried using some Error Handling but am fumbling in the dark. How do I achieve my message box objectives programmatically without getting an error every time I want to switch to Design View? Should this (or similar) code be on a different event? What am I missing?

Thanks,
David

Private Sub intXCoordinate_Exit(Cancel As Integer)
    'Declare variables
    Dim intXCoordinateValue As Integer
 
    'Define values for variables
    intXCoordinateValue = intXCoordinate
 
    'If statement to ensure X Coordinate is within range for the state
    If intXCoordinateValue = Null Then
        MsgBox "You have not entered an X Coordinate."
 
    ElseIf intXCoordinateValue < -84.321824 Then
        MsgBox "The value entered is outside the parameters for the state." & vbNewLine & vbNewLine & "X Coordinate must be between -84.321824 and -75.459656."
        intXCoordinate.Value = Null
        Cancel = True
 
    ElseIf intXCoordianteValue > -75.459656 Then
        MsgBox "The value entered is outside the parameters for the state." & vbNewLine & vbNewLine & "X Coordinate must be between -84.321824 and -75.459656."
        intXCoordinate.Value = Null
        Cancel = True
 
    Else: DoCmd.GoToControl "intYCoordinate"
 
    End If
 
End Sub

 

[pbaldy]

A couple of problems. This test is incorrect:

If intXCoordinateValue = Null Then

and will never be met anyway (which is the source of your error). It's incorrect because nothing is ever "=" to Null, which can perhaps best be defined as unknown. You want to use the IsNull() function to test, or this which tests for both Null and a zero length string:

If Len(intXCoordinateValue & vbNullString) = 0 Then

I say it will never be met because an Integer variable can't hold a Null, so this line is probably the source of your error:

intXCoordinateValue = intXCoordinate

You can declare the variable as Variant, which is the only type that can handle a Null.

 

[dgj32784]

Thanks Paul. I changed my coordinate field type to string since it doesn't really matter for my dbase, which makes the Null options in the if statement a little easier. Now I'm trying to get a simple command button to clear the values in various string controls on the form. Can I simply put

strMyControl.Value = Null

on the click event for that command button? I've done this and it appears to work but I have to click the buttom multiple times. What does that mean?

 

[pbaldy]

Not sure, but it may be a refresh issue. Try using "Me", which I've never had trouble with:

Me.ControlName = Null

 

[dgj32784]

Okay, that also works, but still requires two clicks of the command button to clear the strXCoordinate value if strYCoordinate is empty. If both of these controls have value, it only takes one click. Is there a way to make it clear the values regardless of whether there is a value in both controls? Current code:

Private Sub cmdClearEnteredCoordinates_Click()
 
    Me.strXCoordinate = Null
    Me.strYCoordinate = Null
 
End Sub

 

[pbaldy]

I'm not sure why that would happen. It should run each line regardless of the current contents of the controls. It still may be a refresh issue. Try adding this after those two lines:

Me.Repaint

If that doesn't work, perhaps one of these:

Me.Refresh
Me.Requery

 

[dgj32784]

10-4. I've tried all three options and each still requires a double click. I think it has something to do with some other code on the form that I have inserting control values directly into tables using VBA, with one of those controls being the primary key for multiple tables. I'm going to flip the order of operations on the form for the user and see if I can get all of the programming operations to play nice.

Thanks for your input.