VBA to change a field based on a if then statement

[HBCPH]

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?

Private Sub Form_Open(Cancel As Integer)
    If (Forms!frmRetailProductionLogOpenSplit!RegBDays > 25 And Forms!frmRetailProductionLogOpenSplit!RegBComplianceNotified = 0) Then
        DoCmd.SendObject acReport, "rptRetailProductionLogRegB", "PDFFormat(*.pdf)", "john.doe@bank.com", "", "", "Reg B", """Check the Reg B status on the attached Report"".", False, ""
Me!RegBComplianceNotified = 1
    End If

End Sub

[Stewart Ross]

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

Me!RegBComplianceNotified = True

or, equivalently,

Me!RegBComplianceNotified = -1

-Stewart

[HBCPH]

VBA to change a field based on a if then statement

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.

[NeoPa]

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.

[HBCPH]

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.

[Mihail]

Place line 4 before line 3

[Killer42]

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.

[Mihail]

Yes. This was the idea. Is this working ?

[slenish]

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.

[HBCPH]

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.