The last checkbox will check another one

[doucet1963]

I have a main form that has a subform that contains checkboxes. I would like the checkbox "Caps_Received" on the main form be true only when the last checkbox "Received" on the subform is true.

The Subform is a datasheet type with a column of checkboxes that belong to the same fields named "Received".

At this time any boxes on the subform "received" will activate the checkbox on the main form named "Caps_Received", that code was easy but this is not what I need.

-------------------------------
Private Sub Received_AfterUpdate()
If Observation_Code > 0 And Me.Received = True Then
'Update your Date Fields
Forms("Frm_Observation_Tracking1").CAPs_Received = True
Else
'Update your Date Fields
Forms("Frm_Observation_Tracking1").CAPs_Received = False
End If

End Sub
--------------------------------

The number of checkboxes on the subform will vary depending on the number of observations.

In a nutshell, I trying to get the main checkbox to be "true" only when the last checkbox of the subform (Field named "Received") is true.

 

[MarkK]

The problem with your requirement is that the 'last checkbox' is not well defined. The order of rows in a datasheet may be modified by the user, so if the user changes the sort, then is your definition of 'last checkbox' still met? If not, you need a more rigorous definition of what value in the child table influences this property of the parent object.

hth
Mark

 

[doucet1963]

Thanks but this does not help me. The observations are entered all at once on the subform and none are added later. A filter lines them up by type, it seems complicated but in fact it is easy.

An audit may create 16 observations, once a reply is received, the checkboxes on the subform is checked, the last checkbox should activate the checkbox on the main form.

I am trying to find the code that applies this configuration to the main form checkbox once the last checkbox of the subform is true. So far no luck.

I am still looking......

 

[The_Doc_Man]

When you say "Last Checkbox" there are still two interpretations to consider.

1. Given that you have a filter doesn't mean you have a specific order of records UNLESS your filter is by types that are individually unique. If you don't have an ORDER BY clause then the sequence of appearance of your records is potentially ambiguous. Within a table, Access does not keep rows in a particular order. There IS NO GUARANTEE that the records will be in a specific order, particularly since you have filtration going on. So, were you trying to look for the last checkbox of a sequence of records, some of which might not have been checked - but the last member of the sequence WILL be?

2. "The Last Checkbox" could also be take to mean that you have now checked ALL of the possible checkboxes and the one you just checked was previously the only unchecked check box. I.e. you just checked the last box of the set.

The answer to your question depends on which of these is the case.

 

[doucet1963]

I include a copy of the database that I am working with...

The column "Received" has checkboxes that once all checked should check the one above named "CAPS_Received" At this time any checkbox will either add or remove the check in the box.

If I can get it to work that would be good if not then my co-workers will have to manually select CAPs_received, which is not a big deal, they just need training.

 

[isladogs]

Here you go...

I added a query to count the number of Received=False values for each AuditID. Call this value I

The after update event on your subform now runs an update query to set Caps_Due-True if I=0 or False if I>0

 

[MarkK]

I would do something like this...
...but note that this doesn't store the result in the parent row. I would write a query that calculates it, like the Query1 and then use that query where you need to show that status of the child rows.
hth
Mark

 

[Galaxiom]

Probably similar to what I assume Mark has done.

But rather than a query, add a textbox to the subform header with the ControlSource:
= Sum(checkboxfield)

When all the checkboxes are ticked the Sum will be -16. This value can be used to control the mainform checkbox.

Not that, either way, a change to a checkbox value is only going to be updated in the subform RecordSource after the record is saved, which usually happens after you leave the record. So don't expect the main form to respond until that happens.

 

[isladogs]

Hi Galaxiom

When all the checkboxes are ticked the Sum will be -16. This value can be used to control the mainform checkbox.

That was my first thought too ... but the OP stated the number of checkboxes can change.

So instead I counted the number of False values & if that = zero then the main form checkbox field is set to True

 

[doucet1963]

MarkK, I appreciate your reply but I need to record the result in the table because it affects other part of the database...


Ridders, I cannot get it to work, I select all the checkboxes in the subform but the checkbox above named "CAPs_Received" does not change. I might be doing something wrong. The check is removed when I unselect one of the checkboxes but it does not come back when they are all checked.

Also the code part of the code "Forms![Frm_Observation_Tracking].Requery" resets the form to the previous organization when a checkbox is selected after I select another org from the top.

I am still working at it, there must be a way.

 

[isladogs]

Hi

Strange that it worked last night but I agree its not today....

Slight modification done to fix it

The problem was that whilst a checkbox is selected, its latest state not included in the count. I've explained how I dealt with this in the code

Code is a bit less tidy but it does work now!

 

[doucet1963]

Ridders,

It works like a charm.

Thank you