473,324 Members | 2,239 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Select all check boxes in subform

I have a form that lists a single work request, with an attached
continuous subform that lists specific items that are part of the
request. Each item in the subform has a check box to indicate when the
work is completed, along with a date text box to indicate when the
work was completed. There is also a check box and corresponding date
field in the main form to indicate when the entire request is
completed.

I'm trying to create an event to attach to the check box on the main
form that would check or uncheck all of the items on the subform. The
event that I have so far, however, only updates the first row in the
subform:

Private Sub Completed__Click()

If [Forms]![Main Form]![Completed?] = True Then
[Forms]![Main Form]![Completion Date] = Date
[Forms]![Main Form]![Sub Form].[Form]![Completed?] = True
[Forms]![Main Form]![Sub Form].[Form]![Completion Date] =
Date
Else
[Forms]![Main Form]![Completion Date] = Null
[Forms]![Main Form]![Sub Form].[Form]![Completed?] = False
[Forms]![Main Form]![Sub Form].[Form]![Completion Date] =
Null
End If

End Sub

Can anyone see where I'm going wrong here?

Thanks!

May 15 '07 #1
3 8731
The way you do this is:

Private Sub Completed__Click()

dim strSql as string

If me![Completed?] = True Then
strSql = "update tblSubForm " & _
"set [Completation date] = #" format(date,"mm/dd/yyyy") & "#" & _
", [complaeted?] = True where main_id = " & me!id
Else
strSql = "update tblSubForm " & _
"set [Completation date] = null" & _
", [complaeted?] = False where main_id = " & me!id
End If
currentdb.Execute strSql

me.[child form Name].form.Requery
End Sub

Obviously setting the sub-form is just like any regular form..you set values
to ONE RECORD, and that not what you need/want...

The above assumes the button is on the main form part....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

May 15 '07 #2
On May 15, 1:57 pm, jsur...@gmail.com wrote:
I have a form that lists a single work request, with an attached
continuous subform that lists specific items that are part of the
request. Each item in the subform has a check box to indicate when the
work is completed, along with a date text box to indicate when the
work was completed. There is also a check box and corresponding date
field in the main form to indicate when the entire request is
completed.

I'm trying to create an event to attach to the check box on the main
form that would check or uncheck all of the items on the subform. The
event that I have so far, however, only updates the first row in the
subform:

Private Sub Completed__Click()

If [Forms]![Main Form]![Completed?] = True Then
[Forms]![Main Form]![Completion Date] = Date
[Forms]![Main Form]![Sub Form].[Form]![Completed?] = True
[Forms]![Main Form]![Sub Form].[Form]![Completion Date] =
Date
Else
[Forms]![Main Form]![Completion Date] = Null
[Forms]![Main Form]![Sub Form].[Form]![Completed?] = False
[Forms]![Main Form]![Sub Form].[Form]![Completion Date] =
Null
End If

End Sub

Can anyone see where I'm going wrong here?

Thanks!
Actually, instead of messing with the controls on the form, It's
easier to update all related records in the underlying table via SQL
then requery the subform:

<uncompliled code>

Private Sub Completed__Click()

DoCmd.SetWarnings False
Docmd.RunSQL "UPDATE mySubTable SET mySubTable.Completed = True,
mySubTable.CompletionDate = Date() WHERE mySubTable.stWorkRequestID =
" & Me.tblWorkRequestID"
DoCmd.SetWarnings True

Me.uSysSfmSubtable.Requery
End Sub

</uncompiled code>

This would update all records in the detail table then reload the
detail page. Assumes a numeric WorkRequestID else wrap the work
request ID in single or double quotes in the SQL statement.

Ron, King of Chi

May 15 '07 #3
Thanks for your help!

I've never used SQL before, so I'm not sure how to insert my
information into what you gave me...

If it helps, here is the SQL of an update query that I built to
perform the same action on the source table (to update the table
without using the form)

UPDATE [MainTable] INNER JOIN [SubTable] ON [MainTable].[Request #] =
[SubTable].[Request #] SET [SubTable].[Completed?] = True, [SubTable].
[Completion Date] = Date()
WHERE ((([MainTable].[Completed?])=True));

Can I alter this sequence to make it work in the form?

May 21 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: jdph40 | last post by:
Is there any way to do this on a form in Access 97? If the value of a combo box is .5, I want two check boxes to be visible (labels are a.m and p.m.). If the value of the combo box is 1, I want...
19
by: William Wisnieski | last post by:
Hello Everyone, I have a main form with a datasheet subform that I use to query by form. After the user selects two criteria on the main form and clicks the cmdShowResults button on the main...
2
by: Craig M | last post by:
Hi, I have a form, frmInvoices and a subform, frmInvoicesSub. On the parent form, i have a "print report" button, that prints a report depending on an ID on the parent form. Each record in...
0
by: Karl Roes | last post by:
I'm still having trouble posting follow-ups. :-( "Unable to retrieve message 7cc66112.0501041919.3a6628b4@posting.google.com" Now Turtle wrote "You'll need an extra field in the table...
5
by: jimc52 | last post by:
Hello Everyone: I am hoping one of the gurus here will give me some help. I have designed a form with some check boxes. I put the checkboxes there on the form so later I could use them as flags on...
14
by: Esprit | last post by:
Hi I am trying to use check boxes to select values. When I click on a check box it should select RatingID from my tblSysRating. The check boxes are on my subform and the RatingID text box is...
2
by: woodey2002 | last post by:
Hi Guys and thanks for your time. I have a search form for my database that allows users to select multiple criteria from multi select list boxes. I successfully integrated a multi select...
6
by: woodey2002 | last post by:
Hi Everyone. Thanks for your time. I am trying to create a search form that will allow users to select criteria from multiple multi select boxes. So far i have managed to achieve a search option...
1
by: woodey2002 | last post by:
Hi Everyone and many thanks for your time.. I am trying to begin access and a bit of VBA i am enjoying it but I have a annoying problem I just can’t get any where on. My databse mostly includes...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.