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?
