?
Solved

Copy data from one table to another using VB button on Form

Posted on 2009-05-07
37
Medium Priority
?
823 Views
Last Modified: 2013-11-28
From a form displaying a single ID record from the MRB_Data-Table, I want to create a button that will copy the information from the Data-Table (like displayed below) using the ID field as its criteria. Meaning I only want the data from that specific ID record to copy over, not all records. All the table names match up except for the last one. See below.

Data_Table.ID --> MRB_Data_Table.ID (primary key field)
Data_Table.Feature --> MRB_Data_Table.Feature
Data_Table.Method --> MRB_Data_Table.Method
Data_Table.Type --> MRB_Data_Table.Type
Data_Table.ToolID --> MRB_Data_Table.ToolID
Data_Table.Failed --> MRB_Data_Table.Failed
Data_Table.Measures --> MRB_Data_Table.Measures
Data_Table.Notes --> MRB_Data_Table.Notes
Data_Table.ToolSpecs --> MRB_Data_Table.Criteria

How would one go about doing this?


0
Comment
Question by:filtrationproducts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 20
  • 17
37 Comments
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24329529
Assuming that your form's text boxes are named the name of the field with txt in front of them, and that the fields are all text, this code should do what you want. (You can't insert data into the Autonumber (ID) field.)
Dim strSQL As String
strSQL = "INSERT INTO MRB_Data_Table (Feature, Method, Type, ToolID, " & _
    "Failed, Measures, Notes, Criteria) VALUES ('" & txtFeature & "', '" & _
    txtMethod & "', '" & txtType & "', '" & txtToolID & "', '" & txtFailed & "', '" & _
    txtMeasures & "', '" & txtNotes & "', '" & txtToolSpecs & "') " & _
    "WHERE ID=" & txtID
CurrentProject.Connection.Execute strSQL

Open in new window

0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24329577
Since I will have the MRB_data-table form open where the button is located dont I need to call-out the "Data-Table" somewhere?
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24329605
If your form is (and its controls are) bound to the table, the data is in the text boxes and can be used to populate your second table. You could select from the first table to append to the second table but it is really not necessary. Would you rather select from the table than use the text boxes?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:filtrationproducts
ID: 24329624
The table bound to the form the button will be on is "MRB_Data-Table", and it will be pulling the data from the other table "Data-Table" into the opened form (MRB_Data-Table)
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24329671
I see what you mean. This code will copy the data from the Data-Table table into the MRB_Data-Table table.
Dim strSQL As String
strSQL = "INSERT INTO Data_Table Feature, Method, Type, ToolID, " & _
    "Failed, Measures, Notes, Criteria SELECT Feature, Method, Type, ToolID, " & _
    "Failed, Measures, Notes, ToolSpecs FROM  MRB_Data-Table " & _
    "WHERE ID=" & txtID
CurrentProject.Connection.Execute strSQL

Open in new window

0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24329694
Awesome but should I flip the table call outs since i'm inserting into MRB_Data-Table? (see below)
Dim strSQL As String
strSQL = "INSERT INTO MRB_Data_Table Feature, Method, Type, ToolID, " & _
    "Failed, Measures, Notes, Criteria SELECT Feature, Method, Type, ToolID, " & _
    "Failed, Measures, Notes, ToolSpecs FROM Data-Table " & _
    "WHERE ID=" & txtID
CurrentProject.Connection.Execute strSQL

Open in new window

0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24329858
Yes, sorry but I confused myself.
0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24329923
I tried the code below (using me.parent.id) to pull the record ID and a msgbox just to verify it was pulling the number and it was. But I am receiving an error, I attached the image below.
Dim strSQL As String
strSQL = "INSERT INTO MRB_Data_Table Feature, Method, Type, ToolID, " & _
    "Failed, Measures, Notes, Criteria SELECT Feature, Method, Type, ToolID, " & _
    "Failed, Measures, Notes, ToolSpecs FROM Data-Table " & _
    "WHERE ID=" & Me.Parent.ID
    MSGBOX ME.PARENT.ID
CurrentProject.Connection.Execute strSQL

Open in new window

untitled.JPG
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24329986
Please add the parentheses as shown in the snippet.

