Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


inserting data form 1 table to another table using combo boxes

Posted on 2011-05-09
Medium Priority
Last Modified: 2012-05-11
hello i have attached example - a form with 2 combo boxes - i want to be able to insert data from table in left combo box to the table  in the combo box on the right-(selecting correct table in each combo box and clicking the command button) - and after data is exported and inserted - the data is deleted from table-
you will see i have some code - but it is getting errors
thank you
Question by:davetough
LVL 26

Accepted Solution

Nick67 earned 2000 total points
ID: 35726102

Let me make sure I understand what you would like.
If I select tbl1 and tbl6, you'd like the button to append all the records from tbl1 into tbl6, and then delete all the records from tbl1?

This code works.
Option Compare Database
Option Explicit

Private Sub Command5_Click()
Dim mySQL As String

If Nz(Forms!frmExportTables!cboTblTo, "") = "" Or Nz(Forms!frmExportTables!cboTblFrom, "") = "" Then 'not selected.
MsgBox "Select both tables first"

Exit Sub
End If
' i am getting error here - any ideas?
mySQL = "INSERT INTO " & Me.cboTblTo.Value
mySQL = mySQL & " SELECT " & Me.cboTblFrom & ".*"
mySQL = mySQL & " FROM " & Me.cboTblFrom
CurrentDb.Execute mySQL, dbFailOnError
MsgBox mySQL
mySQL = "Delete " & Me.cboTblFrom & ".* from " & Me.cboTblFrom
MsgBox mySQL
CurrentDb.Execute mySQL, dbFailOnError
End Sub

Open in new window

The biggest problem: lack of Option Explicit, and a missed quote mark
ALWAYS use option explicit
And I always build my SQL strings one clause at a time
Dim strstrsql

If Nz(Forms!frmExportTables!cboTblTo, "") = "" Or Nz(Forms!frmExportTables!cboTblFrom, "") = "" Then 'not selected.
MsgBox "Select both tables first"

Exit Sub
End If
' i am getting error here - any ideas?
strsql = "Insert into " & forms!frmExportTables!cboTblTo &  <----------- you missed quotes here ----------> Select * from " & forms!frmExportTables!cboTblFrom
CurrentDb.Execute strsql, dbFailOnError

strsql = "Delete * from " & Forms!frmExportTables!cboTblFrom
CurrentDb.Execute strsql, dbFailOnError

Author Closing Comment

ID: 35727345
thank you

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

564 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