• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 827
  • Last Modified:

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

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
filtrationproducts
Asked:
filtrationproducts
  • 20
  • 17
1 Solution
 
Chuck WoodCommented:
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
 
filtrationproductsAuthor Commented:
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
 
Chuck WoodCommented:
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
filtrationproductsAuthor Commented:
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
 
Chuck WoodCommented:
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
 
filtrationproductsAuthor Commented:
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
 
Chuck WoodCommented:
Yes, sorry but I confused myself.
0
 
filtrationproductsAuthor Commented:
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
 
Chuck WoodCommented:
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
 
filtrationproductsAuthor Commented:
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
 
filtrationproductsAuthor Commented:
Oh, the ID is a primary key but it is not an autonumber. If that changes anything...
0
 
Chuck WoodCommented:
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
 
filtrationproductsAuthor Commented:
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
 
Chuck WoodCommented:
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
 
filtrationproductsAuthor Commented:
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
 
Chuck WoodCommented:
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
 
filtrationproductsAuthor Commented:
I did that and it says "Syntax error in FROM clause." and it highlights the "-" between "Data-Table" right after the "WHERE" statement.
0
 
Chuck WoodCommented:
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
 
filtrationproductsAuthor Commented:
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
 
Chuck WoodCommented:
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
 
filtrationproductsAuthor Commented:
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
 
Chuck WoodCommented:
Yes, just surround the table names with brackets [table-name].
0
 
filtrationproductsAuthor Commented:
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
 
Chuck WoodCommented:
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
 
filtrationproductsAuthor Commented:
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
 
Chuck WoodCommented:
Please post the code you have in the button's OnClick event.
0
 
filtrationproductsAuthor Commented:
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
 
Chuck WoodCommented:
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
 
filtrationproductsAuthor Commented:
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
 
filtrationproductsAuthor Commented:
I bumped up the point value since this is taking longer than I thought it would.
0
 
Chuck WoodCommented:
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
 
filtrationproductsAuthor Commented:
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
 
filtrationproductsAuthor Commented:
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
 
Chuck WoodCommented:
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
 
Chuck WoodCommented:
Either one will work.
0
 
filtrationproductsAuthor Commented:
Only have to remove -->  & ;" from end of statement.

Thank you!!
0
 
filtrationproductsAuthor Commented:
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 20
  • 17
Tackle projects and never again get stuck behind a technical roadblock.
Join Now