By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,903 Members | 2,075 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,903 IT Pros & Developers. It's quick & easy.

Select all check boxes in subform

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.