deleting records of a recordset without actually deleting rows of the underlying table

[mp3909]

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?

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

[CJ_London]

do you mean to delete or do you mean to filter out?

Also explain what your code is supposed to do and what you do with the rs recordset once you have 'deleted' the relevant records

[mp3909]

I have a table called tblMandatoryColumns which has 2 fields.
The first field contains the names of the headers.
The second field contains the names of the report.

Like this:





I am then opening this table through a recordset.

I am then looping through this recordset to see if the value in field1 is equal to the value in my list box of a form.
If it is, then select the value in my list box and most importantly, remove this value from my recordset as I don't want to iterate over it again as it has already been matched.

[Gicu]

Unless you reset (which you don't) the recordset it will not go through the same row again, so you don't need to worry about that. Just comment the line out and test it.

Cheers,
Vlad

[mp3909]

Hey Vlad,

This is my new code - can you please tell me where I need to insert the "reset recordsert" line?

Public Function SelectItemsInAListBox(reportName As String)

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
If Not (rs.BOF And rs.EOF) Then
    
    rs.MoveLast
    rs.MoveFirst
    Do While Not rs.EOF
    
        Debug.Print rs(j)
        
        For i = 0 To Forms![frmCustomReport].Controls("lstMyFields").ListCount - 1
        
            Debug.Print Forms![frmCustomReport].Controls("lstMyFields").ItemData(i)
        
            If Forms![frmCustomReport].Controls("lstMyFields").ItemData(i) = rs(j) Then
                
                Forms![frmCustomReport].Controls("lstMyFields").Selected(i) = True
    
                sFieldsToExport = sFieldsToExport & "[" & Forms![frmCustomReport].Controls("lstMyFields").ItemData(i) & "],"
    
                Exit For
    
            End If
            
        Next i
    
        rs.MoveNext
    
    Loop
    
End If
    
    
End Function

[Gicu]

I think you misunderstood me, I am not saying you should reset the recordset (you do it here: Set rs = CurrentDb.OpenRecordset(sSql, dbOpenDynaset)). All I'm saying is that looping through a recordset only happens once so, unless you are reseting it, you do not need to delete the matching record(s). SImply select the corresponding entry in the list box and movenext.

And I would use dbOpenSnapShot if you do not need to manipulate the recordself itself.

Cheers,
Vlad