How do I check all check box controls on a Continuous Form?

[olivero]

Hi group,

How do I check all check box controls on a Continuous Form?

I have a form (Main) with two SubForms (Sub1 and Sub2). Sub1 has a button that is supposed to check all the check boxes visible in the SourceObject of Sub2. The SourceObject of Sub2 is a Continous Form based on a query.

I need to be able to click the button and have all the check boxes become checked.

Right now, I can only get teh first one to check!

I'm trying to do this as efficiently as possible, so any help would be much appreciated!

Oliver

[puppydogbuddy]

Hi group,

How do I check all check box controls on a Continuous Form?

I have a form (Main) with two SubForms (Sub1 and Sub2). Sub1 has a button that is supposed to check all the check boxes visible in the SourceObject of Sub2. The SourceObject of Sub2 is a Continous Form based on a query.

I need to be able to click the button and have all the check boxes become checked.

Right now, I can only get teh first one to check!

I'm trying to do this as efficiently as possible, so any help would be much appreciated!
Oliver

Hi Oliver,
If I recall correctly, your sub2 is a subform of sub1. Try this for your select all button. In order for you to do what you want on a continuous form, the checkbox field must be bound to a field in your table.

Private Sub btnSelectAll_Click()
On Error GoTo Error_Routine
 
    Dim strSQL As String
 
    strSQL = "Update [YourTable] SET [YourTable].[fldCkBoxFlag] = True"
    strSQL = strSQL & " WHERE [YourTable].[fldCkBoxFlag] = False" & ";"
 
    CurrentDb.Execute strSQL, dbFailOnError
    Me.YourSub2.Refresh
 
Exit_Continue:
        Exit Sub
Error_Routine:
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue
End Sub

[olivero]

Hi Oliver,
If I recall correctly, your sub2 is a subform of sub1. Try this for your select all button. In order for you to do what you want on a continuous form, the checkbox field must be bound to a field in your table.

Private Sub btnSelectAll_Click()
On Error GoTo Error_Routine
 
    Dim strSQL As String
 
    strSQL = "Update [YourTable] SET [YourTable].[fldCkBoxFlag] = True"
    strSQL = strSQL & " WHERE [YourTable].[fldCkBoxFlag] = False" & ";"
 
    CurrentDb.Execute strSQL, dbFailOnError
    Me.YourSub2.Refresh
 
Exit_Continue:
        Exit Sub
Error_Routine:
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue
End Sub

Hi Puppydogbuddy,

Actually in this case, Sub2 is at the same level as Sub1 (I'm working on a different form now). I'll give this a shot and send an update, but I won't likely get to this until tonight.

Is there a way to do this by interacting with the form, instead of the query? I like how clean your method is, but I'm just curious. If this works for me, it will be the exception in the DB so far, as all my other changes have been made by changing the values of forms and not by directly coded SQL statements.

Thanks for the help!

Oliver

[puppydogbuddy]

Hi Puppydogbuddy,

Actually in this case, Sub2 is at the same level as Sub1 (I'm working on a different form now). I'll give this a shot and send an update, but I won't likely get to this until tonight.

Is there a way to do this by interacting with the form, instead of the query? I like how clean your method is, but I'm just curious. If this works for me, it will be the exception in the DB so far, as all my other changes have been made by changing the values of forms and not by directly coded SQL statements.

Thanks for the help!

Oliver

Although I think the above approach is better and more efficient, you should be able to do it thru the form as long as the form is bound to your table and your button includes a refresh/requery command.

[olivero]

Although I think the above approach is better and more efficient, you should be able to do it thru the form as long as the form is bound to your table and your button includes a refresh/requery command.

Your first method is certainly better and more efficient!

I got back late tonight and haven't had a chance to try this out yet. I'll keep you posted.

Oliver

[olivero]

Your first method is certainly better and more efficient!

I got back late tonight and haven't had a chance to try this out yet. I'll keep you posted.

Oliver

I've been swamped this week and haven't had a chance to get back to this project! Sorry for the delay in replying to you. I hope to get back on this over the weekend.

Oliver

[olivero]

I've been swamped this week and haven't had a chance to get back to this project! Sorry for the delay in replying to you. I hope to get back on this over the weekend.

Oliver

Hey there,

Sorry for the delay getting back. I've tried the code, though I had to adapt it slightly because the button is on Sub1 of Main and the Continuous Form is on another Sub form of Main, at the same level as Sub1.

Main
-->Sub1
-->Sub2 = same level as Sub1

It seemd to work through the SQL statements, but I got errors on the next two lines.

I had to change one line to this:

Forms!Main!frmSubInvoices2.Refresh

But apparently, the Refresh property isn't available for a form?!! Is there another way to do this?

The "CurrentDb.Execute strSQL, dbFailOnError" line got this error:

"Runtime error 3061
Too few parametres, expected 1"

Any suggestions on how to work through this?

Thanks for waiting around!

Oliver

[puppydogbuddy]

Hey there,

Sorry for the delay getting back. I've tried the code, though I had to adapt it slightly because the button is on Sub1 of Main and the Continuous Form is on another Sub form of Main, at the same level as Sub1.

Main
-->Sub1
-->Sub2 = same level as Sub1

It seemd to work through the SQL statements, but I got errors on the next two lines.

I had to change one line to this:

Forms!Main!frmSubInvoices2.Refresh

But apparently, the Refresh property isn't available for a form?!! Is there another way to do this?

The "CurrentDb.Execute strSQL, dbFailOnError" line got this error:

"Runtime error 3061
Too few parametres, expected 1"

Any suggestions on how to work through this?

Thanks for waiting around!

Oliver

Oliver,
Refresh is available for a form...it is not available in your case because you have the wrong syntax.

In your case, if sub1 and sub2 are both subforms on the same level, then both are accessed through their subform control on the main form. Given that the "select all" button is on sub1 and the checkboxes are on sub2, then the button click syntax should be something like this (I am assuming that frmSubInvoices2 is the source object for Sub2, and not the subform control. However, even if it were the subform control, your syntax did not contain the reference to the Form property before the Refresh):

Forms!Main!YourSubformControlforSub2.Form.Refresh

Let me know what happened. Thanks.

PDB

[olivero]

Oliver,
Refresh is available for a form...it is not available in your case because you have the wrong syntax.

In your case, if sub1 and sub2 are both subforms on the same level, then both are accessed through their subform control on the main form. Given that the "select all" button is on sub1 and the checkboxes are on sub2, then the button click syntax should be something like this (I am assuming that frmSubInvoices2 is the source object for Sub2, and not the subform control. However, even if it were the subform control, your syntax did not contain the reference to the Form property before the Refresh):

Forms!Main!YourSubformControlforSub2.Form.Refresh

Let me know what happened. Thanks.

PDB

PDB,

There's a punctuation mark missing in your example, so I've tried these syntices (and a few other too, I think), but I'm getting errors (Object doesn't support this property or method):

Forms!Main!frmSubInvoices2!chkInvoiced!Form.Refres h
Forms!Main!frmSubInvoices2!chkInvoiced.Form.Refres h
Forms!Main!frmSubInvoices2.chkInvoiced!Form.Refres h
Forms!Main!frmSubInvoices2.chkInvoiced.Form!Refres h
Forms!Main!frmSubInvoices2.chkInvoiced.Refresh
Forms!Main!frmSubInvoices2.chkInvoiced!Refresh
Forms!Main!frmSubInvoices2!chkInvoiced!Refresh
Forms!Main!frmSubInvoices2!chkInvoiced.Refresh
Forms!Main!frmSubInvoices2.Refresh

Any suggestions?

Oliver

[puppydogbuddy]

PDB,

There's a punctuation mark missing in your example, so I've tried these syntices (and a few other too, I think), but I'm getting errors (Object doesn't support this property or method):

