Error with Private Sub Form_Current()

[Walter]

I am new to VBA and I am trying to write this simple code to made the JP8
product another color, but I keep getting an error. When I go to the form
it returns me to the VBA and highlight the Private Sub Form_Current(). I
copied most of the code from a book and changed their label to Product.

Thank you in advance for your help.

Walter

Private Sub Form_Current()
If [Product] = JP8 Then
'Make control background opaque.
[Product].BackStyle = 1
'Make control background color white.
[Product].BackColor = vbWhite
'Make font color red.
[Product].ForeColor = vbRed
Else
'Make control background transparent.
[Product].BackStyle = 0
'Make font color black.
[Product].ForeColor = vbBlack

End If

End Sub

 

[RonaldoOneNil]

I assume JP8 is the text in your product field. You need to put quotes around
it
If [Product] = "JP8" Then

 

[Rob Parker]

Hi Walter,

Assuming that JP8 is one possible value (text datatype) for the
field/control Product, then you need to set it as a string when you do the
test, thus:
If [Product] = "JP8" Then
....

If you check the option for "Require Variable Declaration" in the VBA editor
you would get an error when you compile your code - before you try to run
it. It will report "Compile error. Variable not defined". When you set
this option, at the top of each code module the line "Option Explicit" will
appear. Use it - it's worth its weight in gold; it will show you typo
errors when entering/compiling code, before you even try to run it.

HTH,

Rob

 

[Jack Leach]

Aside from enclosing the JP8 in quotes to let vba know that it's a string
"JP8", you may also run into an error if the Product is null... you won't be
able to try that value if it's null. There's a function called Nz() that
returns a specific value if the field happens to be null... you'll want to
use this as a wrapper for your field...


If Nz([Product], "") = "JP8"


this tells vba that if the field [Product] is null, use a zero length string
"" as the comparitor instead (nulls cannot be used as a comparitor)

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Walter]

I thank y'all for the replies, but my code still won't work. I Google
problems with Access 2007 BackStyle and several sites reported this problem.
I don't understand how to fix the problem. Any suggestion?

Fix to BackStyle problem. KB926713

Walter

 

[Douglas J. Steele]

If you look at the Properties sheet for the form, does it say [Event
Procedure] as the value for the Current property? Assuming it does, and you
click on the ellipsis (...) to the right of the property, are you taken into
the code you're showing below? If not, get rid of your existing code, and
put it where you're taken when you click on the ellipsis.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I thank y'all for the replies, but my code still won't work. I Google
problems with Access 2007 BackStyle and several sites reported this
problem.
I don't understand how to fix the problem. Any suggestion?

Fix to BackStyle problem. KB926713

Walter

I am new to VBA and I am trying to write this simple code to made the JP8
product another color, but I keep getting an error. When I go to the
form
it returns me to the VBA and highlight the Private Sub Form_Current(). I
copied most of the code from a book and changed their label to Product.

Thank you in advance for your help.

Walter

Private Sub Form_Current()
If [Product] = JP8 Then
'Make control background opaque.
[Product].BackStyle = 1
'Make control background color white.
[Product].BackColor = vbWhite
'Make font color red.
[Product].ForeColor = vbRed
Else
'Make control background transparent.
[Product].BackStyle = 0
'Make font color black.
[Product].ForeColor = vbBlack

End If

End Sub

 

[Linq Adams via AccessMonster.com]

If [Product] = "JP8"

will not pop an error if [Product] is Null. Using NZ() in this context would
only be necessary if you were trying to assign [Product] to a field whose
Datatype prohibits Null values.

Knowing how these kind of things tend to go, Walter, it would probably be
helpful, in conjunction with Doug's suggestion, to see the actual code from
your last attempt.

 

[Walter]

Thank you for your help. I posted my updated code below. I added the code
to the form On Current. The Text box to update the product code JP8 with the
colors don't have the On Current available in the Event details. I use this
form to update the inventory in my database. The Product data is stored in
the Product table and then Inventory in the Inventory table. The product is
a foreign key int he Inventory table so the product data stored in the
Inventory table is the Primary key form the Product table. Will this create
problem with using color fonts in the Inventory update form.?

The error I get is "Compile error: Method or data member not found"


Option Compare Database
Option Explicit


