Access Continous Form with check boxes

I have an Access continuous form.   In the header section I have a combo box that selects CompanyID’s  and displays all records in the continuous form with that CompanyID.  Is there a way in the detail section to have an check box, that when you check the check box on the first record, to have all the other records with that  CompanyId to show checked?
Any help would be appreciated!

Thank you.

ca1358Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ComputronCommented:
One way is to use the click event of the checkbox to set a global variable. When the next record opens, check this global varible for its value and set the checkbox value in the new record accordingly.
ComputronCommented:
Actually, I may have misunderstood since its access.

Another may be to run an update query then refresh the continuous form to show all checked.
jjafferrCommented:
Hi ca1358,

The Checkboxs have to be Bound to a field in the Table, just like all the fields.

I prefer to have an UnBound checkbox in the Header, then when you check it, all the Checkboxes of all the Records of CompanyID to be checked,
Like Computron said, you need an update query like this:
UPDATE YourTableName SET YourCheckBoxName = -1 WHERE CompanyID=Forms![YourFormName]![CompanyID];
then you will have to Refresh the Form, OR Requery.

or you can have this line with db.CurrentExecute on click of the Checkbox
UPDATE YourTableName SET YourCheckBoxName = -1 WHERE CompanyID=me.CompanyID;

Beware, this code will NOT uncheck the Checkboxes if you changed your mind.

jaffer

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mbizupCommented:
So this check box would appear with every row on your continuous form, right?
Start by binding it to a field in the underlying table, if you haven't already.  Then add code to the After Update event of your checkbox to set the checkboxes for all the records in the form:

'This will set all the check boxes the same... If you check one they will all be checked.  If you uncheck one they will all be unchecked.
Private Sub YourCheckBoxName_AfterUpdate()
   Dim rs As Recordset
   Set rs = Me.RecordsetClone
   rs.MoveFirst
   Do Until rs.EOF
        rs.Edit
        rs!YourCheckBoxFieldName = Me.YourCheckBoxName
        rs.Update
        rs.MoveNext
       
   Loop
End Sub

'** Or do you want this...
'If the check box has been "Checked", all the others will be checked.  But if a check box has been unchecked, the others remain unchanged.
Private Sub YourCheckBoxName_AfterUpdate()
   if Me.YourCheckBoxName = TRUE then
        Dim rs As Recordset
        Set rs = Me.RecordsetClone
        rs.MoveFirst
        Do Until rs.EOF
             rs.Edit
             rs!YourCheckBoxFieldName = Me.YourCheckBoxName
             rs.Update
             rs.MoveNext      
        Loop
   end if
End Sub

ca1358Author Commented:
Thank you all for the help.  Sorry, for the late response but we have gone from having a tornado last week to a blizzard this week, that shut us down.
 I have to add a criteria to this, if the companyid in the header = to the companyid bound to the table then check [Positive Mark].

Private Sub Check36_AfterUpdate()
 Dim rs As Recordset
   Set rs = Me.RecordsetClone
     
   rs.MoveFirst
   Do Until rs.EOF
        rs.Edit
        rs![Positive Mark] = Me.Check36
        rs.Update
        rs.MoveNext
       
   Loop
End Sub

All you help is greatly appreciated.  


ca1358Author Commented:
also, tried this

Private Sub Check36_Click()
Dim sql As String
 

 sql = "update [roll exception report] set checkbox36 = -1 where [companyid] = me.[companyid]"
 CurrentDb.Execute sql, dbFailOnError
 
End Sub
ca1358Author Commented:

Private Sub Check36_Click()
Dim sql As String
 

 sql = "update [roll exception report] set checkbox36 = -1 where [company number] = " & Me.[Company Number]
 CurrentDb.Execute sql

   
   Me.Requery
   

End Sub
  I tried this and this didnt work.  
ca1358Author Commented:
I found another error in Syntax but still doent work.

Private Sub Check36_Click()
Dim sql As String
 

 sql = "update [roll exception report] set check36 = -1 where [companyid] = " & Me.[CompanyID]
 CurrentDb.Execute sql, dbFailOnError
 Me.Requery
   

End Sub
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.