Subform - button to add to all

[David]

I have a subform that is used to assign people to
projects. It is linked to my main form in the standard
manner for subforms.
I now need the ability to assign all people to a project
when the project is things live 'vacation' or 'time
recording' etc. It would be too laborious to assign to
each person individually.

 

[Jim Shaw]

How about using VB to build a dynamic queryDef to select the people to be
added, then loop through the recordset and add them to the project? Put
this code behind a "MassAdd" command button.

-Jim

 

[Guest]

Jim,

How could I make such a button? I don't know enough about queryDef yet.

-ME

 

[Jim Shaw]

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

 

[Jim Shaw]

Sorry, got an error in my sample code of the previous message;

The Loop statement should reference recE not recS.

Sorry
-Jim