Export a form's recordset to a table

A

Angel69

Registered User.
Local time
Today, 13:55
Joined
Jun 11, 2013
Messages
86
I have a main form with combo boxes to filter a subform. I want to be able to export the filtered subform data to a table within the database and only include the filtered dataset not all records. I'm new to VB code and need some help. TIA
 
Hello Angel69, Welcome to AWF.. :)

What is the Filtering Code you are using? You could use something along the lines of.. (Air coded/Not Tested)
Code: 
Private Sub cmdAddToTable_Click()
    Dim tmpSQL As String
    tmpSQL = Me.subFormName.Form.RecordSource
    CurrentDb.Execute("INSERT INTO theTableName (theFieldListInTheSubForm)" & tmpSQL)
End Sub
 
Hi,

I'm using Allen Brown's code for search criteria found at allenbrowne.com/ser-62code.html. I will attempt your suggestion. Where within my module would I add your code? Thank you!
 
The code would go into a Button that you will create called "cmdAddToTable", when you have done all the filtering you want to do.. And finally click this cmdAddToTable button it will add the information to the table..
 
Thank you! I attempted the following but got an error 'Run-time error '3346': Number of query values and destination fields are not the same.

Private Sub cmdAddToTable_Click()

Dim tmpSQL As String

tmpSQL = Me.frmPacsVerificationSubForm.Form.RecordSource

'CurrentDb.Execute "INSERT INTO tblResultsTracking (ID,Business_Line, Created_Date, Updated_Date, Template_ID,Template_Name, Created ADENT, Created Last, Created First, Open By AU, Initiated ADENT, Initiated Last Name, Initiated first Name,Approved Date,Approved First Name, Approved Last Name, Approved ADENT, Total Records Approved, Memo1, Memo2, Memo3)" & tmpSQL

CurrentDb.Execute "INSERT INTO tblResultsTracking (ID, Business_Line)" & tmpSQL

End Sub

Can you show me what it would look like if I only need a few fields? As you can see the code I commented out was my attempt to use all fields and that did not work either. I got error 'Run-Time error '3134': Syntax error in INSERT INTO statement.

Thanks!
 
Okay try Debugging the Error..
Code: 
Private Sub cmdAddToTable_Click()
    Dim tmpSQL As String
    tmpSQL = Me.frmPacsVerificationSubForm.Form.RecordSource
    tmpSQL = "INSERT INTO tblResultsTracking (ID, Business_Line)" & tmpSQL
    Debug.Print tmpSQL
    CurrentDb.Execute tmpSQL
End Sub
Show what you got on the Immediate window (Ctrl+G, in the VBA window)
 
This is what I got but none of it looks odd to me or should I be including the ID in the output of my query?:

INSERT INTO tblResultsTracking (ID, Business_Line)

SELECT tblAUCodes.Business_Line, tblPacsData.Created_Date, tblPacsData.Updated_Date, tblPacsData.[Template_ID], tblPacsData.Template_Name, tblPacsData.Template_Type, tblPacsData.Payment_Type_Description, tblPacsData.[Template Status], tblPacsData.[Created ADENT], tblPacsData.[Created Last], tblPacsData.[Created First], tblPacsData.[Open By AU], tblPacsData.[Initiated ADENT], tblPacsData.[Initiated Last Name], tblPacsData.[Initiated first Name], tblPacsData.[Approved Date], tblPacsData.[Approved First Name], tblPacsData.[Approved Last Name], tblPacsData.[Approved ADENT], tblPacsData.[Total Records Approved], tblPacsData.Memo1, tblPacsData.Memo2, tblPacsData.Memo3, tblPacsData.ID FROM tblAUCodes INNER JOIN tblPacsData ON tblAUCodes.AU=tblPacsData.[Open By AU]

WHERE (((tblPacsData.Template_Type)="Outgoing") AND ((tblPacsData.Payment_Type_Description) Like 'Wire' Or (tblPacsData.Payment_Type_Description)='ACH') AND ((tblPacsData.[Template Status])="Approved"));
 
