Form Reference in VBA?

[excellenthelp]

Accessors,



Hello- I am attempting to have Access 'uncheck' a checkbox before the database closes. I am using this code in a close event of a form.

Private Sub Form_Close()    frmAutoAllOut!chkLogOutAllUsers = False
End Sub

However, the error keeps saying "Compile error: Variable not defined". How do I define this kind of variable?

Thanks!

[pbaldy]

This should help:

http://access.mvps.org/access/forms/frm0031.htm

[excellenthelp]

I am still struggling- I have attempted altering the line to those suggested on the list pbaldy gave. Hmm...

[pbaldy]

Uh, like which specifically? You can use the "Me" reference if the check box is on the same form, otherwise "Forms!..."

[excellenthelp]

I have tried to put in:

Forms!frmAutoAllOut!chkLogOutAllUsers = False
Forms!frmAutoAllOut.chkLogOutAllUsers = False
Forms!frmAutoAllOut!chkLogOutAllUsers.Enabled = False

All to no luck!!!

[excellenthelp]

The line is referring to a separate form with the checkbox on it. Maybe it should refer to the field of the checkbox in the table instead...? I am not sure how to write the referring line for a checkbox field in a table...

[pbaldy]

I would expect this to work:

Forms!frmAutoAllOut.chkLogOutAllUsers = False

Do you get an error or what?

[excellenthelp]

I got an error on each of the lines I have attempted above, including yours.

I even double-checked the spelling!

Odd!

[excellenthelp]

do I refer to the name of the command or the control source of the command in the form?

[amrut]

The line is referring to a separate form with the checkbox on it.

Is this form open ? I am asking this because the syntax given by Paul should work.

[pbaldy]

What error do you get (the description)? You'd refer to the checkbox, as you appear to be doing. As amrut noted, the form does have to be open.

[excellenthelp]

It now works when the referred form is open. -___- Is there anyway I can refer it to the form while it is closed? Or would referring to a table the referred form's checkbox is referring to be a better method?

[pbaldy]

No, you can't refer to a form if it's closed. You could use a recordset or update query to update the table directly. Presumably either would a criteria to update the appropriate record(s).

[excellenthelp]

pbaldy,

Thanks! I figured a way to do this- it sure does cost a minor fraction of the machine's speed but at least it gets the job done. I made a form that will stay permanently hidden and the code is written below to its close event:

Option Compare DatabaseOption Explicit


Private Sub Form_Close()
    DoCmd.OpenForm "frmAutoAllOut"
    Forms!frmAutoAllOut.chkLogOutAllUsers = False
    DoCmd.Close acForm, "frmAutoAllOut"
End Sub

Marking this as SOLVED and pbaldy, I am giving you a rep point. =D

[pbaldy]

Happy to help! As you said, not as efficient but certainly will work.