Using ADO Recordset and Form Controls Together...Not Playing Nice

Copper Contributor

I have a form which I am using for editing an existing record.  This form is unbound however the form controls control name is used to tie back to the table for saving.  This works fine.


So I thought...low overheads.  Let use this same unbound form to add a new record.  So I added some code that calls a sub as below:



Dim strSQL As String
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim strField As String
    Set rst = New ADODB.Recordset    
    strSQL = "SELECT * FROM " & strTable    
    With rst    
        .Open strSQL, gcn, adOpenForwardOnly, adLockOptimistic        
            For Each ctl In frm.Controls            
            If Left(ctl.Name, 3) = "txt" Or Left(ctl.Name, 3) = "cbo" Then            
                strField = Right(ctl.Name, Len(ctl.Name) - 3)                
                rst.Fields(strField) = frm.Recordset.Fields(strField)        
            End If    
            Next ctl    
    End With


The idea being I pass in the name of the target table and the form as a form object and then open the recordset for addition, walk the controls on the form grab the control name which is same as the name in the originating table and populate it.  Seems like a reasonable idea.


Except the line:


             rst.Fields(strField) = frm.Recordset.Fields(strField)


Throws Object variable not set(Error 91) kind of helpful...kind of not.  I cant see what object it is meaning...Is it the form?  I dont think so as the controls are being checked to get here, its not the control as implicitly the assumption is by checking the form and controls they must be okay which leaves me the recordset...But why?


As I read it I have set the recordset correctly, I even added rst to the .fields in case for some reason its getting muddled...but no.  That didnt solve it.  So now I am here to ask the wiser people what am I missing?




3 Replies
best response confirmed by dynamictiger (Copper Contributor)

@dynamictiger how about

.Fields(strField) = ctl

@dynamictiger does the code fail on every strfield value, or just a certain one?  I suggest adding a watch to the objects in play and walking thru a debug session for starters.


@MikeAtVisionCPS Thanks, I found the issue.  The form was dropped for some reason in this iteration.  Weird but got it fixed.

1 best response

Accepted Solutions
best response confirmed by dynamictiger (Copper Contributor)