Solved Small problem with VBA when opening on new record.

[Anthony.DG]

So I have a bit of code on my form to make a textbox invisible when the record is equal to zero and visible if >0.01
. Code works fine when opening a previous record. When a new record opens I get runtime error 2427.
I'm still new to VBA so I have no clue what to do differently. Any suggestions? Here's the code

If Me.txtMatSaved = "0" Or IsNull(Me.txtMatSaved) Then
Me.txtMatSaved.Visible = False
ElseIf Me.txtMatSaved >= "0.01" Then
Me.txtMatSaved.Visible = True
End If

End Sub

 

[theDBguy]

Which line is getting highlighted when you go to Debug mode?

 

[Anthony.DG]

Which line is getting highlighted when you go to Debug mode?

First one is the one that debug stops on.

 

[Eugene-LS]

Any suggestions? Here's the code

Try:

If Val(Nz(Me.txtMatSaved,0)) = 0 Then
    Me.txtMatSaved.Visible = False
ElseIf Val(Me.txtMatSaved) >= 0.01 Then
    Me.txtMatSaved.Visible = True
End If

 

[The_Doc_Man]

The error you reported, 2427, says "the expression has no value" which is an error sometimes returned from functions like "IsNull" - and which indicates that there is something wrong with the actual argument used in the function call. The suggestions made by others are all good, but I will add a method of diagnosing the problem.

Put a breakpoint in your code on that line containing the IsNull function. When the code breaks at that point, open the Immediate window (it's an option under menu bar item VIEW) and type Debug.Print Me.txtMatSaved to see what Access thinks is in that control. Odds are that it isn't defined properly in some way.

 

[Eugene-LS]

it's an option under menu bar item VIEW ...

Immediate window is one of the most useful code debugging tools, it can also be displayed by pressing combination [Ctrl]+[G] on the keyboard

 

[Anthony.DG]

1. Don't run the code for a new record
2. Don't enclose numeric values in quotes.
3. Be careful to cover all cases. You omitted one.

If Me.NewRecord = True Then
Else
    If Me.txtMatSaved = 0 Or IsNull(Me.txtMatSaved) Then
        Me.txtMatSaved.Visible = False
    Else
        If Me.txtMatSaved >= 0.01 Then
            Me.txtMatSaved.Visible = True
        Else
            Me.txtMatSaved.Visible = False
        End If
    End If
End If
           
End If

End Sub

So I tried this and ran into some weird errors. The first part I think is what I needed for sure. Did a little editing to it, changed the Else to ElseIf (i know someone said ElseIf make your code sloppy but its working with and for this instance)

If Me.NewRecord = True Then
ElseIf Me.txtMatSaved = "0" Or IsNull(Me.txtMatSaved) Then
Me.txtMatSaved.Visible = False
ElseIf Me.txtMatSaved >= 0.01 Then
Me.txtMatSaved.Visible = True
Else
Me.txtMatSaved.Visible = False

End If
End Sub




Works exactly like it needs to. In the background without a pep. Thank you so much! And thank you to everyone with all of the useful information about error handling and everthing! You guys are awesome!!