record should be deleted from the recordset but not from the main table

[arunakumari02]

I have a form with with ID,title, owner and checkboxe next to it.

When I uncheck the checkbox the row with ID,title, owner should be deleted from record set but not from the main table.

The problem is the record is getting deleted in the form and in the main table. (The fact should be the record should be deleted from the recordset but not from the maintable)

The code:

Private Function RemoveRecord(intRow As Integer)

strSQL = "SELECT chklock from tblProjects WHERE chklock=True"
Set rstProjects = CurrentDb.OpenRecordset(strSQL)
Debug.Print rstProjects.RecordCount

If Forms!frmpipeline!("chkResource") = False Then
rstProjects.Delete

End If
rstProjects.Close
Me.Refresh
Call Form_Open(1)

End Function

Any help is appreciated.

 

[boblarson]

I think some more detail on what you are actually trying to accomplish with this is required. When you open a recordset, it is live and unless you opened it read-only, it is capable of deleting records. You can't delete a record from the recordset without deleting it from the source that you have opened.

What you need is to revise the query for the recordsource and requery it but you will have to have some way to keep it from loading whatever records you have selected.

 

[arunakumari02]

How to uncheck the field in the table matching with the same ID in the form.

strSQL = "SELECT chkresourceOpt from tblProjects WHERE chkresourceOpt=true"
Set rstProjects = CurrentDb.OpenRecordset(strSQL)

If Forms!frmPipeline("chkResourceOptimizer" & intRow) = False And _
Not IsNull(Forms!frmPipeline("txtProjectID" & intRow)) Then

' Forms!frmPipeline!("chkResourceOptimizer" & intRow) = False
Forms!frmPipeline!("txtProjectID" & intRow) = Null
Forms!frmPipeline!("txtTitle" & intRow) = Null
Forms!frmPipeline!("txtSEOwner" & intRow) = Null
************* how to uncheck the field in the table for that ID.
Me.Refresh

Call Form_Open(1)

' End If

 

[arunakumari02]

I was trying to run this command.

I was trying to uncheck the field in the table but my problem is how to match tables projectID to the forms project ID

I having syntax error when I run this command.

DoCmd.RunSQL "UPDATE tblProjects SET ResourceOptimizer=False where ProjectID = Forms!frmPipeline("txtProjectID" &intRow)"

 

[DCrake]

Simple Software Solutions

You need to revise your syntax
From

DoCmd.RunSQL "UPDATE tblProjects SET ResourceOptimizer=False where ProjectID = Forms!frmPipeline("txtProjectID" &intRow)"

To

DoCmd.RunSQL "UPDATE tblProjects SET ResourceOptimizer=False where ProjectID =" & Forms(("txtProjectID")("intRow")

Or

DoCmd.RunSQL "UPDATE tblProjects SET ResourceOptimizer=False where ProjectID = '" & Forms("txtProjectID")("intRow") & "'"

if the ProjectID is a string