Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 757
  • Last Modified:

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

0
Destiny947
Asked:
Destiny947
  • 12
  • 7
1 Solution
 
Arthur_WoodCommented:
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.
0
 
Destiny947Author 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.



0
 
Arthur_WoodCommented:
well, the code that you have wil not do what you want.  Do you not know the names of the fields in the data sheet?  YOu should then be able to assign the values from the datasheet to the corresponding fields in the recordset.  You do not 'loop through the datasheet's fields and assign their values to the fields in tblStrPlanBldgs.  You need to assign corresonding values from the Datasheet to the related field in the table, and that cannot be done in a loop.

It would look more like this:
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("MailCode")
            rst1.Fields("AnotherField")=rs.Fields("ValueforAnotherField")
 
            rst1.Update
            rst1.MoveNext
        Loop

Open in new window

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Destiny947Author Commented:
Hi Arthur,

Thank you so much! I will try this right now...
0
 
Destiny947Author 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

0
 
Destiny947Author 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

0
 
Destiny947Author Commented:
Same error message, same line... ???
0
 
Destiny947Author 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

0
 
Arthur_WoodCommented:
can you attach a stripped down copy of your database, so I can take a look at what is going on?
0
 
Destiny947Author 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...
0
 
Arthur_WoodCommented:
no problem.  Just enough so I can see what is causing the problem.
0
 
Destiny947Author Commented:
Okay, here you go... you need the excel file  as well... At "3" select "08".
stripped-Copy-Excel-file.xls
EE-Help.mdb
0
 
Destiny947Author 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....
0
 
Arthur_WoodCommented:
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

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

AW
0
 
Destiny947Author Commented:
Hi AW,

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

AW
0
 
Destiny947Author 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).
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 12
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now