How to copy data from one table to another from button click

Looking for the best way to 'copy' data from one table to another. So here's the situation... I have four tabs on a form each tab page has a subform. Each subform has identical fields linking to different tables with identical structure. Often times users will check the appropriate 'checkboxes'  on the subform for quarter 1. Then when they enter quarter 2 i would like to offer the ability to 'copy' over the data checked in quarter one so they can simply 'add' the checks on the new quarter without having to re-check all the previously check boxes in Quarter one if status remains the same which it normally does. I'd like to offer them a button to click to 'clone' the data check in quarter 1. What's the best way to do this?
shogun5Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
The best way in my oppinion is to build and execute a SQL string, in VBA. So e.g. in a click event (aircode):

Private sub cmd_copy_quarter_click()
  Currentdb.Execute "INSERT INTO YourTable( QuarterID, SomeValue) " & _
                                   " SELECT (20182, SomeValue) " & _
                                   " FROM YourTable " & _
                                   " WHERE QuarterID=20181",dbFailOnError

End sub

Open in new window


The sql above inserts quarter 1 2018 into quarter 2 2018 (I use 20181 as the ID for quarter 1)
0
Gustav BrockCIOCommented:
Use the RecordsetClone of the subforms - very fast, and the target will update automatically:

Public Sub CopyRecords()

  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
  Dim lngLoop     As Long
  Dim lngCount    As Long

  Set rstInsert = Me.Parent.NameOfTargetSubformCONTROL.Form.RecordsetClone  ' adjust to your name of the subform control.
  Set rstSource = Me.Recordset.Clone
  With rstSource
    lngCount = .RecordCount
    For lngLoop = 1 To lngCount
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.

              ' Adjust other exceptions. An example:
              ElseIf .Name = "PROCESSED_IND" Then
                rstInsert.Fields(.Name).Value = Null

              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
      End With
      .MoveNext
    Next
    rstInsert.Close
    .Close
  End With

  Set rstInsert = Nothing
  Set rstSource = Nothing

End Sub

Open in new window

0
Dale FyeCommented:
But your question points out a flaw in your data structure.

"Each subform has identical fields linking to different tables with identical structure"

You should never have two or more tables in your database with identical structure!

There is no need to have 4 tables when a single field with an additional field (it sounds like that field would be Qtr or something like that.  Then the subforms would use the same table and only modify the recordsource with a criteria:

WHERE [Qty] = 1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

shogun5Author Commented:
Dale...as always the voice of reason. You are right of course as i was being lazy and created a table for each quarter but I am having a difficult time seeing the relationship between student and the four quarters.  So in a nutshell, each students has various standards that have to be met each quarter. Some quarters these standards overlap. The easily explanation would be Kindergarten.  So each student has say 100 high frequency words they must master by the end of the year. I have a table of all High Frequency words with boolean value. On the form the control is a toggle button that is pressed if the student recognizes the word. Same words for each quarter. So for example little Suzy in Quarter 1 gets five of the HFW correct and the teacher toggles those five works and the "on" color changes so its real easy to see what words were recognized. In Quarter two the student may recognize the same words (or not) and some more.

Each tab on the student form has the same words for each quarter. So I was initially creating four separate table with identical words and linking the student ID to the table. But I do see that a better solution would be to query the table where Quarter  = (quarter value 1 to 4) I just don't exactly how I would set it up. I tried a junction table with student id and quarter id but was getting a duplicate value index error.  so  I only need four quarters and each quarter will have a list of about 100 of the same words. Hope this makes sense. I am just not seeing how to relate these tables where I can keep the values separate from quarter to quarter.

Ideas?

Then of course if a teacher starts quarter 2 and knows the student has master quarter 1 words then the teacher can press the 'duplicate' button and copy over the 'checked' values from quarter 1. This was the purpose of this post.

Thanks! Any assistance is most appreciated. Have always learned much here.
0
Gustav BrockCIOCommented:
query the table where Quarter  = (quarter value 1 to 4) I just don't exactly how I would set it up.

Adjust the four queries - one for each subform - to filter on [Quarter] = 1 in the first, and 2 to 3 for the next respectively.

In the subforms, set the DefaultValue of field Quarter to "1" to "4" respectively.
0
shogun5Author Commented:
Thank you to all who assistance in this question. Combine efforst. I simplified the database and filter on Qtrs. yay!
0
Gustav BrockCIOCommented:
You are welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.