Solved

Syntax to save subform datasheet to table

Posted on 2008-10-06
19
737 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

0
Comment
Question by:Destiny947
  • 12
  • 7
19 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 22649556
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
 

Author Comment

by:Destiny947
ID: 22650161
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
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 500 total points
ID: 22651024
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
 

Author Comment

by:Destiny947
ID: 22651098
Hi Arthur,

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

Author Comment

by:Destiny947
ID: 22651252
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
 

Author Comment

by:Destiny947
ID: 22651584
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
 

Author Comment

by:Destiny947
ID: 22651737
Same error message, same line... ???
0
 

Author Comment

by:Destiny947
ID: 22651801
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 22652453
can you attach a stripped down copy of your database, so I can take a look at what is going on?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Destiny947
ID: 22652545
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 22653078
no problem.  Just enough so I can see what is causing the problem.
0
 

Author Comment

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

Author Comment

by:Destiny947
ID: 22655125
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 22655579
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
 

Author Comment

by:Destiny947
ID: 22655918
Thanks for all your help Arthur!
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 22656203
Glad to be of assistance.  Switching to DAO was the solution?

AW
0
 

Author Comment

by:Destiny947
ID: 22657874
Hi AW,

Not yet... It still is giving me problems.... :(
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 22658186
What problems?

AW
0
 

Author Comment

by:Destiny947
ID: 22658923
"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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now