Private Sub Form_Current()
If Nz([Product], "") = "JP8" Then
'Make control background opaque.
[Product].BackStyle = 1
'Make control background color white.
[Product].BackColor = vbWhite
'Make font color red.
[Product].ForeColor = vbRed
Else
'Make control background transparent.
[Product].BackStyle = 0
'Make font color black.
[Product].ForeColor = vbBlack

End If

End Sub


Again,

Thank you,

Walter

 

[Rob Parker]

Hi Walter,

What you are wanting to do is change properties of a control on a form -
it's probably named Product, the same name as the field it's bound to in the
form's RecordSource. When you use code with the name in square brackets,
you are explicitly referring to the field, and the field does not have the
properties you are trying to set. Try renaming the textbox contorl
displaying the Product data to txtProduct, and change your code to:

Private Sub Form_Current()
If [Product] = "JP8" Then
'Make control background opaque.
Me.txtProduct.BackStyle = 1
'Make control background color white.
Me.txtProduct.BackColor = vbWhite
'Make font color red.
Me.txtProduct.ForeColor = vbRed
Else
'Make control background transparent.
Me.txtProduct.BackStyle = 0
'Make font color black.
Me.txtProduct.ForeColor = vbBlack
End If
End Sub

HTH,

Rob

PS. you could achieve the same result using conditional formatting.

Thank you for your help. I posted my updated code below. I added
the code to the form On Current. The Text box to update the product
code JP8 with the colors don't have the On Current available in the
Event details. I use this form to update the inventory in my
database. The Product data is stored in the Product table and then
Inventory in the Inventory table. The product is a foreign key int
he Inventory table so the product data stored in the Inventory table
is the Primary key form the Product table. Will this create problem
with using color fonts in the Inventory update form.?

The error I get is "Compile error: Method or data member not found"


Option Compare Database
Option Explicit


Private Sub Form_Current()
If Nz([Product], "") = "JP8" Then
'Make control background opaque.
[Product].BackStyle = 1
'Make control background color white.
[Product].BackColor = vbWhite
'Make font color red.
[Product].ForeColor = vbRed
Else
'Make control background transparent.
[Product].BackStyle = 0
'Make font color black.
[Product].ForeColor = vbBlack

End If

End Sub


Again,

Thank you,

Walter

If [Product] = "JP8"

will not pop an error if [Product] is Null. Using NZ() in this
context would only be necessary if you were trying to assign
[Product] to a field whose Datatype prohibits Null values.

Knowing how these kind of things tend to go, Walter, it would
probably be helpful, in conjunction with Doug's suggestion, to see
the actual code from your last attempt.

 

[Walter]

Rob, Linq, Doug, Jack, and Ronald,

Thank you for the awesome support, but I still can’t get the code to work.
I don’t get any errors with the code Rob wrote, but the form doesn’t display
the JP8 in Red. I tried conditional formatting, but that didn’t work either.
What am I doing wrong?

Walter

Hi Walter,

What you are wanting to do is change properties of a control on a form -
it's probably named Product, the same name as the field it's bound to in the
form's RecordSource. When you use code with the name in square brackets,
you are explicitly referring to the field, and the field does not have the
properties you are trying to set. Try renaming the textbox contorl
displaying the Product data to txtProduct, and change your code to:

Private Sub Form_Current()
If [Product] = "JP8" Then
'Make control background opaque.
Me.txtProduct.BackStyle = 1
'Make control background color white.
Me.txtProduct.BackColor = vbWhite
'Make font color red.
Me.txtProduct.ForeColor = vbRed
Else
'Make control background transparent.
Me.txtProduct.BackStyle = 0
'Make font color black.
Me.txtProduct.ForeColor = vbBlack
End If
End Sub

HTH,

Rob

PS. you could achieve the same result using conditional formatting.

Thank you for your help. I posted my updated code below. I added
the code to the form On Current. The Text box to update the product
code JP8 with the colors don't have the On Current available in the
Event details. I use this form to update the inventory in my
database. The Product data is stored in the Product table and then
Inventory in the Inventory table. The product is a foreign key int
he Inventory table so the product data stored in the Inventory table
is the Primary key form the Product table. Will this create problem
with using color fonts in the Inventory update form.?

The error I get is "Compile error: Method or data member not found"


Option Compare Database
Option Explicit


