SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1108437%22%20slang%3D%22en-US%22%3EUsing%20ADO%20Recordset%20and%20Form%20Controls%20Together...Not%20Playing%20Nice%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1108437%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20form%20which%20I%20am%20using%20for%20editing%20an%20existing%20record.%26nbsp%3B%20This%20form%20is%20unbound%20however%20the%20form%20controls%20control%20name%20is%20used%20to%20tie%20back%20to%20the%20table%20for%20saving.%26nbsp%3B%20This%20works%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20thought...low%20overheads.%26nbsp%3B%20Let%20use%20this%20same%20unbound%20form%20to%20add%20a%20new%20record.%26nbsp%3B%20So%20I%20added%20some%20code%20that%20calls%20a%20sub%20as%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EDim%20strSQL%20As%20String%0ADim%20rst%20As%20ADODB.Recordset%0ADim%20ctl%20As%20Control%0ADim%20strField%20As%20String%0A%20%20%20%20Set%20rst%20%3D%20New%20ADODB.Recordset%20%20%20%20%0A%20%20%20%20strSQL%20%3D%20%22SELECT%20*%20FROM%20%22%20%26amp%3B%20strTable%20%20%20%20%0A%20%20%20%20With%20rst%20%20%20%20%0A%20%20%20%20%20%20%20%20.Open%20strSQL%2C%20gcn%2C%20adOpenForwardOnly%2C%20adLockOptimistic%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20.AddNew%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20For%20Each%20ctl%20In%20frm.Controls%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20Left(ctl.Name%2C%203)%20%3D%20%22txt%22%20Or%20Left(ctl.Name%2C%203)%20%3D%20%22cbo%22%20Then%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20strField%20%3D%20Right(ctl.Name%2C%20Len(ctl.Name)%20-%203)%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20rst.Fields(strField)%20%3D%20frm.Recordset.Fields(strField)%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20Next%20ctl%20%20%20%20%0A%20%20%20%20%20%20%20%20.Update%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20.Close%20%20%20%20%0A%20%20%20%20End%20With%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20idea%20being%20I%20pass%20in%20the%20name%20of%20the%20target%20table%20and%20the%20form%20as%20a%20form%20object%20and%20then%20open%20the%20recordset%20for%20addition%2C%20walk%20the%20controls%20on%20the%20form%20grab%20the%20control%20name%20which%20is%20same%20as%20the%20name%20in%20the%20originating%20table%20and%20populate%20it.%26nbsp%3B%20Seems%20like%20a%20reasonable%20idea.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcept%20the%20line%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20rst.Fields(strField)%20%3D%20frm.Recordset.Fields(strField)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EThrows%20Object%20variable%20not%20set(Error%2091)%20kind%20of%20helpful...kind%20of%20not.%26nbsp%3B%20I%20cant%20see%20what%20object%20it%20is%20meaning...Is%20it%20the%20form%3F%26nbsp%3B%20I%20dont%20think%20so%20as%20the%20controls%20are%20being%20checked%20to%20get%20here%2C%20its%20not%20the%20control%20as%20implicitly%20the%20assumption%20is%20by%20checking%20the%20form%20and%20controls%20they%20must%20be%20okay%20which%20leaves%20me%20the%20recordset...But%20why%3F%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EAs%20I%20read%20it%20I%20have%20set%20the%20recordset%20correctly%2C%20I%20even%20added%20rst%20to%20the%20.fields%20in%20case%20for%20some%20reason%20its%20getting%20muddled...but%20no.%26nbsp%3B%20That%20didnt%20solve%20it.%26nbsp%3B%20So%20now%20I%20am%20here%20to%20ask%20the%20wiser%20people%20what%20am%20I%20missing%3F%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1108437%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3E2016%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAccess%20Web%20App%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAccess%20Web%20Database%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1109955%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20ADO%20Recordset%20and%20Form%20Controls%20Together...Not%20Playing%20Nice%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1109955%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417778%22%20target%3D%22_blank%22%3E%40dynamictiger%3C%2FA%3E%26nbsp%3Bhow%20about%3C%2FP%3E%3CP%3E%3CSPAN%3E.Fields(strField)%20%3D%20ctl%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1113234%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20ADO%20Recordset%20and%20Form%20Controls%20Together...Not%20Playing%20Nice%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1113234%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417778%22%20target%3D%22_blank%22%3E%40dynamictiger%3C%2FA%3E%26nbsp%3Bdoes%20the%20code%20fail%20on%20every%20strfield%20value%2C%20or%20just%20a%20certain%20one%3F%26nbsp%3B%20I%20suggest%20adding%20a%20watch%20to%20the%20objects%20in%20play%20and%20walking%20thru%20a%20debug%20session%20for%20starters.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1113434%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20ADO%20Recordset%20and%20Form%20Controls%20Together...Not%20Playing%20Nice%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1113434%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460861%22%20target%3D%22_blank%22%3E%40MikeAtVisionCPS%3C%2FA%3E%26nbsp%3BThanks%2C%20I%20found%20the%20issue.%26nbsp%3B%20The%20form%20was%20dropped%20for%20some%20reason%20in%20this%20iteration.%26nbsp%3B%20Weird%20but%20got%20it%20fixed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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        
        .AddNew        
            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    
        .Update        
        .Close    
    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
Highlighted
Best Response confirmed by dynamictiger (Occasional Contributor)
Solution

@dynamictiger how about

.Fields(strField) = ctl

Highlighted

@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.

 

Highlighted

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