Checkbox in Form header that remembers its state

[mcomp72]

In my database, I have a continuous form that lists all records from a table called Members. I added an unbound checkbox to the form header to filter the results in the form, and it is working properly. However, I would like the status of the checkbox to be remembered the next time the database is opened. In order to do that, my understanding is, I need to make it a bound checkbox.

I have another table called Options, which is where I will store certain things about the database. This table will only ever have 1 record. I added a field to this table (called Roster_ShowOnlyActiveMembers) to record the result of the checkbox. The field is of type Yes/No. I can't figure out how to properly bind the checkbox to the field. I have tried to do it in the Control Source of the checkbox like this:

But when I click the checkbox now, the computer beeps at me, and the message in the bottom left of Access says:
Control can't be edited; it's bound to the expression '[Options]![Roster_ShowOnlyActiveMembers]'

I'm not sure what I've done wrong. Can anyone tell me what I need to do in order to get it to work?

 

[theDBguy]

Calculated controls are read only, so you get a beep when you try to click it. Instead, you could try using the form's Open event to look up the settings in the Options table and assign that value to the checkbox.

 

[mcomp72]

Thanks! That did the trick!

 

[theDBguy]

Thanks! That did the trick!

Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

I would like the status of the checkbox to be remembered the next time the database is opened. In order to do that, my understanding is, I need to make it a bound checkbox.

Just to clarify. Bound for a form means that the form has a RecordSource which would be a table name querydef name or an SQL string. Bound for a control means that the control has a ControlSource that references a column of the Form's RecordSource.

 

[MajP]

I would think you want to be able to select and save the choice. It would require code to change the selection if you are looking it up and assigning. To do it without code you canmake a small subform in the header or footer bound to the options table and showing just the checkbox. Then the main form is bound to the roster.

 

[mcomp72]

I would think you want to be able to select and save the choice. It would require code to change the selection if you are looking it up and assigning. To do it without code you canmake a small subform in the header or footer bound to the options table and showing just the checkbox. Then the main form is bound to the roster.

Yes, I wrote some VBA code to read the value of the field in the Options table when the form is opened and then apply the result to the checkbox. Then if the checkbox is clicked, it will write to that field in the Options button. It took a little bit of experimenting but I finally got it to work.