Destiny947
asked on
Syntax to save subform datasheet to table
Please help... I have really been struggling to correctly code my save event procedure. I have searched the EE database, looked at Allen Brown's website... and I just can't get the coding right.
With the code below I am trying to save the fields in my subform datasheet to a table and am receiving a type mismatch error at the following line.
> rst1.Fields("MailCode") = fld.Value
Thanks
With the code below I am trying to save the fields in my subform datasheet to a table and am receiving a type mismatch error at the following line.
> rst1.Fields("MailCode") = fld.Value
Thanks
Private Sub cmdSAVE_Click()
On Error GoTo ErrorHandle
Dim plan As String, approval As Date, desc As String, auth As Date
Dim MailCode As String, BldgDesc As String, Action As String
Dim rst As ADODB.Recordset, rst1 As ADODB.Recordset, rs As ADODB.Recordset, fld As ADODB.Field
desc = Me!txtPlanDescription
plan = Me!txtPlanNo
approval = Me!txtCharterApprovalDate
'auth = Me.txtAuthDate
Set rst = New ADODB.Recordset
With rst
.Open "tblPlan", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
.Find "PlanNo = '" & plan & "'"
.Fields("PlanDescription") = desc
.Fields("CharterApprovalDate") = approval
' .Fields("AuthorizationDate") = auth
.Update
End With
rst.Close
Set rs = Me.subfrmGetSPBuildings.Form.RecordsetClone
Set rst1 = New ADODB.Recordset
rst1.Open "tblStrPlanBldgs", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Do While Not rs.EOF
rst1.AddNew
For Each fld In rs.Fields
rst1.Fields("PlanID") = plan
rst1.Fields("MailCode") = fld.Value
rst1.Fields("MailCode") = fld.Value
rst1.Fields("MailCode") = fld.Value
Next
rst1.Update
rst1.MoveNext
Loop
ExitHere:
Set rst = Nothing
Set rst1 = Nothing
Exit Sub
ErrorHandle:
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere
End Sub
ASKER
Hi Arthur,
Thanks for responding ! Sorry I didn't get to you sooner, was on a conf call.
With this loop I am trying to save the subform datasheet's current record fields into the table tblStrPlanBldgs.
It's at this section where I am having difficulty. I don't know how to loop through the datasheet and reference each of it's fields so I can assign their values to the table tblStrPlanBldgs.
Thanks for responding ! Sorry I didn't get to you sooner, was on a conf call.
With this loop I am trying to save the subform datasheet's current record fields into the table tblStrPlanBldgs.
It's at this section where I am having difficulty. I don't know how to loop through the datasheet and reference each of it's fields so I can assign their values to the table tblStrPlanBldgs.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Arthur,
Thank you so much! I will try this right now...
Thank you so much! I will try this right now...
ASKER
Hi again,
For some reason, I am still getting the error message type mismatch beginning at this line...
> rst1.Fields("MailCode") = rs.Fields("Mail Code")
For some reason, I am still getting the error message type mismatch beginning at this line...
> rst1.Fields("MailCode") = rs.Fields("Mail Code")
Set rs = Me.subfrmGetSPBuildings.Form.RecordsetClone
Set rst1 = New ADODB.Recordset
rst1.Open "select * from tblStrPlanBldgs where 1 = 0", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Do While Not rs.EOF
rst1.AddNew
rst1.Fields("PlanID") = plan
rst1.Fields("MailCode") = rs.Fields("Mail Code")
rst1.Fields("BldgDesc") = rs.Fields("Building Desc")
rst1.Fields("AuthorizedAction") = rs.Fields("Action")
rst1.Update
rst1.MoveNext
Loop
ASKER
I am going to shut the app down and restart. I converted my datasheet fields to string and now I'm getting the type mismatch error at:
> Set rst1 = New ADODB.Recordset
> Set rst1 = New ADODB.Recordset
Set rs = Me.subfrmGetSPBuildings.Form.RecordsetClone
Set rst1 = New ADODB.Recordset
rst1.Open "select * from tblStrPlanBldgs where 1 = 0", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Do While Not rs.EOF
rst1.AddNew
rst1.Fields("PlanNo") = plan
rst1.Fields("MailCode") = rs.Fields(CStr("Mail Code"))
rst1.Fields("BldgDesc") = rs.Fields(CStr("Building Desc"))
rst1.Fields("AuthorizationAction") = rs.Fields(CStr("Action"))
rst1.Update
rst1.MoveNext
Loop
ASKER
Same error message, same line... ???
ASKER
I rearranged where I instantiated the recordsets and the type mismatch is now at this line:
> Do While Not rs.EOF
> Do While Not rs.EOF
Set rst1 = New ADODB.Recordset
rst1.Open "select * from tblStrPlanBldgs where 1 = 0", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Set rs = Me.subfrmGetSPBuildings.Form.RecordsetClone
Do While Not rs.EOF
rst1.AddNew
rst1.Fields("PlanNo") = plan
rst1.Fields("MailCode") = rs.Fields(CStr("Mail Code"))
rst1.Fields("BldgDesc") = rs.Fields(CStr("Building Desc"))
rst1.Fields("AuthorizationAction") = rs.Fields(CStr("Action"))
rst1.Update
rst1.MoveNext
Loop
can you attach a stripped down copy of your database, so I can take a look at what is going on?
ASKER
Hi Arthur,
I can create a version that contains all that is necessary for this this form. The database itself is quite large.
It will take me a little while to do this, maybe an hour...
I can create a version that contains all that is necessary for this this form. The database itself is quite large.
It will take me a little while to do this, maybe an hour...
no problem. Just enough so I can see what is causing the problem.
ASKER
Okay, here you go... you need the excel file as well... At "3" select "08".
stripped-Copy-Excel-file.xls
EE-Help.mdb
stripped-Copy-Excel-file.xls
EE-Help.mdb
ASKER
From what I've read at EE, the problem is that Recordsetclone doesn't return a recordset, and I need to rewrite the code to use DAO rather than ADO....
OK, Give that a try, and see what it gets you.
I do not have Access 2007 on my box, and do not have 'access' to a copy.
AW
I do not have Access 2007 on my box, and do not have 'access' to a copy.
AW
ASKER
Thanks for all your help Arthur!
Glad to be of assistance. Switching to DAO was the solution?
AW
AW
ASKER
Hi AW,
Not yet... It still is giving me problems.... :(
Not yet... It still is giving me problems.... :(
What problems?
AW
AW
ASKER
"Object variable or with block variable not set". My subform's recordsource is a form whose recordsource is a temporary table created by a make table query run during the upload event on the main form.
Looking at what I have just written... poor, poor, poor design... and I think the subform's recordsource should be set to the query (changed to a select).
Looking at what I have just written... poor, poor, poor design... and I think the subform's recordsource should be set to the query (changed to a select).
For Each fld In rs.Fields
rst1.Fields("PlanID") = plan
rst1.Fields("MailCode") = fld.Value
rst1.Fields("MailCode") = fld.Value
rst1.Fields("MailCode") = fld.Value
Next
Explain what you are attempting to accomplish, in plain English, in terms of your application, not in terms of the recordset incolved.