Checkbox Count

[hinchi1]

I have been on here before and the help was fantastic.

Here is my problem, i have a database which as several forms on it, one of which is a form with a subform. The subform, onLoad is populated with the full recordset of a table which keeps track of which desks are researchers occupy. On the main part of the form is a combobox which is populated with text as a value list. When the user selects say "This Week" from the combobox the subform is filtered with all the desks which should be vacated this week. There are other options like "This Month, Next Month" and so on. The subform is filtered with a relevant query which in turn has a date criteria attached to it. This part works fine and i can also send bulk emails to the recipients shown in the filtered subform to tell them there "vacate desk" date is due. The part I am struggling with is this, on the data shown in the subform there is a checkbox(bound to the underlying table) which when i select the checkbox(chkDeskVacated) of any of the records in the filtered subform allows me to archive the persons which have vacated their desk. The append and delete query work fine but it needs refining. What i would like to do when i click the archive button is the following:

1. A message box displays, saying i have selected "X" records for archiving. Do you wish to proceed to archive? If VBNo then exit sub. If vbYes then execute the append and delete queries



I am able to select the checkboxes in the subform, but i am struggling to code on how to count the number of checkboxes that are "True" and pass this to the message box indicating to the user how many records they have selected to archive. If I can get this part right, then all I need to do is to archive the records i have selected in the filtered subform. Then I just need to refresh the form to show the updated records.This again is a part of the code I am unsure of. I have tried the requery
method but it does not work. Any help would be greatfully appreciated.

[pbaldy]

How about a DCount() with a criteria of "FieldName = True"?

[hinchi1]

Thanks for your quick reply, however I am not really sure what you mean to be honest. Could you give me a bit of example code at all?. I think one of the other problems I am having is referencing the checkbox control on the subform as the command button is on the main form.

[pbaldy]

Since it's bound, you can test the underlying table/query, not the subform.

DCount("*", "QueryName", "FieldName = True")

replace the query and field names with your actual names.

[hinchi1]

Thanks again for this. I will try it later but the checkboxes in the underlying query will be false so not sure if this will work the way I was hoping. Could there be a way to refresh the query if I select a checbox in the subform to give the result I am after? Basically when i select one or more checkboxes to = true . I press the command button, the query is refreshed then the message box tells me how many records I have selected. Clutching at straws here to be honest.

[pbaldy]

When you check a box on the form, it updates the underlying recordset when you leave a record. If the button is on the main form, the query should reflect any changes you made. Can you attach the db here?

[hinchi1]

Well do shortly, just doing some food. Thanks again.

[pbaldy]

Food?? What's cooking, I'm hungry!

[hinchi1]

Me to, been a long day

[hinchi1]

HI pbaldy

Thanks for your help and i think i have solved the issue. I was referencing the control in the code and not the field name in the query. I will double check at work tomorrow and let you know for sure.

[hinchi1]

HI pbaldy

i have checked the code and it works a treat, thanks again. Just one more thing, is there a way to refresh the filtered list without having to select the option from the combobox?

[Micron]

Are you sure it's working? Did you try saying "No" to the saving and seeing what happens? It was pointed out that as soon as you leave the subform after checking the last box, if that form is bound, it automatically updates so the prompt is pointless. Maybe it's not bound and I missed that?

[hinchi1]

If I elect to archive a record #delete# displays in the selected record fields on the subform. It's not until I select the same filter from the comboboxes then the selected record has been removed from the subform. I just wondered if there was a way to automate refreshing the subform without using the combobox. I hope this makes sense.

[pbaldy]

is there a way to refresh the filtered list without having to select the option from the combobox?

I'm not clear on what you mean. You can requery the list from any event.

[pbaldy]

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