Solved Small problem with VBA when opening on new record. (1 Viewer)

Anthony.DG

Registered User.
Local time
Today, 09:05
Joined
Oct 18, 2019
Messages
27
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

I’m here to help
Staff member
Local time
Today, 09:05
Joined
Oct 29, 2018
Messages
21,358
Which line is getting highlighted when you go to Debug mode?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:05
Joined
Feb 19, 2002
Messages
42,970
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.


Code:
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

Registered User.
Local time
Today, 19:05
Joined
Dec 7, 2018
Messages
481
Any suggestions? Here's the code
Try:
Code:
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

Immoderate Moderator
Staff member
Local time
Today, 11:05
Joined
Feb 28, 2001
Messages
26,999
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

Super Moderator
Staff member
Local time
Today, 12:05
Joined
Feb 19, 2002
Messages
42,970
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:

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

Anthony.DG

Registered User.
Local time
Today, 09:05
Joined
Oct 18, 2019
Messages
27
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.


Code:
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

Super Moderator
Staff member
Local time
Today, 12:05
Joined
Feb 19, 2002
Messages
42,970
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.
Code:
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.
 

Users who are viewing this thread

Top Bottom