Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2015
    Posts
    5

    Answered: Simple VBA macro to copy records from one table to another

    Hi!

    I am a newbie here. I have some experience with Excel VBA, but Access is totally different. Could you please help me with the following?

    I have two tables in my Access database, their fields are exactly the same. One is called Uncheched. The other one is called Checked.

    So what is need is a macro that takes selected records in table Unchecked and copies them to the end of table Checked.

    Thank you!

  2. Best Answer
    Posted by Sinndho

    "Use a query and have it run form a macro:

    1. Create a query, switch to SQL View, and paste the following code (this only works if the structures of both tables are identical):
    Code:
    INSERT INTO Tbl_CheckEd
    SELECT * FROM Tbl_UnChecked;
    2. Execute the query directly or create a macro to execute the query or, in VBA:
    Code:
    Public Sub ExecQuery()
    
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "QueryName"
        DoCmd.SetWarnings True
        
    End Sub
    Or:
    Code:
    Public Sub ExecQueryBis()
    
       CurrentDb.QueryDefs("Query10").Execute dbFailOnError
        
    End Sub
    Or (without Query object):
    Code:
    Public Sub ExecQueryTer()
    
        Const c_SQL As String = "INSERT INTO Tbl_Checked SELECT * FROM Tbl_UnChecked;"
    
        CurrentDb.Execute c_SQL, dbFailOnError
        
    End Sub
    "


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why do you see the need to move the data between tables?
    instead modify your table design and create a column (filed if you prefer), call it, say, verified (boolean or yes/no datatype)
    then when you have checked the data set the value of the verified column/field to true

    then when you need to see the data you have 3 choices
    show data that is:-
    checked (verified = true)
    unchecked (verified = false)
    either (verified not specified)
    ...that can be done by applying a filter on the form displaying the data
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Jun 2015
    Posts
    5
    Well, many people will have access to the Unchecked table. They will be entering records. There will be mistakes, some records would not be approved at all, etc. Only one person (me) will have access to the Checked table. I do not want other people who enter data into Unchecked table to be able to see or even worse - edit any records after they have been copied to the Checked table.

    In fact, I want to CUT selected records from Unchecked table and PASTE them to Checked table. So Unchecked table is most of the time empty or has just a few pending-to-be-checked records.

    Thank you!

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why?
    use the column to indicate if a row is checked or not. moving data around like this is just unnecessary*.
    then ONLY allow other users to see rows which aren't checked.

    its really very, very trivial. when opening the form set a filter such that if they are not you they cannot see rows that are checked. one of the mauin advanatges is future code maintenance. you only have one form, one table design. so if you have to add new columns int he future its a change to the tabel, and a change to the form, not to each rable, ech form, the query / macro process that does the copy, then delete

    it may be neccesary however if you are using a datasheet view, as opposed to a form. but then again Id suggest that ius a very very good rerason NOT to use datasheet views in the first place

    if your users are 'just' entering data then consider changing the forms style based on ujser id. you can make a form for data entry only (forget the property details but its 'allow data entry' or something similar)
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Jun 2015
    Posts
    5
    I use Datasheet view. I did not create any forms and it will probably not learn how to do it in a few hours until the deadline.

    Coming from Excel, it's really easy to copy from one table to another. I run a macro recorder and it gives me just 2-3 lines of VBA code. I thought Access would be similar, but alas... it's not.

    Thank you anyway for your help!

  7. #6
    Join Date
    Jun 2015
    Posts
    5
    Wrote this code:

    Sub CopyRecords()

    DoCmd.RunCommand acCmdCopy
    DoCmd.OpenTable "tblChecked", acNormal, acEdit
    DoCmd.RunCommand acCmdPaste

    End Sub

    Trying to add a button to a ribbon to run this macro, but for some reason can't see CopyRecords macro in the list. Any ideas how to do it?

    Thank you!

  8. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use a query and have it run form a macro:

    1. Create a query, switch to SQL View, and paste the following code (this only works if the structures of both tables are identical):
    Code:
    INSERT INTO Tbl_CheckEd
    SELECT * FROM Tbl_UnChecked;
    2. Execute the query directly or create a macro to execute the query or, in VBA:
    Code:
    Public Sub ExecQuery()
    
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "QueryName"
        DoCmd.SetWarnings True
        
    End Sub
    Or:
    Code:
    Public Sub ExecQueryBis()
    
       CurrentDb.QueryDefs("Query10").Execute dbFailOnError
        
    End Sub
    Or (without Query object):
    Code:
    Public Sub ExecQueryTer()
    
        Const c_SQL As String = "INSERT INTO Tbl_Checked SELECT * FROM Tbl_UnChecked;"
    
        CurrentDb.Execute c_SQL, dbFailOnError
        
    End Sub
    Have a nice day!

  9. #8
    Join Date
    Jun 2015
    Posts
    5
    Thank you very much, Sinndho!!!

  10. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •