Solved

Create a table or append a table from a subform results

Posted on 2016-10-04
7
82 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 50

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 50

Expert Comment

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

/gustav
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 41833043
You are welcome!

/gustav
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

749 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