Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

copying from one table to another in ms access 2007

Posted on 2013-02-06
6
Medium Priority
?
424 Views
Last Modified: 2013-02-06
Hi.
If I have a database table with 20 records and want to copy a few records that have been checked off to second existing table (using a check field in the first table) how is this easily accomplished. I assume you have to do it in VBA as I am using access 2007.
e g
TABLE 1
record 1
record 2 (checked)
record 3
record 4
record 5 (checked)

TABLE 2
(contains 50 records)

simply take record 2 and 5 and copy them into TABLE 2. Then uncheck the two records.

I am not sure if I have to reopen the tables in VBA? I have a check field in TABLE 1
0
Comment
Question by:PawloA
6 Comments
 
LVL 85
ID: 38861115
You can do this:

Currentdb.Execute "INSERT INTO [Table 2] (Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM [Table 1] WHERE MyCheckBoxField=True"

Currentdb.Execute "UPDATE [Table 1] SET MyCheckBoxField=False"

Obviously you'd have to change the columns and field names to match your own.

You don't have to open the tables.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 38861125
You could make a select query containing the checked items from Table 1, and then make an append query to append all but the key field for these recods to Table 2.  Then an update query to set the Yes/No field in Table 1 to False (unchecking these records).  After testing these queries, you could run the append and update query from VBA code, using OpenQuery.
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 38861128
That will be two queries:  INSERT and UPDATE

INSERT INTO Table2 (column1, column2, the_check)
SELECT column1, column2, the_check
FROM Table1
WHERE Table1.the_check = True

... then ...

UPDATE Table1
SET the_check = False
WHERE the_check = True
0
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.

 
LVL 7

Expert Comment

by:Steve
ID: 38861132
You could do it with an append query. No vba needed.
0
 
LVL 1

Author Closing Comment

by:PawloA
ID: 38861635
actually it would be append instead of insert I believe
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38861650
Thanks for the grade, although I believe LSMConsulting had the same answer, and posted it before I did, so really he should be awarded at minimum half the points, probably more.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

810 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