By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Checkbox problem in an Access 2003 form

P: 4
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.
Feb 13 '09 #1
Share this Question
Share on Google+
17 Replies


NeoPa
Expert Mod 15k+
P: 31,418
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).
Feb 13 '09 #2

P: 4
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)
Feb 13 '09 #3

topher23
Expert 100+
P: 234
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:

Expand|Select|Wrap|Line Numbers
  1. If Me.2ndCoat then
  2.        Me.PriceOfJob=Me.PriceOfJob * 0.65
  3. Else
  4.        Me.PriceOfJob=Me.PriceOfJob * (1/0.65)
  5. 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.
Feb 13 '09 #4

P: 4
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
Feb 13 '09 #5

100+
P: 675
In the long run, might be better if table had a DiscountRate field, which had a default of 1.00. Me.txtPriceOfJob would be calculated from that. Checkbox would then change Me.txtDiscountRate 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.
Feb 13 '09 #6

missinglinq
Expert 2.5K+
P: 3,532
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Ctl2ndCoat_AfterUpdate()
  2.  
  3. If Not IsNull(Me.PriceOfJob) Then
  4.  
  5.  If Me.Ctl2ndCoat Then
  6.   Me.PriceOfJob = Me.PriceOfJob * 0.65
  7.  Else
  8.   Me.PriceOfJob = Me.PriceOfJob * (1 / 0.65)
  9.  End If
  10.  
  11. Else
  12.  MsgBox "You must first enter a PriceOfJob!"
  13.  Ctl2ndCoat = 0
  14.  PriceOfJob.SetFocus
  15. End If
  16.  
  17. End Sub
Linq ;0)>
Feb 13 '09 #7

NeoPa
Expert Mod 15k+
P: 31,418
@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.
Feb 13 '09 #8

NeoPa
Expert Mod 15k+
P: 31,418
@missinglinq
8-) Didn't realise that Linq.

Lucky you came along.
Feb 13 '09 #9

100+
P: 675
... 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.
Feb 13 '09 #10

topher23
Expert 100+
P: 234
You can also wrap the control name in brackets, as in Me.[2ndCoat] - has the same effect and causes less confusion. I had intended to use brackets in the original post but just plain forgot.

I agree strongly with others' comments about using a discount field.
Feb 13 '09 #11

NeoPa
Expert Mod 15k+
P: 31,418
I didn't see how that could work Topher, but I tested to be sure.

Although the IntelliSense doesn't recognise it, [2ndCoat] certainly does work. Nice point.
Feb 13 '09 #12

100+
P: 675
Most naming/coding conventions would append a prefix to 2ndCoat, making it something like "txt2ndCoat", and making this discussion academic.
Feb 13 '09 #13

P: 4
Many thanks to all who helped, it's working.
Feb 16 '09 #14

NeoPa
Expert Mod 15k+
P: 31,418
Very true OB - hence most experienced developers don't even know about this. Nevertheless, I doubt we'll ever get to the point where all who start to develop in MS Access will have had exposure to such guidance. Until then, we just keep dropping the hints ;)
Feb 16 '09 #15

topher23
Expert 100+
P: 234
@OldBirdman
I took an advanced MS Access course last year, just to see if my self-taught skills were lacking anything important. I was sorely disappointed in that the instructor was not using any sort of naming conventions whatsoever. When I asked him if he was going to be teaching Leszynski conventions at some point, he said that you really didn't need to use conventions in Access because it doesn't require them. My jaw dropped - this guy has developed in Access since v2, written several books on Access and instructed countless students on how to build Access databases, yet teaches NO naming conventions whatsoever. Sad, very sad...
Feb 16 '09 #16

NeoPa
Expert Mod 15k+
P: 31,418
@topher23
Yeah right! 'Cause we never have to clear up issues arising simply because sensible naming conventions weren't used!

Oh well, there really is no accounting for stupidity (As opposed to simply being unaware of such issues).
Feb 16 '09 #17

missinglinq
Expert 2.5K+
P: 3,532
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe."- Albert Einstein
Feb 16 '09 #18

Post your reply

Sign in to post your reply or Sign up for a free account.