Forms!Main!frmSubInvoices2!chkInvoiced!Form.Refres h
Forms!Main!frmSubInvoices2!chkInvoiced.Form.Refres h
Forms!Main!frmSubInvoices2.chkInvoiced!Form.Refres h
Forms!Main!frmSubInvoices2.chkInvoiced.Form!Refres h
Forms!Main!frmSubInvoices2.chkInvoiced.Refresh
Forms!Main!frmSubInvoices2.chkInvoiced!Refresh
Forms!Main!frmSubInvoices2!chkInvoiced!Refresh
Forms!Main!frmSubInvoices2!chkInvoiced.Refresh
Forms!Main!frmSubInvoices2.Refresh

Any suggestions?

Oliver

If frmSubInvoices2 is the subform control on the main form, then try this exactly as shown:

Forms!Main!frmSubInvoices2.Form.Refresh

[olivero]

If frmSubInvoices2 is the subform control on the main form, then try this exactly as shown:

Forms!Main!frmSubInvoices2.Form.Refresh

ok, that line worked. It seems so simple when you do it...!

I still don't fully understand the purpose of each punctuation mark. Although I get it right about 80% of the time, it's more from guessing...

Now, I'm still getting the other error I had mentioned earlier with the following line:

CurrentDb.Execute strSQL, dbFailOnError

Error = Too few parametres. Expected 1

I'll review my code for errors and report back.

Thanks for your help.

Oliver

[olivero]

ok, that line worked. It seems so simple when you do it...!

I still don't fully understand the purpose of each punctuation mark. Although I get it right about 80% of the time, it's more from guessing...

Now, I'm still getting the other error I had mentioned earlier with the following line:

CurrentDb.Execute strSQL, dbFailOnError

Error = Too few parametres. Expected 1

I'll review my code for errors and report back.

Thanks for your help.

Oliver

This mght help:

The error apparently happens if you WHERE points to a control on a form. This isn't what we are doing (though being able to do it would solve the issue).

Apparently, this error can also happen if the query requires a parameter. It doesn, because I'm using this as he criteria on the OrderID field in the query:

DLookUp("[OrderID]","[Order Details]","[OrderID] = " & [Forms]![Main]![frmSubInvoices1].[Form]![cmbOrderIDInvoices])

Though it might be best to keep our code strictly referencing the query and not the form, the form is already showing what I want to see because of this Criteria in the query.

If we can turn the above DLookUp into SQL, we can use PARAMETERS in strSQL. Are you able to convert this for me?

Thanks,
Oliver

[puppydogbuddy]

This mght help:

The error apparently happens if you WHERE points to a control on a form. This isn't what we are doing (though being able to do it would solve the issue).

Apparently, this error can also happen if the query requires a parameter. It doesn, because I'm using this as he criteria on the OrderID field in the query:

DLookUp("[OrderID]","[Order Details]","[OrderID] = " & [Forms]![Main]![frmSubInvoices1].[Form]![cmbOrderIDInvoices])

Though it might be best to keep our code strictly referencing the query and not the form, the form is already showing what I want to see because of this Criteria in the query.

If we can turn the above DLookUp into SQL, we can use PARAMETERS in strSQL. Are you able to convert this for me?

Thanks,
Oliver

You will also get the parameter message when you use an object reference that Access does not recognize.....usually the result of a mispelled or incorrect reference. In the DLookup, you are supplying the parameter via the form. I have a strong feeling that your reference to frmSubInvoices1 is incorrect....it should be frmSubInvoices2...am I correct?

If that is not it.... is "[Order Details]" a query?....if it is a query, Access generally requires parameters used to be declared. you can do the declaration by placing the query in design view and selecting query>parameters from the command menu. copy and paste your form parameter in the dialog box as shown

[Forms]![Main]![frmSubInvoices1].[Form]![cmbOrderIDInvoices]

[olivero]

You will also get the parameter message when you use an object reference that Access does not recognize.....usually the result of a mispelled or incorrect reference. In the DLookup, you are supplying the parameter via the form. I have a strong feeling that your reference to frmSubInvoices1 is incorrect....it should be frmSubInvoices2...am I correct?

