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

    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
    Kerala, India
    You may try the following untested sample code with appropriate changes:

    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!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
       End If
    Set rstSub = Nothing
    Set rstOut = Nothing
    Set db = Nothing
    End Function (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
    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