• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

Create a table or append a table from a subform results

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
Braulio cordova
Asked:
Braulio cordova
  • 4
  • 3
1 Solution
 
Gustav BrockCIOCommented:
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
 
Braulio cordovaAuthor Commented:
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
 
Gustav BrockCIOCommented:
We don't even know which table to copy to.

/gustav
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Braulio cordovaAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
Braulio cordovaAuthor Commented:
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
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now