Read only recordset "local" update

Question

I have DAO recordset that is generated with pass-through query to postgresql stored function. I use it to fill out combobox in my form. What I need is additional item in combobox with "AllItems" description. But the recordset is read-only (that's normal in this case). So I cannot just add new row to it. Can I do any kind of in memory recordset clone, copy or anything like that to make addition possible? I don't want to update recordsource. And I don't want to hardcode this option in to the pgsql function as well.

Public Sub fillCboAssortmentType()
    
    Dim rs As DAO.Recordset
    
    If (lngViewContext = acMyItems) Then
            Set rs = getAssortmentTypesByDAO(TempVars!loggedUser)
    Else (lngViewContext = acAllItems) Then
        Set rs = getAssortmentTypesByDAO
    End If
        
' It wont work, because the rs is RO
    With rs
        .AddNew
        !type_id = 0
        !type_name = "***AllItems***"
    End With
        
' It wont work neither, because cboTypeFilter rowsource is Table/Query
    Set Me.cboTypeFilter.Recordset = rs
    Me.cboTypeFilter.AddItem "0;***AllItems***"
End Sub

Any suggestions? TY All.

You can use UNION ALL to add AllItems, or read the recordset into a value list string, where you add AllItems (and change the combobox row source type to Value List). Both not really what you are asking for... but easy to do. — Andre, Jan 09 '22 at 18:48

score 1 · Answer 1 · answered Jan 09 '22 at 18:55

1

I think you are asking for a "In Memory" Recordset. Let's assume you have a table which looks like this

Then the following code will read the values from the table and copy it to a in memory recordset and add a new value but only in memory

Option Compare Database
Option Explicit

    Sub inMemory()
    
        Dim rs As ADODB.Recordset
    
        Set rs = New ADODB.Recordset
        With rs.Fields
            .Append "val", adVarChar, 64
        End With
    
        Dim sourceRs As DAO.Recordset
        Dim db As DAO.Database
        Set db = CurrentDb
        Set sourceRs = db.OpenRecordset("SELECT * FROM tbl")
        
        Dim i As Long
        rs.Open
        Do Until sourceRs.EOF
            rs.AddNew
            rs.Fields(0).Value = sourceRs.Fields(0).Value
            rs.Update
            sourceRs.MoveNext
        Loop
        
        rs.AddNew
        rs.Fields(0).Value = "Cancel"
        rs.Update
        
        ' let's print the list just for testing
        rs.MoveFirst
        Do Until rs.EOF
            Debug.Print rs.Fields(0).Value
            rs.MoveNext
        Loop
    
    End Sub

answered Jan 09 '22 at 18:55

Storax

11,158
3
16
33

And how is that recordset used as RowSource for combobox? – June7 Jan 09 '22 at 19:10
What do you mean? I would use `AddItem`? – Storax Jan 09 '22 at 19:20
So loop through recordset and build Value List string for combobox RowSource? Would UNION query as RowSource as suggested by @Andre be simpler approach? – June7 Jan 09 '22 at 19:42
Yes, that is my suggestion. You are free to post another solution. – Storax Jan 09 '22 at 19:43
Union All is the way to go for me. In this particular example I stuck and needed your kick guys to move forward. As in memory table is a great solution, for me it was better and easier to do Union All. Thank You. – Smok Jan 16 '22 at 16:58

Read only recordset "local" update

1 Answers1