Form OnCurrent Event

[Guest]

I have a form on which I have a control [County] that I want to show/hide
based on the value of [ExpAcct#]. I have placed the following code in the On
Current event:

Private Sub Form_Current()
Me.[County].Visible = ((Me.[ExpAcct#] = "93030") Or (Me.[ExpAcct#] = "93040"))
End Sub

When I move from record to record the event works well. However when I
click on the "Add Record" button I get the message:
"Run time error '94':
Invalid Use of Null"

Can some please let me know what is wrong with my code.

Thanks

 

[Guest]

I copied and pasted what you provided. When I attempted to open the form I
received a "Compile Error: Syntax Error" message.

What I have is as follows for the form On Current event:
Private Sub Form_Current()
Me.[County].Visible = ((Nz(Me.[ExpAcct#],"") = "93030") Or
(Nz(Me.[ExpAcct#],"") = "93040"))
End Sub

The "Private Sub Form_Current() is highlighted in yellow with an arrow.

Thanks for your help.

Comparing Null with a string will return Null (that returned with empty
record) instead of False, and you can't set the Visible property with a Null
value.

Use the Nz function to replace Null with empty, and that will return false

Private Sub Form_Current()
Me.[County].Visible = ((Nz(Me.[ExpAcct#],"") = "93030") Or
(Nz(Me.[ExpAcct#],"") = "93040"))
End Sub

--
Good Luck
BS"D

I have a form on which I have a control [County] that I want to show/hide
based on the value of [ExpAcct#]. I have placed the following code in the On
Current event:

Private Sub Form_Current()
Me.[County].Visible = ((Me.[ExpAcct#] = "93030") Or (Me.[ExpAcct#] = "93040"))
End Sub

When I move from record to record the event works well. However when I
click on the "Add Record" button I get the message:
"Run time error '94':
Invalid Use of Null"

Can some please let me know what is wrong with my code.

Thanks

 

[Guest]

Comparing Null with a string will return Null (that returned with empty
record) instead of False, and you can't set the Visible property with a Null
value.

Use the Nz function to replace Null with empty, and that will return false

Private Sub Form_Current()
Me.[County].Visible = ((Nz(Me.[ExpAcct#],"") = "93030") Or
(Nz(Me.[ExpAcct#],"") = "93040"))
End Sub

 

[Guest]

Try this version. You will have to decide whether to make County visible or
not for new records. I will write it based on the logic of the original.

Private Sub Form_Current()
If Me.NewRecord Then
Me.[County].Visible = False
Else
If Me.[ExpAcct#] = "93030" Or Me.[ExpAcct#] = "93040" Then
Me.[County].Visible = True
Else
Me.[County].Visible = False
End If
End If
End Sub

--
Dave Hargis, Microsoft Access MVP

I have just begun using 2007.

Thanks for all your help.

Make sure the code is in one line.
Also, what version of Access are you using

Private Sub Form_Current()
Me.[County].Visible = ((Nz(Me.[ExpAcct#],"") = "93030") Or
(Nz(Me.[ExpAcct#],"") = "93040"))
End Sub

--
Good Luck
BS"D

I copied and pasted what you provided. When I attempted to open the form I
received a "Compile Error: Syntax Error" message.

What I have is as follows for the form On Current event:
Private Sub Form_Current()
Me.[County].Visible = ((Nz(Me.[ExpAcct#],"") = "93030") Or
(Nz(Me.[ExpAcct#],"") = "93040"))
End Sub

The "Private Sub Form_Current() is highlighted in yellow with an arrow.

Thanks for your help.

:

Comparing Null with a string will return Null (that returned with empty
record) instead of False, and you can't set the Visible property with a Null
value.

Use the Nz function to replace Null with empty, and that will return false

Private Sub Form_Current()
Me.[County].Visible = ((Nz(Me.[ExpAcct#],"") = "93030") Or
(Nz(Me.[ExpAcct#],"") = "93040"))
End Sub

--
Good Luck
BS"D


:

I have a form on which I have a control [County] that I want to show/hide
based on the value of [ExpAcct#]. I have placed the following code in the On
Current event:

Private Sub Form_Current()
Me.[County].Visible = ((Me.[ExpAcct#] = "93030") Or (Me.[ExpAcct#] = "93040"))
End Sub

When I move from record to record the event works well. However when I
click on the "Add Record" button I get the message:
"Run time error '94':
Invalid Use of Null"

Can some please let me know what is wrong with my code.

Thanks

 

[Guest]

Make sure the code is in one line.
Also, what version of Access are you using

Private Sub Form_Current()
Me.[County].Visible = ((Nz(Me.[ExpAcct#],"") = "93030") Or
(Nz(Me.[ExpAcct#],"") = "93040"))
End Sub

--
Good Luck
BS"D

I copied and pasted what you provided. When I attempted to open the form I
received a "Compile Error: Syntax Error" message.

What I have is as follows for the form On Current event:
Private Sub Form_Current()
Me.[County].Visible = ((Nz(Me.[ExpAcct#],"") = "93030") Or
(Nz(Me.[ExpAcct#],"") = "93040"))
End Sub

The "Private Sub Form_Current() is highlighted in yellow with an arrow.

Thanks for your help.

Comparing Null with a string will return Null (that returned with empty
record) instead of False, and you can't set the Visible property with a Null
value.

Use the Nz function to replace Null with empty, and that will return false

Private Sub Form_Current()
Me.[County].Visible = ((Nz(Me.[ExpAcct#],"") = "93030") Or
(Nz(Me.[ExpAcct#],"") = "93040"))
End Sub

--
Good Luck
BS"D

I have a form on which I have a control [County] that I want to show/hide
based on the value of [ExpAcct#]. I have placed the following code in the On
Current event:

Private Sub Form_Current()
Me.[County].Visible = ((Me.[ExpAcct#] = "93030") Or (Me.[ExpAcct#] = "93040"))
End Sub

When I move from record to record the event works well. However when I
click on the "Add Record" button I get the message:
"Run time error '94':
Invalid Use of Null"

Can some please let me know what is wrong with my code.

Thanks

 

[missinglinq via AccessMonster.com]

Why not simply do the formatting only when the record isn't new?

Private Sub Form_Current()
If Not(Me.NewRecord) Then
Me.[County].Visible = ((Me.[ExpAcct#] = "93030") Or (Me.[ExpAcct#] = "93040")
)
End If
End Sub