?
Solved

Create a table or append a table from a subform results

Posted on 2016-10-04
7
Medium Priority
?
95 Views
Last Modified: 2016-10-06
I have a main form with a subform. On the main form i have combo boxes in which the end user selects their search criteria. I want to be able to have a button that appends a table with the subform results. Attached is a sample file . Click on sales and their you will see the combo boxes.

help is greatly appreciated
CW.accdb
0
Comment
Question by:Braulio cordova
[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
  • 4
  • 3
7 Comments
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41829477
Open the RecordsetClone of the subform as a DAO.Recordset, say, rsSelected.
Open the table you wish to copy to as a DAO.Recordset, say, rsCopy.

Now loop rsSelected, and for each record call rsCopy.AddNew
Assign values to the fields of rsCopy as needed.
Call rsCopy.Update

That's it.

/gustav
0
 

Author Comment

by:Braulio cordova
ID: 41830586
Thank you for the reply. I am not that good at coding so I was hoping you can pate the code and I can manipulate to fit my application. Help is greatly appreciated
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41830589
We don't even know which table to copy to.

/gustav
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Braulio cordova
ID: 41830691
Thanks for the quick reply. attached is an updated DB. I have created a local table called appendtable.

Thanks again for the support
CW.accdb
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 41831271
You can use this code:
Private Sub btnCopy_Click()

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

  strSQL = "SELECT TOP 1 * FROM appendtable"

  Set rstInsert = CurrentDb.OpenRecordset(strSQL)
  Set rstSource = Me!invoicedmonth_subform.Form.RecordsetClone
  With rstSource
    lngCount = .recordCount
    For lngLoop = 1 To lngCount
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              ' Copy field content.
              rstInsert.Fields(.Name).Value = .Value
            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

See the attached demo.

/gustav
CW--1-.accdb
0
 

Author Closing Comment

by:Braulio cordova
ID: 41832829
Gustav,

Thank you so much! This is exactly what I need. I am glad I joined Expert Exchange .You guys rock

Thanks again!
Braulio
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41833043
You are welcome!

/gustav
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

771 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