01-14-2020 12:55 PM
01-14-2020 12:55 PM
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?
01-15-2020 05:30 AMSolution
@dynamictiger how about
.Fields(strField) = ctl
01-16-2020 10:03 AM
@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.
01-16-2020 11:30 AM
@MikeAtVisionCPS Thanks, I found the issue. The form was dropped for some reason in this iteration. Weird but got it fixed.