Results 1 to 3 of 3
  1. #1
    Join Date
    May 2013
    Posts
    8

    Unanswered: Select multiple records in subform to create records

    I need to create some new records based on main form data and a selection of records from a sub form. The main form and sub form have different sources. I wanted to show the source fields in the sub form along with a check box to allow the users to select individual records. The record source for the sub form contains >1000 records, so the user will first enter data in the main form, use filters to find the records he wants to 'assign' to the main form data, click those he selects, then click a command button in the main form to create the record(s) based on the main form data and the selected records from the sub form. The new records will be appended to a new table. Help is appreciated, especially if you have examples. TIA.

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    You may try the following untested sample code with appropriate changes:

    Code:
    Private Function cmdButton_Click()
    Dim db As Database, rstSub As Recordset, rstOut As Recordset
    Dim frmMain As Form, frmSub As Form, bolSelected As Boolean
    Dim mainField1 As String, mainField2 As String, mainField3 As String
    
    
    Set frmMain = Me
    Set frmSub = Me.SubFormName.Form
    
    Set rstSub = frmSub.RecordsetClone
    Set rstOut = db.OpenRecordset("myOutputTable")
    
    mainField1 = frmMain![txtField1]
    mainField2 = frmMain![txtField2]
    mainField3 = frmMain![txtField3]
    
    Do While Not rstSub.EOF
       bolSelected = rstSub![fldSelect]
       If bolSelected Then
           rstOut.AddNew
           rstOut!m_Field1 = mainField1
           rstOut!m_Field2 = mainField2
           rstOut!m_Field3 = mainField3
           
           rstOut!s_field1 = rstSub!field1
           rstOut!s_field2 = rstSub!Field2
           rstOut!s_field3 = rstSub!field3
           rstOut!s_field4 = rstSub!field4
           
           rstOut.Update
       End If
       rstSub.MoveNext
    Loop
    rstSub.Close
    rstOut.Close
    
    Set rstSub = Nothing
    Set rstOut = Nothing
    Set db = Nothing
    
    End Function
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    May 2013
    Posts
    8
    Thanks. I did something very similar. I first added a select field to my tasks table which was the basis for a sub-form. In the form I picked up additional data and created the new records from that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •