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!frmS ubInvoices2.Ref resh

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

The "CurrentDb.Exec ute 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!frmS ubInvoices2.Ref resh

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

The "CurrentDb.Exec ute 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!Your SubformControlf orSub2.Form.Ref resh

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!Your SubformControlf orSub2.Form.Ref resh

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!frmS ubInvoices2!chk Invoiced!Form.R efresh
Forms!Main!frmS ubInvoices2!chk Invoiced.Form.R efresh
Forms!Main!frmS ubInvoices2.chk Invoiced!Form.R efresh
Forms!Main!frmS ubInvoices2.chk Invoiced.Form!R efresh
Forms!Main!frmS ubInvoices2.chk Invoiced.Refres h
Forms!Main!frmS ubInvoices2.chk Invoiced!Refres h
Forms!Main!frmS ubInvoices2!chk Invoiced!Refres h
Forms!Main!frmS ubInvoices2!chk Invoiced.Refres h
Forms!Main!frmS ubInvoices2.Ref resh

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!frmS ubInvoices2!chk Invoiced!Form.R efresh
Forms!Main!frmS ubInvoices2!chk Invoiced.Form.R efresh
Forms!Main!frmS ubInvoices2.chk Invoiced!Form.R efresh
Forms!Main!frmS ubInvoices2.chk Invoiced.Form!R efresh
Forms!Main!frmS ubInvoices2.chk Invoiced.Refres h
Forms!Main!frmS ubInvoices2.chk Invoiced!Refres h
Forms!Main!frmS ubInvoices2!chk Invoiced!Refres h
Forms!Main!frmS ubInvoices2!chk Invoiced.Refres h
Forms!Main!frmS ubInvoices2.Ref resh

Any suggestions?

Oliver

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

Forms!Main!frmS ubInvoices2.For m.Refresh

[olivero]

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

Forms!Main!frmS ubInvoices2.For m.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.Execu te 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.Execu te 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]![cmbOrderIDInvoi ces])

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]![cmbOrderIDInvoi ces])

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.....u sually 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>parameter s from the command menu. copy and paste your form parameter in the dialog box as shown

[Forms]![Main]![frmSubInvoices1].[Form]![cmbOrderIDInvoi ces]

[olivero]

You will also get the parameter message when you use an object reference that Access does not recognize.....u sually 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>parameter s from the command menu. copy and paste your form parameter in the dialog box as shown

[Forms]![Main]![frmSubInvoices1].[Form]![cmbOrderIDInvoi ces]

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 qryOrderDetails ByOrderID 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 qryOrderDetails ByOrderID 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.