469,924 Members | 1,365 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,924 developers. It's quick & easy.

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

32
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
Jan 15 '08 #1
17 4319
puppydogbuddy
1,923 Expert 1GB
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSelectAll_Click()
  2. On Error GoTo Error_Routine
  3.  
  4.     Dim strSQL As String
  5.  
  6.     strSQL = "Update [YourTable] SET [YourTable].[fldCkBoxFlag] = True"
  7.     strSQL = strSQL & " WHERE [YourTable].[fldCkBoxFlag] = False" & ";"
  8.  
  9.     CurrentDb.Execute strSQL, dbFailOnError
  10.     Me.YourSub2.Refresh
  11.  
  12. Exit_Continue:
  13.         Exit Sub
  14. Error_Routine:
  15.         MsgBox "Error# " & Err.Number & " " & Err.Description
  16.         Resume Exit_Continue
  17. End Sub
Jan 15 '08 #2
olivero
32
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSelectAll_Click()
  2. On Error GoTo Error_Routine
  3.  
  4.     Dim strSQL As String
  5.  
  6.     strSQL = "Update [YourTable] SET [YourTable].[fldCkBoxFlag] = True"
  7.     strSQL = strSQL & " WHERE [YourTable].[fldCkBoxFlag] = False" & ";"
  8.  
  9.     CurrentDb.Execute strSQL, dbFailOnError
  10.     Me.YourSub2.Refresh
  11.  
  12. Exit_Continue:
  13.         Exit Sub
  14. Error_Routine:
  15.         MsgBox "Error# " & Err.Number & " " & Err.Description
  16.         Resume Exit_Continue
  17. 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
Jan 15 '08 #3
puppydogbuddy
1,923 Expert 1GB
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.
Jan 15 '08 #4
olivero
32
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
Jan 16 '08 #5
olivero
32
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
Jan 18 '08 #6
olivero
32
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
Jan 20 '08 #7
puppydogbuddy
1,923 Expert 1GB
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
Jan 21 '08 #8
olivero
32
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
Jan 22 '08 #9
puppydogbuddy
1,923 Expert 1GB
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
Jan 22 '08 #10
olivero
32
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
Jan 23 '08 #11
olivero
32
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
Jan 23 '08 #12
puppydogbuddy
1,923 Expert 1GB
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]
Jan 23 '08 #13
olivero
32
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
Jan 23 '08 #14
puppydogbuddy
1,923 Expert 1GB
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.
Jan 23 '08 #15
olivero
32
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
Jan 24 '08 #16
olivero
32
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
Jan 30 '08 #17
olivero
32
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:

Expand|Select|Wrap|Line Numbers
  1.     DoCmd.SetWarnings False
  2.     DoCmd.OpenQuery ("qrySetInvoicedTrueByOrderID")
  3.     DoCmd.SetWarnings True
  4.     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
Jan 31 '08 #18

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by jdph40 | last post: by
reply views Thread by Karl Roes | last post: by
1 post views Thread by John T Ingato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.