If that is not it.... is "[Order Details]" a query?....if it is a query, Access generally requires parameters used to be declared. you can do the declaration by placing the query in design view and selecting query>parameters from the command menu. copy and paste your form parameter in the dialog box as shown

[Forms]![Main]![frmSubInvoices1].[Form]![cmbOrderIDInvoices]

the reference is correct. [Order Details] is a table. The control on frmSubInvoices1 is a combo box that uses a query to present the user with a list of orders that have not been invoiced yet - this query include the Order ID. The DLookUp grabs the OrderID from the record that the user chose and uses it as the criteria for the qryOrderDetailsByOrderID used in strSQL. It then pulls up all OrderDetails that are associated with the Order ID and presents them as a Continous Form in Sub2.

The queries work perfectly. I'm just having a hard time checking all the boxes for the Order Details that show up in the continuous form.

Oliver

[puppydogbuddy]

the reference is correct. [Order Details] is a table. The control on frmSubInvoices1 is a combo box that uses a query to present the user with a list of orders that have not been invoiced yet - this query include the Order ID. The DLookUp grabs the OrderID from the record that the user chose and uses it as the criteria for the qryOrderDetailsByOrderID used in strSQL. It then pulls up all OrderDetails that are associated with the Order ID and presents them as a Continous Form in Sub2.

The queries work perfectly. I'm just having a hard time checking all the boxes for the Order Details that show up in the continuous form.

Oliver

I have a similar application with checkboxes on a continuoua subform, and it works fine for me. One thing I do differently then you is that I have my buttons to select/deselect my checkboxes in the footer/header of my continuous form, For my Select All button, I just execute the sql and then do a Me.Refresh. It works much better this way because the buttons for the checkbox are in the same proximate subform as the checkboxes themselves.

[olivero]

I have a similar application with checkboxes on a continuoua subform, and it works fine for me. One thing I do differently then you is that I have my buttons to select/deselect my checkboxes in the footer/header of my continuous form, For my Select All button, I just execute the sql and then do a Me.Refresh. It works much better this way because the buttons for the checkbox are in the same proximate subform as the checkboxes themselves.

Another busy day... i haven't had a chance to work on this today, and I won't tomorrow. I'll need to follow up with you on the weekend again.

Thanks for the help so far, I appreciate it!

Oliver

[olivero]

Another busy day... i haven't had a chance to work on this today, and I won't tomorrow. I'll need to follow up with you on the weekend again.

Thanks for the help so far, I appreciate it!

Oliver

PDB,

Sorry for the delay. again. I put in some self study time and during this (while reading up on a completely different topic), I got the bright idea of just creating an Update Query object and calling that from the button on Sub1... In it, I could use the same DLookup for the criteria, and because the form is open, this should work.

I created the Parameter Update Query and tested it manually, and it works. I just need to find some time to update the VBA code so the button will call it.

I'll let you know how this goes.

Oliver

[olivero]

PDB,

Sorry for the delay. again. I put in some self study time and during this (while reading up on a completely different topic), I got the bright idea of just creating an Update Query object and calling that from the button on Sub1... In it, I could use the same DLookup for the criteria, and because the form is open, this should work.

I created the Parameter Update Query and tested it manually, and it works. I just need to find some time to update the VBA code so the button will call it.

I'll let you know how this goes.

Oliver

So that worked!

This is what I used on the event of the button:

    DoCmd.SetWarnings False
    DoCmd.OpenQuery ("qrySetInvoicedTrueByOrderID")
    DoCmd.SetWarnings True
    Forms!Main!frmSubInvoices2.Form.Refresh

qrySetInvoicedTrueByOrderID is a simple Update query with the Order ID field and the Invoiced field. The Order ID field uses the DLookup aggregate domain function I pasted above somewhere as the criteria.

I think it's funny that I didn't think of doing it this way before, but...

I still like your original suggestion though. As a phase 2, I plan to rebuild this DB using only strict SQL and VBA. I'll likely use it then.

Thanks again for all the help. I do apprecaite it.

Oliver