I am using the following code to co

[mikehoot]

I am using the following code to concantenate one field from all records from the recordset, spaced with a comma.

Dim db As Database
Dim rs As Recordset
Dim strName As String
Dim intCnt As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM qryDrugItemString&quot


intCnt = 0

Do Until rs.EOF
intCnt = intCnt + 1


If intCnt = 1 Then 'See if this is first rec
strName = rs!StringCont
Else 'If not first rec add a comma
strName = strName & ", " & rs!StringCont
End If

rs.MoveNext

Loop

Forms!frmDrugItemSUBFORM!StringCont = strName

End Sub


I would like to restrict these records by amemding the SELECT query for the recordset to include WHERE and AND parameters (taking values from current form) but I am not sure of the syntax to use.

This is what I would like (roughly!)

WHERE qryDrugItemSUBFORM.DrugMainID = Forms!FormName!ControlName AND qryDrugItemsSUBFORM.AdminRouteID = Forms!FormName!ControlName

If anyone can help out it will be much appreciated. B-)

 

[lameid]

Make you query as you would normally. Then you can either change the view to sql view and copy and paste that statement or simply substitute the queryname for the select statement in your openrecordset method.

 

[UncleJack]

Shouldn't your code line:

Forms!frmDrugItemSUBFORM!StringCont = strName

be:

Forms!frmDrugItemSUBFORM.Form!StringCont = strName

Uncle Jack