Insert records for each record in different table

[scottb]

Hi everyone,
I have a form frmResources (record source tblResources). There is a subform on frmResources named subfrmInstructorFacilities (continuous, record source tblInstructorFacility). Form and subform are linked by ResourceID. tblInstructorFacility has three fields: InstructorFacilityID (PK), ResourceID, and FacilityID and identifies which facilities the resource can teach at. On the subform I have a combobox (cmbFacilityID, row source tblFacilities) with which the user selects the facilities at which a resource can teach.

I would like to add a command button to the subform that will create/insert records into tblInstructorFacility for the active resource for each facilityID found in tblFacilities. This would save a lot of time if possible rather than having to manually select each facility for each resource.
Thank you for any help. -Scott

[HansV]

Wouldn't it be better to place this button on the main form, since the records to be inserted all have the ResourceID of the main form?
You could use

Code: Select all

Private Sub cmdAddRecords_Click()
    Dim strSQL As String
    ' Check whether we have a ResourceID
    If IsNull(Me.ResourceID) Then
        MsgBox "Please create a new resource or move to an existing one!", vbExclamation
        Exit Sub
    End If
    ' Assemble SQL for append query
    strSQL = "INSERT INTO tblInstructorFacility (ResourceID, FacilityID) SELECT " & _
        Me.ResourceID & ", FacilityID FROM tblFacilities WHERE FacilityID Not In " & _
        "(SELECT FacilityID FROM tblInstructorFacility WHERE ResourceID = " & Me.ResourceID & ")"
    ' Execute the append query
    CurrentDb.Execute strSQL, dbFailOnError
    ' Requery the subform to show the new records
    Me.subfrmInstructorFacilities.Requery
End Sub

[scottb]

Hans,
Yes that would be a better idea. Works great and it will save a lot of time. Thank you very much for the help. - Scott