We help IT Professionals succeed at work.

Syntax to save subform datasheet to table

Destiny947
Destiny947 asked
on
772 Views
Last Modified: 2013-11-28
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
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

Open in new window

Comment
Watch Question

what are you trying to do with this loop?

            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.

Author

Commented:
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.



This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Arthur,

Thank you so much! I will try this right now...

Author

Commented:
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")
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

Open in new window

Author

Commented:
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 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

Open in new window

Author

Commented:
Same error message, same line... ???

Author

Commented:
I rearranged where I instantiated the recordsets and the type mismatch is now at this line:

>     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

Open in new window

can you attach a stripped down copy of your database, so I can take a look at what is going on?

Author

Commented:
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...
no problem.  Just enough so I can see what is causing the problem.

Author

Commented:
Okay, here you go... you need the excel file  as well... At "3" select "08".
stripped-Copy-Excel-file.xls
EE-Help.mdb

Author

Commented:
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

Author

Commented:
Thanks for all your help Arthur!
Glad to be of assistance.  Switching to DAO was the solution?

AW

Author

Commented:
Hi AW,

Not yet... It still is giving me problems.... :(
What problems?

AW

Author

Commented:
"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).
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.