Okay the Insert statement should specify the fields it should Update.. as you have added ID and Business_Line.. The Select should have only the two fields.. in the SELECT Query.. So reconstruct your SELECT Query.. I hope the Filer will be returned from the Code Allen Brown has provided.. So you just need to concatenate the WHERE part to the SELECT..
 
oh, you lost me :confused:

I can not edit the query because the subform is based on it. Allen Brown's code just filters the fields on the subform based on combo box selection in the main form.

Do I need to add a select and where clause to my code?

Thanks!
 
Okay lets tackle it this way..
Code: 
Private Sub cmdAddToTable_Click()
    Dim tmpRS As DAO.Recordset, tmpSQL As String
    Set tmpRS = Me.frmPacsVerificationSubForm.Form.RecordSetClone
    If tmpRS.RecordCount > 0 Then
        Do While Not tmpRS.EOF
            tmpSQL = "INSERT INTO tblResultsTracking (ID, Business_Line) VALUES (" & tmpRS!ID & ", '" & tmpRS!Business_Line & "')"
            CurrentDb.Execute tmpSQL
            tmpRS.MoveNext
        Loop
    End If
    Set tmpRS = Nothing
End Sub
 
Thanks that worked for just the two fields but I want to add the rest of the fields I'm getting an error '3134' Syntax error in INSERT INTO statement:

I'm wondering if my quotes are correct around my values. Here's my code:


Private Sub cmdAddToTable_Click()
Dim tmpRS As DAO.Recordset, tmpSQL As String
Set tmpRS = Me.frmPacsVerificationSubForm.Form.RecordsetClone
If tmpRS.RecordCount > 0 Then
Do While Not tmpRS.EOF
tmpSQL = "INSERT INTO tblResultsTracking (Template_ID, Template_Name, Payment_Type_Description, Created_Date,Approved Date,Approved ADENT,Business_Line, ID) VALUES (" & tmpRS!Template_ID & "," & tmpRS!Template_Name & "," & tmpRS!Payment_Type_Description & "," & tmpRS!Created_Date & "," & tmpRS!Approved_Date & "," & tmpRS!Approved_ADENT & "," & tmpRS!Business_Line & ")"
CurrentDb.Execute tmpSQL
tmpRS.MoveNext
Loop
End If
Set tmpRS = Nothing
End Sub
 
Okay, for Strings you need to enclose them inbetween single quotes.. for dates you need to enclose them between hast tags.. I am taking a guess at your fields.. Only number need not have any enclosing characters.. So based on that..
Code: 
Private Sub cmdAddToTable_Click()
    Dim tmpRS As DAO.Recordset, tmpSQL As String
    Set tmpRS = Me.frmPacsVerificationSubForm.Form.RecordsetClone
    If tmpRS.RecordCount > 0 Then
        Do While Not tmpRS.EOF
            tmpSQL = "INSERT INTO tblResultsTracking (Template_ID, Template_Name, Payment_Type_Description, Created_Date,Approved Date,Approved ADENT,Business_Line, ID) VALUES (" & tmpRS!Template_ID & ",[COLOR=Red][B]'[/B][/COLOR]" & tmpRS!Template_Name & "[COLOR=Red][B]'[/B][/COLOR],[COLOR=Red][B]'[/B][/COLOR]" & tmpRS!Payment_Type_Description & "[COLOR=Red][B]'[/B][/COLOR],[COLOR=Red][B]#[/B][/COLOR]" & tmpRS!Created_Date & "[COLOR=Red][B]#[/B][/COLOR],[COLOR=Red][B]#[/B][/COLOR]" & tmpRS!Approved_Date & "[COLOR=Red][B]#[/B][/COLOR],[COLOR=Red][B]'[/B][/COLOR]" & tmpRS!Approved_ADENT & "[COLOR=Red][B]'[/B][/COLOR],[COLOR=Red][B]'[/B][/COLOR]" & tmpRS!Business_Line & "[COLOR=Red][B]'[/B][/COLOR])"
            CurrentDb.Execute tmpSQL
            tmpRS.MoveNext
        Loop
    End If
    Set tmpRS = Nothing