Dim strSQL As String
strSQL = "INSERT INTO MRB_Data_Table (Feature, Method, Type, ToolID, " & _
    "Failed, Measures, Notes, Criteria) SELECT Feature, Method, Type, ToolID, " & _
    "Failed, Measures, Notes, ToolSpecs FROM Data-Table " & _
    "WHERE ID=" & Me.Parent.ID
    MSGBOX ME.PARENT.ID
CurrentProject.Connection.Execute strSQL

Open in new window

0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24330120
I copied in what you have above but now I got a "Syntax error in FROM clause" error.

I checked all the field names against the design view of the tables to make sure they were spelt the same and they are.
0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24330126
Oh, the ID is a primary key but it is not an autonumber. If that changes anything...
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24330185
Sonetimes Access is very picky about syntax. Try the code in the snippet. If the ID field is Text, substitute this line:

    "WHERE Data-Table.ID='" & Me.Parent.ID & "';"

Dim strSQL As String
strSQL = "INSERT INTO MRB_Data_Table (ID, Feature, Method, Type, ToolID, " & _
    "Failed, Measures, Notes, Criteria) SELECT Data-Table.ID, Data-Table.Feature, " & _
    "Data-Table.Method, Data-Table.Type, Data-Table.ToolID, " & _
    "Data-Table.Failed,Data-Table. Measures, Data-Table.Notes, " & _
    "Data-Table.ToolSpecs FROM Data-Table " & _
    "WHERE Data-Table.ID=" & Me.Parent.ID & ";"
    MSGBOX ME.PARENT.ID
CurrentProject.Connection.Execute strSQL

Open in new window

0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24330315
There was a missing space and an extra space in a couple spots in the code above that i fixed but I still received the same error "Syntax error in FROM clause".

The ID field is a number field, but not autonumber so I used the one in the code snippet area.
Dim strSQL As String
strSQL = "INSERT INTO MRB_Data_Table (ID, Feature, Method, Type, ToolID, " & _
    "Failed, Measures, Notes, Criteria) SELECT Data-Table.ID, Data-Table.Feature, " & _
    "Data-Table.Method, Data-Table.Type, Data-Table.ToolID, " & _
    "Data-Table.Failed, Data-Table.Measures, Data-Table.Notes, " & _
    "Data-Table.ToolSpecs FROM Data-Table " & _
    "WHERE Data-Table.ID=" & Me.Parent.ID & ";"
    MsgBox Me.Parent.ID
CurrentProject.Connection.Execute strSQL

Open in new window

0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24330359
Please try this:
1. Cahnge the code as shown at the bottom of the snippet.
2. Run the code again.
3. Copy the SQL statement from the Immediate window (Ctrl+G).
4. Create a new query and paste the SQL statement into its SQL view.
5. Run the query from the SQL view (click the ! icon).
6. Post any error messages it shows.
Dim strSQL As String
strSQL = "INSERT INTO MRB_Data_Table (ID, Feature, Method, Type, ToolID, " & _
    "Failed, Measures, Notes, Criteria) SELECT Data-Table.ID, Data-Table.Feature, " & _
    "Data-Table.Method, Data-Table.Type, Data-Table.ToolID, " & _
    "Data-Table.Failed, Data-Table.Measures, Data-Table.Notes, " & _
    "Data-Table.ToolSpecs FROM Data-Table " & _
    "WHERE Data-Table.ID=" & Me.Parent.ID & ";"
    MsgBox Me.Parent.ID
Debug.Print strSQL
'CurrentProject.Connection.Execute strSQL

Open in new window

0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24335830
I created a sql query and used the code snippet below (I wasnt sure exactally what I was suppose to put in the SQL Query, is that right?)

