Implementing an updateable form recordset using stored procedures

Question

1. I'm trying to set up a form using a recordset based on a stored procedure on a MySQL server. When I use the command object and then Set Me.Recordset = cmd.execute it works fine, but I need to be able to update the two fields bound to the checkbox and textbox (tinyint and varchar ) on my form. Of course, this doesn't work.

2. In view of this design problem, I tried to use the .Open method and "CALL procName ('value1', 'value2');" syntax to open the recordset, and then create a new of the broken recordset, populate the schema from the .Open method, then loop through the records and append to the new rs. Finally, set it as the form's recordset like this:

3. Dim OriginalRecordset As ADODB.Recordset
    Dim Field As ADODB.Field
   
    Set NewRecordset = New ADODB.Recordset
    Set OriginalRecordset = New ADODB.Recordset
    OriginalRecordset.Open "CALL `DosarClient_Functie` ('14575','2234');", SQL_ADE.Conn, adOpenDynamic, adLockPessimistic, adCmdText
   
    For Each Field In OriginalRecordset.Fields
      NewRecordset.Fields.Append Field.Name, Field.Type, Field.DefinedSize, adFldIsNullable Or adFldUpdatable
    Next Field
   
    NewRecordset.CursorType = adOpenDynamic
    NewRecordset.CursorLocation = adUseClient
    NewRecordset.LockType = adLockOptimistic
    NewRecordset.Open
   
    OriginalRecordset.MoveFirst
   
    Do Until OriginalRecordset.EOF
       NewRecordset.AddNew
       For Each Field In OriginalRecordset.Fields
          If Not IsNull(Field.value) Then
              NewRecordset.Fields(Field.Name).value = Field.value
          Else
          End If
       Next Field
   
       NewRecordset.Update
       OriginalRecordset.MoveNext
    Loop
   
    Set Me.Recordset = NewRecordset

4. Additional information: NewRecordset variables are declared private at the form level. I also know it's bad practice to call a procedure like I did, but I can't think of any other way to accomplish this task.

5. Important: I cannot and do not want to use local tables. That would be the easy fix, but it would also be inconsistent with the rest of the code. Thanks:)

The problem is: If I do it as I explained, I get #Name errors for the bound fields, or they are set as recordsets and I can change their values, but I don't see the initial values.

Answer 1

From what I understand, you have tried various approaches, such as creating a new recordset and copying the schema from the original recordset, but you still cannot update the checkbox field bound to the form.

Using stored procedures in this way can be challenging, I recommend that you individually test whether the called stored procedure allows updates.

1. Try using the .Clone method instead of manual copy mode to create a copy of the original recordset.
2. Verify that the checkbox control on the form is properly bound to the recordset field and allows updates.
3. Consider using a different cursor type, such as adOpenKeyset, to provide more flexibility in updating data.

Hope this helps!