Checkbox Count

[Micron]

If you delete using a button, add a requery of the subform to the code.

Forms!MainFormName!SubformControlName.Form.Requery should do it;
where subformcontrolname is the name of the control that contains the subform, not the name of the subform itself.

[pbaldy]

There's the fish. I'll get out of the way.

[Micron]

No need. Sorry if you feel pushed out of the way. If so, it is I that should bow out.
I just liked your answer and thought I'd get ahead of what I figured that was going to be the inevitable question.

[hinchi1]

Hi guys
Thanks for the feedback it is very much appreciated. I will try your suggestions at work tomorrow and let you know how I get on. From what was supposed to be a simple database it has grown into a bit of a monster and I am learning more and more each day and with forums like this, the support is fantastic.

[ssanfu]

<snip> If VBNo then exit sub. If vbYes then execute the append and delete queries<snip>

Just curious.... why have append and delete queries???
So much easier to have a Boolean field, maybe "Archived", to indicate desks that have been vacated.
Queries would have additional criteria of "Archived = False" if in use or "Archived = True" if desks have been vacated

[hinchi1]

Not sure what you mean by Boolean field. Still finding my way around access so open to any new methods.

[hinchi1]

HI Micron

Not sure what you mean subformcontrolname is the name of the control that contains the subform, not the name of the subform itself. My subform listed in the Objects tale is called qryPGRDesk1FilterAll subform2, so where do i find it's control name?

[Micron]

Boolean is True / False. Can also equate to Yes/No, On/Off or 1/0. IIRC, sometimes True is -1 but it's fairly rare (no I'm not thinking of the triple state of a checkbox - at least I don't think so).
Open the main form in design view. With the property sheet visible, click once on what you think is the subform. A highlight line (orange?) will appear around the subform. Look at the property sheet and see the selection type. It should read subform/subreport and it's name should be showing in the combo. When you selected the subform control from the ribbon and added it to the form, you had to choose a source object to have that object show there when the form opens. Before you did that, what you were looking at on the form (the activated control) is the subform/subreport control. It is part of the chain of objects that you have to refer to when drilling down to anything on or about the form that is contained within it.

[hinchi1]

Thanks for that tried it but all I see is#deleted# in the controls of the record I selected. It did not refresh the subform or do I need another piece of code for that to happen?

[Micron]

That is still what you see if you click Refresh All on the Ribbon?
You used the syntax from post #16? Post what you tried.

[hinchi1]

The syntax is fine but i did not use the the "Refresh All Button" on the ribbon. I was hoping to do this in the code as i am sure the end user will not use the "Refresh All Button" when the record as been archived. Once i am happy with the database i will be splitting it ready for deployment so will that button be there at the user end?

[Micron]

I was attempting to discover what the results were from using the built in refresh feature. Maybe I'm involved in too many threads in this forum because I find answers like "the syntax is fine" and responses that lack answers to simple questions to be quite frustrating.

[hinchi1]

Ok, the syntax to requery the subform seems to work. However, to refresh the data you need to choose the same option from the combobox. I would like the the requery of the form to be automatic. I am sure I will figure it out. Thanks again

[Micron]

Look again a posts 14 and 15, I think.

[ssanfu]

So why are you using append and delete queries???