End Sub
 
now I'm getting erorr 'number of query values and destination fields are not the same'
 
They are all text with the exception of the two dates. Now I'm getting erorr 'number of query values and destination fields are not the same'
 
Okay, got it.... I left off the ID :-(

However, now I have three memo fields and it's giving me an error '3075' syntax error (missing operator) in query expression and it's showing the text that is in that memo. I have memos like this

'" & tmpRS!Memo1 & "', '" & tmpRS!Memo2 & "', '" & tmpRS!Memo3 & "'

Is that wrong?

Thank you!!

EDITED: I put '%" & tmpRS!Memo1 & "%', '%" & tmpRS!Memo2 & "%', '%" & tmpRS!Memo3 & "%' and it's working but my memo fields have '%' plus the text that's in the memo. Any ideas?? Thanks!
 
Last edited:
Is the Memo fields by any chance Null? The Query seems to be alright.. Can you show the entire query?
 
Yes, some are null but it's adding it to the beginning of each memo even if it is not null so all three memo fields have the symbol.

Here's my code:

Private Sub cmdAddToTable_Click()
Dim tmpRS As DAO.Recordset, tmpSQL As String
Set tmpRS = Me.frmPacsVerificationSubForm.Form.RecordsetClone
If tmpRS.RecordCount > 0 Then
Do While Not tmpRS.EOF
tmpSQL = "INSERT INTO tblResultsTracking (Template_ID, Template_Name, Payment_Type_Description, Created_Date, Approved_Date, Approved_ADENT, Business_Line, ID, Updated_Date, Created_Name, Open_By_AU, Initiated_ADENT, Initiated_Name, Approved_Name, Memo1, Memo2, Memo3) VALUES ('" & tmpRS!Template_ID & "','" & tmpRS!Template_Name & "','" & tmpRS!Payment_Type_Description & "',#" & tmpRS!Created_Date & "#,#" & tmpRS!Approved_Date & "#,'" & tmpRS!Approved_ADENT & "','" & tmpRS!Business_Line & "','" & tmpRS!ID & "', #" & tmpRS!Updated_Date & "#,'" & tmpRS!Created_Name & "', '" & tmpRS!Open_By_AU & "', '" & tmpRS!Initiated_ADENT & "', '" & tmpRS!Initiated_Name & "', '" & tmpRS!Approved_Name & "', '%" & tmpRS!Memo1 & "%', '%" & tmpRS!Memo2 & "%', '%" & tmpRS!Memo3 & "%')"
CurrentDb.Execute tmpSQL
tmpRS.MoveNext
Loop
End If
Set tmpRS = Nothing
End Sub
 
That looks perfectly fine to me.. :confused: What did debugging the tmpSQL in the Immediate Window show you? Does that come out alright?
Code: 
            tmpSQL = "INSERT INTO tblResultsTracking (Template_ID, Template_Name, Payment_Type_Description, Created_Date, Approved_Date, Approved_ADENT, " & _
                     "Business_Line, ID, Updated_Date, Created_Name, Open_By_AU, Initiated_ADENT, Initiated_Name, Approved_Name, Memo1, Memo2, Memo3) VALUES ('" & _
                     tmpRS!Template_ID & "','" & tmpRS!Template_Name & "','" & tmpRS!Payment_Type_Description & "',#" & tmpRS!Created_Date & "#,#" & _
                     tmpRS!Approved_Date & "#,'" & tmpRS!Approved_ADENT & "','" & tmpRS!Business_Line & "','" & tmpRS!ID & "', #" & tmpRS!Updated_Date & "#,'" & _
                     tmpRS!Created_Name & "', '" & tmpRS!Open_By_AU & "', '" & tmpRS!Initiated_ADENT & "', '" & tmpRS!Initiated_Name & "', '" & tmpRS!Approved_Name & _
                     "', '" & tmpRS!Memo1 & "', '" & tmpRS!Memo2 & "', '" & tmpRS!Memo3 & "')"
 
The intermediate window is showing the '%'. Here's one line item:

INSERT INTO tblResultsTracking (Template_ID, Template_Name, Payment_Type_Description, Created_Date, Approved_Date, Approved_ADENT, Business_Line, ID, Updated_Date, Created_Name, Open_By_AU, Initiated_ADENT, Initiated_Name, Approved_Name, Memo1, Memo2, Memo3) VALUES ('159555','CPAS13A Cert','Wire',#3/20/2013 2:55:39 PM#,#5/29/2013 2:50:04 PM#,'KARI.A.TOLIFMAN','ABS','100', #5/29/2013 2:50:04 PM#,'JOE LAVARNE', '0057147', 'JOE.LAVARNE', 'JUSTIN LAVARNWAY', 'CORI TOLIFMAN', '%Tanner Fischer emailed us with Wilmington Trust on 5/17/13 to confirm wiring instructions to Wilmington sent to WF by Dorri with CPS. Tomi confirmed instructions on 5/28/2013 via email.,TOLIFMAN, CORI ,05/29/2013 14:49:46 ET%', '%%', '%%')
 
No Angel, try the one I gave in Post#18.. Without the % symbol.. Try the following..
Code: 
Private Sub cmdAddToTable_Click()
    Dim tmpRS As DAO.Recordset, tmpSQL As String
    Set tmpRS = Me.frmPacsVerificationSubForm.Form.RecordsetClone
    
    If tmpRS.RecordCount > 0 Then
        Do While Not tmpRS.EOF
            tmpSQL = "INSERT INTO tblResultsTracking (Template_ID, Template_Name, Payment_Type_Description, Created_Date, Approved_Date, Approved_ADENT, " & _
                     "Business_Line, ID, Updated_Date, Created_Name, Open_By_AU, Initiated_ADENT, Initiated_Name, Approved_Name, Memo1, Memo2, Memo3) VALUES ('" & _
                     tmpRS!Template_ID & "','" & tmpRS!Template_Name & "','" & tmpRS!Payment_Type_Description & "',#" & tmpRS!Created_Date & "#,#" & _
                     tmpRS!Approved_Date & "#,'" & tmpRS!Approved_ADENT & "','" & tmpRS!Business_Line & "','" & tmpRS!ID & "', #" & tmpRS!Updated_Date & "#,'" & _
                     tmpRS!Created_Name & "', '" & tmpRS!Open_By_AU & "', '" & tmpRS!Initiated_ADENT & "', '" & tmpRS!Initiated_Name & "', '" & tmpRS!Approved_Name & _
                     "', '" & tmpRS!Memo1 & "', '" & tmpRS!Memo2 & "', '" & tmpRS!Memo3 & "')"
            [COLOR=Red][B]Debug.Print tmpSQL[/B][/COLOR]
            CurrentDb.Execute tmpSQL
            tmpRS.MoveNext
        Loop
    End If
    Set tmpRS = Nothing
End Sub
 
You must log in or register to reply here.

Similar threads

J
Solved Troubleshooting Subform
Replies
10
Views
389
jamesave
J
S
Solved Multiple listboxes on main form
Replies
6
Views
742
soupy8728
S
tmd63
How to use a combo box to filter a forms data
Replies
2
Views
658
theDBguy
theDBguy
A
Form with subform in transaction
2
Replies
39
Views
1,644
Albert D. Kallal
A
arnelgp
Another demo of JSON Recordset (mimic the behavior of DAO.Recordset)
Replies
6
Views
473
GPGeorge
GPGeorge

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom