check all check box based on 0ne master

[dixxy12]

Hi,

I would like to know if there is a way to check a checkbox in every record base on 1 check box outside that form?

The main form has the checkbox 'chkReceivedAll' and the subform 'frmReceivedSud' has a checkbox on every record call 'chkReceived'.

So when the user check the 'main' checkbox i would like that all checkboxes in the subform to be'check'.

I'm playing with this code with not much luck:

Dim ctr As Control
Dim FirstNull As CheckBox
For Each ctr In Me.Controls
If ctr.ControlType = acCheckBox Then
  If IsNull(ctr) Then
    If FirstNull = "" Then
    ctr.SetFocus
    ctr.Checked = True
    End If
  FirstNull.Value = True
  End If
End If
Next

Can this be done?

Thanks

 

[Remou]

I would say the esiest thing would be an update query.

Here is a very rough example with imaginary names.
[tt]strSQL="UPDATE tblTable SET ChkField=True WHERE LinkField=" & Me.LinkField
CurrentDB.Execute strSQL, dbFailOnError[/tt]

 

[dixxy12]

yeah that could work...could you just elaborate a little more....in terms of what i have to dim....and the other details of the procedure...
I know i need:

dim str as string

replace chkFeild, by the field name?

what else?

 

[Remou]

strSQL is indeed a string and ChkField is the name of the subform field that you need to update. The important bit is the link field, that is, the field that identifies the subset of records in the subform. This will generally be the field that you used as the link master and link child field.

The alternative to the above is to use the recordsetclone of the subform, but this can be a little tedious.

 

[dixxy12]

ok...starting to get this....

this is what i have

strSQL = "UPDATE tblPODoorHistory SET forms!frmReceiced!frmRecivedDoorSub.form!chkAllRec=True WHERE chkReceived=" & Me.chkAllRec
CurrentDb.Execute strSQL, dbFailOnError

main form name : frmReceived Data : Unbound Feild Name : chkReceivedAll(Data unbound)
SubFormName: frmRecivedDoorSub Feild Name : chkReceived Data: SQL recordSource

 

[TheAceMan1]

How are ya dixxy12 . . .

Using an [blue]Update SQL[/blue] I get:

[blue]   Dim db As DAO.Database, frm As Form, SQL As String, flg As Boolean
   
   Set db = CurrentDb
   Set frm = [frmReceivedSub].Form
   If Me!chkReceivedAll Then flg = True
   
   SQL = "UPDATE tblPODoorHistory " & _
         "SET [chkReceived] = " & flg & _
         "WHERE [[purple][b][i]PrimarykeyName[/i][/b][/purple]] = " & Me![purple][b][i]PrimarykeyName[/i][/b][/purple] & ";"
   db.Execute SQL, dbFailOnError
   
   frm.Requery
   
   Set frm = Nothing
   Set db = notning[/blue]

Using [blue]Recordset[/blue]:

[blue]   Dim frm As Form, rst As DAO.Recordset, ctl As Control, flg As Boolean
   
   If Me!chkReceivedAll Then flg = True
   Set frm = [frmReceivedSub].Form
   Set rst = frm.RecordsetClone
   
   If Not rst.BOF Then
      Do
         rst.Edit
         rst!chkReceived = flg
         rst.Update
         rst.MoveNext
      Loop Until rst.EOF
   End If
   
   frm.Requery
   
   Set rst = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[dixxy12]

Thanks AceMan,

The main form is Unbound Data....does this matter in either case

I see that your first statment seems to be looking for the link between the forms....what about the second one will it matter?

 

[TheAceMan1]

[blue]I see that your first statment seems to be looking for the link between the forms....what about the second one will it matter?[/blue]

Since [blue]chkReceivedAll[/blue] is unbound and only has operational control then remove the where clause:

[blue]   Dim db As DAO.Database, frm As Form, SQL As String, flg As Boolean
   
   Set db = CurrentDb
   Set frm = [frmReceivedSub].Form
   If Me!chkReceivedAll Then flg = True
   
   SQL = "UPDATE tblPODoorHistory " & _
         "SET [chkReceived] = " & flg & ";"
   db.Execute SQL, dbFailOnError
   
   frm.Requery
   
   Set frm = Nothing
   Set db = notning[/blue]

However, the code will turn all [blue]chkReceived[/blue] checkboxes on or off. I'm just not sure if you're looking to change a subset, instead of all records in the table.
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[dixxy12]

I realy appriciate your help but something is not working out...

Dim db As DAO.Database, frm As Form, SQL As String, flg As Boolean
   
   Set db = CurrentDb
   Set frm = Forms![frmReceived]![frmRecivedDoorSub].Form
   If Me!chkAllRec Then flg = True
   
   SQL = "UPDATE tblPODoorHistory " & _
         "SET [chkReceived] = " & flg & ";"
   db.Execute SQL, dbFailOnError
   
   frm.Requery
   
   Set frm = Nothing
   Set db = notning

