UPDATING QUERY USING SQL

[mrwendell]

I hope I can explain the without gibberish... anyway

Mainform/subform-- cbofindrecord select a team as follows
Sub cboFindRecord_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[CoachNbr] = '" & Me![cboFindRecord] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
DoCmd.GoToControl "frmARDMTESTsub"
DoCmd.GoToRecord , , acLast
End Sub

which populates subform/datasheet with all records associated with that coachnbr...from qry of all records...

now I would like to update only those records with a checkall command button...once clicked all the records in the subform.reviewed (checkbox) will be go from false to true... and timestamp the [moddate]using the following sql update...

DoCmd.RunSQL "UPDATE qryRDM041 SET qryRDM041.REVIEWED = 1, qryRDM041.moddate = Now() WHERE (((qryRDM041.REVIEWED)<> 1)); ", -1

IT WORKS PERFECTLY!!! BUT

now I need to update only the records that are currently associated with that coachnbr in the subform... this is what I tried....didnt work....and dont laugh!

DoCmd.RunSQL "UPDATE qryRDM041 SET qryRDM041.REVIEWED = 1, qryRDM041.moddate = Now() WHERE (((qryRDM041.REVIEWED)<> 1 and (qryrdm041.coachnbr)= me.bookmark); ", -1

ANY HELP OUT THERE...MY BRAIN IS FRIED!!

 

[JonFer]

This restricts the update to the coach number in the "Find" combo box if the button or event is from the main form.

DoCmd.RunSQL "UPDATE qryRDM041 SET qryRDM041.REVIEWED=True, qryRDM041.moddate=Now() WHERE qryRDM041.REVIEWED=False and qryrdm041.coachnbr=" & Me![cboFindRecord], -1


This restricts the update to the coach number in the current record on the main form.

DoCmd.RunSQL "UPDATE qryRDM041 SET qryRDM041.REVIEWED=True, qryRDM041.moddate=Now() WHERE qryRDM041.REVIEWED=False and qryrdm041.coachnbr=" & Me!CoachNbr, -1


John

http://www.interautomateconsulting.com