Solved

copying from one table to another in ms access 2007

Posted on 2013-02-06
6
414 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 84
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 65

Accepted Solution

by:
Jim Horn earned 500 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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 65

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 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

12 Experts available now in Live!

Get 1:1 Help Now