Checkbox problem in an Access 2003 form

[TheSealer]

I am new to this, I'm assuming the answer is simple for the experienced. I have a checkbox on a form to indicate that a job is a '2ndCoat'. When the user checks the box, I want another field( the 'PriceofJob' field ) to be reduced by 65%. Any help greatly appreciated.

[NeoPa]

This can be done, but to help it would be easier if we had more info as to which controls are on your form (Names particularly).

[TheSealer]

I hope this is what you are asking for...The form has the following names/conntrols.
DateOfCall
CallTakenBy
LastName
Address
City
State
Zip
HomePhone
WorkPhone
CellPhone
Fax
EstimatorName
PriceOfJob
2ndCoat (Checkbox)

[topher23]

I don't know how to paste code in yet, so I'm just gonna plain-text this.

Put this code into the AfterUpdate event of your 2ndCoat checkbox:

If Me.2ndCoat then
       Me.PriceOfJob=Me.PriceOfJob * 0.65
Else
       Me.PriceOfJob=Me.PriceOfJob * (1/0.65)
End If

This code allows for the possibility that someone will click 2ndCoat and then realize that they've messed up and click it again to change it back. The code doesn't do anything else that I might recommend, like ensuring that PriceofJob isn't null before trying to perform an operation on it or rounding PriceofJob after the operation.

[TheSealer]

Thanks - did as sussgested, but the first line of the code (If Me.2ndCoat then)
turns green or red (I'm colorblind) & when I try to run it, I get a the error message: Complie error: Syntax error

[OldBirdman]

In the long run, might be better if table had a DiscountRate field, which had a default of 1.00. Me.txtPriceOfJo b would be calculated from that. Checkbox would then change Me.txtDiscountR ate to .65 or if uncheck, then to 1.00.
This would allow for future expansion with other rates, or special promotions. Also, the PriceOfJob would remain, and any discount applied to that.

[missinglinq]

The error is caused because Access VBA doesn't like control names that begin with a digit. Because of this, it adds Ctl to the beginning, so the line

If Me.2ndCoat then

should read

If Me.Ctl2ndCoat then

This should do what you need:

Private Sub Ctl2ndCoat_AfterUpdate()
 
If Not IsNull(Me.PriceOfJob) Then
 
 If Me.Ctl2ndCoat Then
  Me.PriceOfJob = Me.PriceOfJob * 0.65
 Else
  Me.PriceOfJob = Me.PriceOfJob * (1 / 0.65)
 End If
 
Else
 MsgBox "You must first enter a PriceOfJob!"
 Ctl2ndCoat = 0
 PriceOfJob.SetFocus
End If
 
End Sub

Linq ;0)>

[NeoPa]

@TheSealer
I can't see why it might. Are you sure the code is EXACTLY as shown?

PS. I would follow the route indicated by OB. Maintain the original value, and have a separate field that holds a multiplier.

[NeoPa]

@missinglinq
8-) Didn't realise that Linq.

Lucky you came along.

[OldBirdman]

... turns green or red (I'm colorblind) & when I try to run it,

I would suggest that you change the color scheme for VBA to work better for you. If you are only red/green colorblind, there are enough other colors you could use. If needed, combinations with background color might help.
On the Tools menu of the Visual Basic editor, click Tools->Options... and then "Editor Format" tab.