Solved

Syntax to save subform datasheet to table

Posted on 2008-10-06
19
746 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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
 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

830 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