Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Create a table or append a table from a subform results

Posted on 2016-10-04
7
Medium Priority
?
101 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 52

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 52

Expert Comment

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

/gustav
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 52

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 52

Expert Comment

by:Gustav Brock
ID: 41833043
You are welcome!

/gustav
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

647 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