Here's some code that solves all kinds of problems. Use it as a starting
point
for your own solutions.
Add a Command Button to your form from the toolbox. Call it cmdMassAdd.
Add a Reference to Microsoft DAO 3.6 (off the tools menu in the VB IDE)
In the properties view, create code for the OnClick event similar to the
following:
Private Sub cmdMassAdd_Click()
Dim Db As DAO.Recordset
Dim recP as DAO.Recordset
Set Db = CurrentDb()
Set recP = Db.OpenRecordst("tblProjectEmps ") 'An associative table
where assignments are recorded
Set qdf = CurrentEb.CreateQueryDef("") 'Creates an
unnamed/unsaved query
Dim recE As DAO.Recordset
Dim strSELECT as String 'A place to build your data field list
Dim strFROM as String 'A place to build specification of
Employee Table
Dim strWHERE as String 'A place to build selection criteria
Dim strSQL as String 'A place to build query statement
'Build data field list (Don't type the < or > characters)
strSELECT = "SELECT " 'note the
trailing space
strSELECT = strSELECT & "<fieldname-1>" 'note no spaces
strSELECT = strSELECT & ", <fieldname-2>" 'note comma,leading
space,no trailing space
strSELECT = strSELECT & ", <fieldname-n> " 'note trailing space
'Specify where the employees are to be found
strFROM = "FROM tblEmployee "
'Specify selection criteria
strWHERE = "Where tblEmployee.Status = 'Warm & Breathing' "
'Put it all together
strSQL = strSELECT & strFROM & strWHERE 'Make sure the resulting SQL
syntax is valid
' Let's get the data
qdf.SQL = strSQL 'Modify the queryDef
Set recE = qdf.OpenRecordset() 'Returns the result of the query
'Process the data
If NOT recE.BOF and NOT recE.EOF Then 'We got some records
DO
With recP
.AddNew
!fieldname-1 = RecS!fieldname-1
!fieldname-2 = RecS!fieldname-2
!fieldname-n = RecS!fieldname-n
.Update
End With
Loop Until recS.EOF = True
ELSE
msgbox "No records found"
End If
qdf.close
recP.close
End Sub