Code to clear Checkbox on selected records

  • Thread starter Thread starter Bradley C. Hammerstrom
  • Start date Start date
B

Bradley C. Hammerstrom

Access2000

Having trouble getting this to work:
*******************************
' Clear PBI checks on Defect selected
Private Sub cmdClearPBI_Click()
Dim dbs As Database

Set dbs = CurrentDb
dbs.Execute "UPDATE tblObservations " _
& "SET PBI = 0 " _
& "WHERE Defect_ID = Me![cboSelectDefect];"

End Sub
*******************************
User first selects Defect from cboSelectDefect. The cbo's RowSource is:
SELECT tblDefectList.DefectID, tblDefectList.Defect
FROM tblDefectList
ORDER BY tblDefectList.Defect;
(The bound column is DefectID and is hidden.)

A subform returns the list of records with that DefectID using:
*****************************
Private Sub cboSelectDefect_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[DefectID] = " & Str(Me![cboSelectDefect])
Me.Bookmark = rs.Bookmark

End Sub
****************************
Next the user wants to uncheck PBI on all those records by clicking
cmdClearPBI.

What do I have wrong?
Brad H.
 
Brad, the SQL you are creating in your cmdClearPBI_Click event add's
Me![...] - and this is something that's only understood by the Form - not
the query engine.And that's why it fails.
Change the code to this:

' Clear PBI checks on Defect selected
Private Sub cmdClearPBI_Click()
Dim dbs As Database

Set dbs = CurrentDb
dbs.Execute "UPDATE tblObservations " _
& "SET PBI = 0 " _
& "WHERE Defect_ID = " & Me![cboSelectDefect]

End Sub

This way, it's the value of you combo that will be added to the SQL, rather
than an unknown reference to "Me![cboSelectDefect]"
 
Back
Top