Solved

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

Posted on 2009-05-07
37
816 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 350 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

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.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 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

11 Experts available now in Live!

Get 1:1 Help Now