Either way, I ran the query it errored with "syntax error in INSERT INTO statement" and the first word "Failed" was highlighted.
INSERT INTO MRB_Data_Table (ID, Feature, Method, Type, ToolID, " & _
    "Failed, Measures, Notes, Criteria) SELECT Data-Table.ID, Data-Table.Feature, " & _
    "Data-Table.Method, Data-Table.Type, Data-Table.ToolID, " & _
    "Data-Table.Failed, Data-Table.Measures, Data-Table.Notes, " & _
    "Data-Table.ToolSpecs FROM Data-Table " & _
    "WHERE Data-Table.ID=" & Me.Parent.ID & ";"

Open in new window

0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24336052
If you want to do it that way, copy the code snippet and paste it into the SQL view of your query. Change the ID shown (123456) to one that exists in your Data-Table table.
INSERT INTO MRB_Data_Table (ID, Feature, Method, Type, ToolID, Failed, Measures, Notes, Criteria) SELECT Data-Table.ID, Data-Table.Feature, Data-Table.Method, Data-Table.Type, Data-Table.ToolID, Data-Table.Failed, Data-Table.Measures, Data-Table.Notes, Data-Table.ToolSpecs FROM Data-Table 
WHERE Data-Table.ID=123456

Open in new window

0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24336220
I did that and it says "Syntax error in FROM clause." and it highlights the "-" between "Data-Table" right after the "WHERE" statement.
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24336339
Sorry, Data-Table should have been Data_Table. Please try this code.
INSERT INTO MRB_Data_Table (ID, Feature, Method, Type, ToolID, Failed, Measures, Notes, Criteria) SELECT Data_Table.ID, Data_Table.Feature, Data_Table.Method, Data_Table.Type, Data_Table.ToolID, Data_Table.Failed, Data_Table.Measures, Data_Table.Notes, Data_Table.ToolSpecs FROM Data_Table 
WHERE Data_Table.ID=123456

Open in new window

0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24336444
You were right the first time. The tables names are "Data-Table" and "MRB_Data-Table"

Is there an issue with using dashes (-) in SQL statements?
untitled.JPG
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24336512
Yes, there is. A dash is a special character and table names with special characters must be surounded by brackets as I have done in the updated SQL. And don't forget to change the ID shown (123456) to one that exists in your Data-Table table.
INSERT INTO MRB_Data_Table (ID, Feature, Method, Type, ToolID, Failed, Measures, Notes, Criteria) SELECT [Data-Table].ID, [Data-Table].Feature, [Data-Table].Method, [Data-Table].Type, [Data-Table].ToolID, [Data-Table].Failed, [Data-Table].Measures, [Data-Table].Notes, [Data-Table].ToolSpecs FROM [Data-Table] 
WHERE [Data-Table].ID=123456

Open in new window

0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24336577
Ok, I copied the two tables and made "datatable" and "mrbdatatable" and it looks like the statement copied the fields. So my problem is the dashes. Is there a way to work around this without having to rename the tables?
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24336655
Yes, just surround the table names with brackets [table-name].
0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24337559
OK, It's working now. I just have one little problem. The MRB_Data-Table has a field named "CPR" which is required for it to show up on the form. Can we add a line to the SQL query that will fill in the CPR with "4" ?
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24337736
Modify your SQL to add CPR to the list of fields in MRB_Data_Table and '4' AS CPR in the SELECT clause. This will insert a 4 into the CPR field.
INSERT INTO MRB_Data_Table (ID, Feature, Method, Type, ToolID, Failed, Measures, Notes, Criteria, CPR) SELECT [Data-Table].ID, [Data-Table].Feature, [Data-Table].Method, [Data-Table].Type, [Data-Table].ToolID, [Data-Table].Failed, [Data-Table].Measures, [Data-Table].Notes, [Data-Table].ToolSpecs, '4' AS CPR FROM [Data-Table] 
WHERE [Data-Table].ID=123456

Open in new window

0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24355212
It works from the SQL query window but when I try to add it to a button on the form it just gives me a compile error?
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24355220
Please post the code you have in the button's OnClick event.
0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24355332
Code below works fine in SQL Query but when put under the onclick event of a button on the form it says copile error.
INSERT INTO [MRB_Data-Table] ( ID, Feature, Method, Type, ToolID, Failed, Measures, Notes, Criteria, CPR )
SELECT [Data-Table].ID, [Data-Table].Feature, [Data-Table].Method, [Data-Table].Type, [Data-Table].ToolID, [Data-Table].Failed, [Data-Table].Measures, [Data-Table].Notes, [Data-Table].ToolSpecs, '4' AS CPR
FROM [Data-Table]
WHERE [Data-Table].ID= 18140;

Open in new window

0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24355374
You need to create a string of the SQL code and execute it like this:
CurrentProject.Connection.Execute "INSERT INTO [MRB_Data-Table] ( ID, Feature, Method, Type, " & _
    "ToolID, Failed, Measures, Notes, Criteria, CPR ) " & _
    "SELECT [Data-Table].ID, [Data-Table].Feature, [Data-Table].Method, [Data-Table].Type, " & _
    "[Data-Table].ToolID, [Data-Table].Failed, [Data-Table].Measures, [Data-Table].Notes, " & _
    "[Data-Table].ToolSpecs, '4' AS CPR " & _
    "FROM [Data-Table] " & _
    "WHERE [Data-Table].ID= 18140;"

Open in new window

0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24355603
That works. Now that its working I need to replace the 18140 with the ID (me.id). Is is possible to use me.id?
0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24355607
I bumped up the point value since this is taking longer than I thought it would.
0
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 1400 total points
ID: 24355613
Yes, it is possible. See the change in the code in the last line.
CurrentProject.Connection.Execute "INSERT INTO [MRB_Data-Table] ( ID, Feature, Method, Type, " & _
    "ToolID, Failed, Measures, Notes, Criteria, CPR ) " & _
    "SELECT [Data-Table].ID, [Data-Table].Feature, [Data-Table].Method, [Data-Table].Type, " & _
    "[Data-Table].ToolID, [Data-Table].Failed, [Data-Table].Measures, [Data-Table].Notes, " & _
    "[Data-Table].ToolSpecs, '4' AS CPR " & _
    "FROM [Data-Table] " & _
    "WHERE [Data-Table].ID=" & Me.ID & ;"

Open in new window

0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24355748
I changed the WHERE statement like you posted above but now it says "compile error - syntax error" when I run it.
Private Sub Command79_Click()
CurrentProject.Connection.Execute "INSERT INTO [MRB_Data-Table] ( ID, Feature, Method, Type, " & _
    "ToolID, Failed, Measures, Notes, Criteria, CPR ) " & _
    "SELECT [Data-Table].ID, [Data-Table].Feature, [Data-Table].Method, [Data-Table].Type, " & _
    "[Data-Table].ToolID, [Data-Table].Failed, [Data-Table].Measures, [Data-Table].Notes, " & _
    "[Data-Table].ToolSpecs, '4' AS CPR " & _
    "FROM [Data-Table] " & _
    "WHERE [Data-Table].ID=" & Me.ID & ;"
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24355767
This works
Private Sub Command79_Click()
CurrentProject.Connection.Execute "INSERT INTO [MRB_Data-Table] ( ID, Feature, Method, Type, " & _
    "ToolID, Failed, Measures, Notes, Criteria, CPR ) " & _
    "SELECT [Data-Table].ID, [Data-Table].Feature, [Data-Table].Method, [Data-Table].Type, " & _
    "[Data-Table].ToolID, [Data-Table].Failed, [Data-Table].Measures, [Data-Table].Notes, " & _
    "[Data-Table].ToolSpecs, '4' AS CPR " & _
    "FROM [Data-Table] " & _
    "WHERE [Data-Table].ID=" & Me.ID
End Sub

Open in new window

0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24355771
Sorry, I missed a quote before the semicolon (;). Please ttry this.
Private Sub Command79_Click()
CurrentProject.Connection.Execute "INSERT INTO [MRB_Data-Table] ( ID, Feature, Method, Type, " & _
    "ToolID, Failed, Measures, Notes, Criteria, CPR ) " & _
    "SELECT [Data-Table].ID, [Data-Table].Feature, [Data-Table].Method, [Data-Table].Type, " & _
    "[Data-Table].ToolID, [Data-Table].Failed, [Data-Table].Measures, [Data-Table].Notes, " & _
    "[Data-Table].ToolSpecs, '4' AS CPR " & _
    "FROM [Data-Table] " & _
    "WHERE [Data-Table].ID=" & Me.ID & ";"
End Sub

Open in new window

0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24355782
Either one will work.
0
 
LVL 1

Author Closing Comment

by:filtrationproducts
ID: 31579157
Only have to remove -->  & ;" from end of statement.

Thank you!!
0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 24355813
I have a couple things extra I would like to do but I will open a new question for them.

Thanks again!!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses
Course of the Month9 days, 20 hours left to enroll

762 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