Controlling tick box's accross various forms?

[Maillme]

Hi there,

I have a form, with a subform:

Main: document_log
Sub Form: sign_off

My main form has a tick box (boolean) yes/no.

My Subform has various records, and each record has a sign off tick box(boolean) yes/no.

What i would liek to happen is the following:

If i all the records within my sub form have their tick boxes as ticked (boolean = 1) then the main tick box within the main form is ticked as yes.

Also, vice versa if possible, so that if they tick the main tick box on the main form, all the records in my subform have their tick box's ticked (boolean = 1).

many thanks,
Neil

 

[TheAceMan1]

How are ya Maillme . . .

Two SQL update queries and a little logic should do. The following is an example.

In the AfterUpdate event of the document_log checkbox:

[blue]   Dim db As DAO.Database, sfrm As Form, SQL As String
   
   Set db = CurrentDb
   Set sfrm = [sign_off].Form
   
   SQL = "[purple][b][i]Your SQL to update all checkboxes on subform[/i][/b][/purple]"
   db.Execute SQL, dbFailOnError
   sfrm.Requery
   
   Set sfrm = Nothing
   Set db = Nothing[/blue]

Next, in the AfterUpdate event of the sign_off checkbox:

[blue]   Dim db As DAO.Database, SQL As String, Cri As String
   
   Set db = CurrentDb
   Cri = "YourCriteriaHere"
   
   If DCount([CheckboxName], "subFormTableName", Cri) = _
      Me.Recordset.RecordCount Then
      SQL = "YourUpdateSQLForMainForm"
      db.Execute SQL, dbFailOnError
      Me.Parent.Requery
   End If[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Maillme]

Hi Ace Man,

thanks alot fo the response - unfrotunately, i have no SQL experience? What SQL should i use?

many thanks,
Neil

 

[TheAceMan1]

Maillme . . .

I just happened to check. Apparently I received no notice of your return.

Post the [blue]RecordSource[/blue] of mainform & subform . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

C'mon Maillme! . . .

If you not sure how to get the info, just ask! We'll guide you thru it! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Maillme]

HI AceMan,

APologies for the delay- i can only access this at work (and my database is also at work).

ok, i'm not quite sure what you mean by posting the recordSource? However, i have screen captured the p[roperties of both forms:

Main Form:

http://img237.imageshack.us/img237/9961/mainformly0.jpg

SubForm:

http://img237.imageshack.us/img237/7849/subformaz0.jpg

Once again,

many thanks for your help!

Neil