I have the following code which deletes records in a recordset but when I go back to view the table that was in my SQL statement, rows also getting deleted from there too.
I don't want to delete rows from the underlying table, just want to delete from recordset.
Any ideas?
Code:
Dim sSql As String
sSql = "SELECT [Column Header] FROM tblMandatoryColumns "
sSql = sSql & vbNewLine & "WHERE Report = '" & reportName & "'"
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset(sSql, dbOpenDynaset)
Dim sFieldsToExport As String: sFieldsToExport = ""
Dim i As Integer
Dim j As Integer
For i = 0 To Forms![frmCustomReport].Controls("lstMyFields").ListCount - 1
For j = 0 To rs.Fields.Count - 1
If Not (rs.BOF And rs.EOF) Then
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
Debug.Print rs(j) & " " & j
Debug.Print Forms![frmCustomReport].Controls("lstMyFields").ItemData(i) & " " & i
If Forms![frmCustomReport].Controls("lstMyFields").ItemData(i) = rs(j) Then
rs.Delete //this line is actually deleting records in my table called tblMandatoryColumns
Forms![frmCustomReport].Controls("lstMyFields").Selected(i) = True
sFieldsToExport = sFieldsToExport & "[" & Forms![frmCustomReport].Controls("lstMyFields").ItemData(i) & "],"
Exit For
End If
rs.MoveNext
Loop
End If
Next j
Next i
End Function