Private Sub Form_Current()
If Nz([Product], "") = "JP8" Then
'Make control background opaque.
[Product].BackStyle = 1
'Make control background color white.
[Product].BackColor = vbWhite
'Make font color red.
[Product].ForeColor = vbRed
Else
'Make control background transparent.
[Product].BackStyle = 0
'Make font color black.
[Product].ForeColor = vbBlack

End If

End Sub


Again,

Thank you,

Walter

If [Product] = "JP8"

will not pop an error if [Product] is Null. Using NZ() in this
context would only be necessary if you were trying to assign
[Product] to a field whose Datatype prohibits Null values.

Knowing how these kind of things tend to go, Walter, it would
probably be helpful, in conjunction with Doug's suggestion, to see
the actual code from your last attempt.

 

[Rob Parker]

Hi again Walter,

If you've renamed the textbox control to txtProduct, then the code I posted
will certainly work. The only thing(s) I can think of are that either the
form's Current event is not firing (ie. you're not moving between records;
perhaps you're expecting it to change when you enter the value "JP8") or
you're displaying a continuous form, and the current record does not contain
the value "JP8". In the second case, you perhaps are missing the vital
point that every instance of a control on a continuous form will be changed
by that code - the textbox for every Product will change if the current (ie.
selected) record contains "JP8". If you are wanting differentiation of
controls in a continuous form, you must use conditional formatting.

To apply conditional formatting to the txtProduct control, select the
control in design mode, then open the Conditional Formatting dialog from the
Format menu. Ensure that the default formatting looks how you want it to,
then in the Condition 1 section select "Field Value Is" in the first
drop-down, "equal to" in the second drop-down, and enter "JP8" (with the
quotes) in the textbox; select the foreground and background colours you
want via the icon buttons, and that should be it.

Again, HTH,

Rob

Rob, Linq, Doug, Jack, and Ronald,

Thank you for the awesome support, but I still can't get the code to
work.
I don't get any errors with the code Rob wrote, but the form doesn't
display the JP8 in Red. I tried conditional formatting, but that
didn't work either. What am I doing wrong?

Walter

Hi Walter,

What you are wanting to do is change properties of a control on a
form - it's probably named Product, the same name as the field it's
bound to in the form's RecordSource. When you use code with the
name in square brackets, you are explicitly referring to the field,
and the field does not have the properties you are trying to set.
Try renaming the textbox contorl displaying the Product data to
txtProduct, and change your code to:

Private Sub Form_Current()
If [Product] = "JP8" Then
'Make control background opaque.
Me.txtProduct.BackStyle = 1
'Make control background color white.
Me.txtProduct.BackColor = vbWhite
'Make font color red.
Me.txtProduct.ForeColor = vbRed
Else
'Make control background transparent.
Me.txtProduct.BackStyle = 0
'Make font color black.
Me.txtProduct.ForeColor = vbBlack
End If
End Sub

HTH,

Rob

PS. you could achieve the same result using conditional formatting.

Thank you for your help. I posted my updated code below. I added
the code to the form On Current. The Text box to update the product
code JP8 with the colors don't have the On Current available in the
Event details. I use this form to update the inventory in my
database. The Product data is stored in the Product table and then
Inventory in the Inventory table. The product is a foreign key int
he Inventory table so the product data stored in the Inventory table
is the Primary key form the Product table. Will this create problem
with using color fonts in the Inventory update form.?

The error I get is "Compile error: Method or data member not found"


Option Compare Database
Option Explicit


Private Sub Form_Current()
If Nz([Product], "") = "JP8" Then
'Make control background opaque.
[Product].BackStyle = 1
'Make control background color white.
[Product].BackColor = vbWhite
'Make font color red.
[Product].ForeColor = vbRed
Else
'Make control background transparent.
[Product].BackStyle = 0
'Make font color black.
[Product].ForeColor = vbBlack

End If

End Sub


Again,

Thank you,

Walter

:

If [Product] = "JP8"

will not pop an error if [Product] is Null. Using NZ() in this
context would only be necessary if you were trying to assign
[Product] to a field whose Datatype prohibits Null values.

Knowing how these kind of things tend to go, Walter, it would
probably be helpful, in conjunction with Doug's suggestion, to see
the actual code from your last attempt.

 

[AccessVandal via AccessMonster.com]

Hmmm.....Maybe Walter copied what you wrote from your previous post.

If [Product] = "JP8" Then 'should it be Me.txtProduct = "JP8"?

Me.txtProduct.BackStyle = 1 ' did Walter changed the control name?