Solved

copying from one table to another in ms access 2007

Posted on 2013-02-06
6
419 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Technology Partners: 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

Suggested Solutions

Title # Comments Views Activity
Pass Variables from Vba access to SQL Query 1 32
Opening DAO Recordset dbSnapshot vs dbForwardOnly 11 37
Copy all value in one form to another 4 42
Append Query Access 2010 4 26
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
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.

730 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