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.

 

[Pat Hartman]

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

 

[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.

 

[Pat Hartman]

ElseIf's make your code sloppy. Many people forget the final else path

Also, Access remembers the property setting from one record to the next.

If you want to be efficient and negatives either don't exist or they shouldn't show, then:

If Nz(Me.txtMatSaved,0)  <= 0 Then
    Me.txtMatSaved.Visible = False
Else
    Me.txtMatSaved.Visible =True
End If

 

[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!!

 

[Pat Hartman]

ElseIF makes you sloppy because at least 95% of the code we see here that uses ElseIF omits the last else. People also don't properly indent them so they are usually unreadable. The best statement is the one I posted later with only a single IF. The first If (not the one about the new record) handles THREE conditions. It handles Null, 0 and <0. That leaves the Else to handle all the positive, non-zero values.

If Me.NewRecord = True Then
Else
    If Nz(Me.txtMatSaved,0) <= 0 Then
        Me.txtMatSaved.Visible = False
    Else
        Me.txtMatSaved.Visible =True
    End If
End If

Are you sure your txtMatSaved is a string? If it isn't , you should NOT be enclosing the numeric values in quotes.