Search code examples
ms-accessappendsubform

Appending Parent form Record based on Value in Subform with Multiple Records

I have an append query that runs on the on click event of a button on my parent form.

The query only appends the current record if a text field in the sub form meets a specific criteria. The query seems to operate as intended other than the criteria is only evaluated on the first sub form record.

How can I get the criteria to search all current sub form records and append the record if the criteria is met?

-Parent Form: Primary_frm -Sub Form: Review_sfrm -Field being evaluated: Review_sfrm.Desc -Table appending to: Editied_records_tbl

Query below (table and field names have been changed):

 INSERT INTO Edited_records_tbl
    SELECT Record_number 
    FROM Records_tbl
    WHERE ID =Forms!Primary_frm!Review_sfrm.Form.ID and Forms!Primary_frm!Review_sfrm.Form.Desc = "xxxx";

Thank you in advance

Solution

  • In Access, subform controls are generated for only one record at a time. This means you have to iterate over the subform to append all records.

    You can use the following code from the main form.

    Dim rs As DAO.Recordset
With Me.Review_sfrm.Form
   DoCmd.SetWarnings False
   Set rs = .RecordsetClone 'Get underlying subform records
   rs.MoveFirst 'Start at first record
   Do Until rs.EOF 'Until last record
       .Bookmark = rs.Bookmark 'Navigate to current record
       DoCmd.OpenQuery "MyQuery"
       rs.MoveNext 'Next record
    Loop
End With
DoCmd.SetWarnings True