i have even changed the RecordSource of the subform because before it was a SQL statment, and still nothing happens. -

what i'm i not doing irhgt here?

 

[dixxy12]

got it...the field name was wrong.....

 

[dixxy12]

ok...got this working would like to add one small thing...

right now it will update every checkbox in the table....so i would like to filter it.

this is working:

Dim db As DAO.Database, frm As Form, SQL As String, flg As Boolean

   Set db = CurrentDb
   Set frm = Forms![frmReceived]![frmRecivedDoorSub].Form
   If Me.chkAllRec Then flg = True

   SQL = "UPDATE tblPODoorHistory " & _
         "SET [Received] = " & flg & ";"

   db.Execute SQL, dbFailOnError

   Forms!frmReceived!frmRecivedDoorSub.Form!chkReceived.Requery

'   frm.Requery

   Set frm = Nothing
   Set db = notning

but it check every box in the table

trying to filter it with this:

   Dim db As DAO.Database, frm As Form, SQL As String, flg As Boolean

   Set db = CurrentDb
   Set frm = Forms![frmReceived]![frmRecivedDoorSub].Form
   If Me.chkAllRec Then flg = True

   SQL = "UPDATE tblPODoorHistory " & _
         "SET [Received] = " & flg & _
         "WHERE [tblPODoorHistory]![PONumber] = " & Me.cboDoorPO & ";"

   db.Execute SQL, dbFailOnError

   Forms!frmReceived!frmRecivedDoorSub.Form!chkReceived.Requery

'   frm.Requery

   Set frm = Nothing
   Set db = notning

but this those not want to work, it filters the recorset perfectly but does not check any boxes

what i'm i doing wrong?

 

[Remou]

You seem to be missing a space.

SQL = "UPDATE tblPODoorHistory " & _
         "SET [Received] = " & flg & _
         "[COLOR=red yellow] [/color]WHERE [tblPODoorHistory]![PONumber] = " & Me.cboDoorPO

& ";" - This is completely unnecessary in Jet SQL.

 

[dixxy12]

Remou,

smae thing...does not check the boxes at all

 

[Remou]

In that case, you do not have a match.

Try:

strSQL = "UPDATE tblPODoorHistory " & _
         "SET [Received] = " & flg & _
         " WHERE [tblPODoorHistory]![PONumber] = " & Me.cboDoorPO
[b]Debug.print strSQL[/b]

This will print the SQL to the immediate window, so you can see exactly what is happening.

By the way, SQL is a reserved word (

http://support.microsoft.com/kb/321266)

and therefore should be avoided - I generally use strSQL.

 

[dixxy12]

i must be doing something wrong, cause it does not show anything in the immidiate window..

 

[Remou]

Please post your code.

 

[dixxy12]

Private Sub chkAllRec_AfterUpdate()
   Dim db As DAO.Database, frm As Form, SQL As String, flg As Boolean

   Set db = CurrentDb
   Set frm = Forms![frmReceived]![frmRecivedDoorSub].Form
   If Me.chkAllRec Then flg = True

   SQL = "UPDATE tblPODoorHistory " & _
         "SET [Received] = " & flg & _
         " WHERE [tblPODoorHistory]![PONumber] = " & Me!cboDoorPO & ";"
         Debug.Print strSQL

   db.Execute SQL, dbFailOnError

   Forms!frmReceived!frmRecivedDoorSub.Form!chkReceived.Requery

'   frm.Requery

   Set frm = Nothing
   Set db = notning

End Sub

nothing shows up in the immidiate window....i'm i suppose to do somthing special to see that?

sorry i am not familiar at all with the immidiate window aspect of VBA...

 

[Remou]

Try:

Private Sub chkAllRec_AfterUpdate()
   Dim db As DAO.Database, frm As Form, strSQL As String, flg As Boolean

   Set db = CurrentDb
   Set frm = Forms![frmReceived]![frmRecivedDoorSub].Form

   'What is flg to equal when Me.ChkAllRec is False?
   'Would it not be easier to simply refer to the control
   'in the SQL, especially when you refer to the combo?
   If Me.chkAllRec Then flg = True

   strSQL = "UPDATE tblPODoorHistory " & _
         "SET [Received] = " & Me.chkAllRec & _
         " WHERE [tblPODoorHistory]![PONumber] = " & Me!cboDoorPO & ";"
         Debug.Print strSQL

   db.Execute strSQL, dbFailOnError

   'You can use records affected here:
   msgbox db.RecordsAffected & " records were updated."
   
   'This should read something like:
   frm.[name of subformcontrol].Form.Requery

'   frm.Requery

   Set frm = Nothing
   Set db = notning

End Sub

 

[dixxy12]

the msgbox says '0 records updated'

 

[Remou]

What does the immediate window say?