Solved

Create a table or append a table from a subform results

Posted on 2016-10-04
7
77 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
  • 4
  • 3
7 Comments
 
LVL 49

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 49

Expert Comment

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

/gustav
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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 49

Accepted Solution

by:
Gustav Brock earned 500 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 49

Expert Comment

by:Gustav Brock
ID: 41833043
You are welcome!

/gustav
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

770 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