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

VBA to change a field based on a if then statement

P: 5
I have a form that should a count of days exceed 25 a email should be sent out and then a box checked to acknowledge that it was sent out. I can get the first part but checking the box is causing me problems. Can anyone help?
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.     If (Forms!frmRetailProductionLogOpenSplit!RegBDays > 25 And Forms!frmRetailProductionLogOpenSplit!RegBComplianceNotified = 0) Then
  3.         DoCmd.SendObject acReport, "rptRetailProductionLogRegB", "PDFFormat(*.pdf)", "john.doe@bank.com", "", "", "Reg B", """Check the Reg B status on the attached Report"".", False, ""
  4. Me!RegBComplianceNotified = 1
  5.     End If
  6.  
  7. End Sub
Dec 28 '11 #1

✓ answered by Stewart Ross

Assuming that control RegBComplianceNotified is a checkbox, you need to set it to the boolean value True (which is represented as -1, not 1), as follows:

Expand|Select|Wrap|Line Numbers
  1. Me!RegBComplianceNotified = True
or, equivalently,

Expand|Select|Wrap|Line Numbers
  1. Me!RegBComplianceNotified = -1
-Stewart

Share this Question
Share on Google+
9 Replies


Expert Mod 2.5K+
P: 2,545
Assuming that control RegBComplianceNotified is a checkbox, you need to set it to the boolean value True (which is represented as -1, not 1), as follows:

Expand|Select|Wrap|Line Numbers
  1. Me!RegBComplianceNotified = True
or, equivalently,

Expand|Select|Wrap|Line Numbers
  1. Me!RegBComplianceNotified = -1
-Stewart
Dec 28 '11 #2

P: 5
I tried True and "Yes" and now have tried the -1 but I get the message "You can't assign a value to this object". If I run VBA to check the box by itself it works fine but not in combination.
Dec 28 '11 #3

NeoPa
Expert Mod 15k+
P: 31,709
HBCPH:
"You can't assign a value to this object".
Therein lies your problem then. What is it about the design of your form that precludes this control from accepting a value? We cannot tell you something that you haven't given us any clue to first.
Dec 28 '11 #4

P: 5
I wish I knew the answer to that, as I mentioned if I run VBA to only check the box it works fine but if I try to add it to the VBA that sends the email it doesn't work. Separately they both will run. But I need them both to run if they meet the criteria.
Dec 28 '11 #5

100+
P: 759
Place line 4 before line 3
Dec 29 '11 #6

Expert 5K+
P: 8,434
I've run into this error numerous times (though it was a long time ago, not working much with Access these days). As far as I can recall, I was basically unable to touch controls from my code unless they had the focus. So I had to set focus to the control, then set the value.

Might be worth a try, at least.

P.S. After reading "Place line 4 before line 3" (I had this page open for a few hours, so I didn't see that before posting my reply) I'm thinking it might just be that the right form has to have focus, not specifically the control.
Dec 29 '11 #7

100+
P: 759
Yes. This was the idea. Is this working ?
Dec 29 '11 #8

100+
P: 283
You could do a work around. Create an unbound text box hidden on your form. Then when the check box is clicked set the text box value to 1 or True or what ever you want. Then have your code check the hidden text box for the value instead of the check box itself for the value and see if that works. After the email is sent then clear the text box of the value.
Dec 29 '11 #9

P: 5
I finally got it to run, it seems to have something to do with which event triggered the VBA. I moved it to "On Lod" and it ran fine. Thank You to everyone for trying to help.
Dec 29 '11 #10

Post your reply

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