Can anyone help me with this. I followed the link as suggested by John Big Booty which lead me to finding a function for concatenating field values (i am using an access 2003 frontend linked to SQL Server 2000 backend): The function is:
Public Function ConcatRelated(strField As String, _
strTable As String, _
Optional strWhere As String, _
Optional strOrderBy As String, _
Optional strSeparator = ", ") As Variant
On Error GoTo Err_Handler
Dim rs As DAO.Recordset 'Related records
Dim rsMV As DAO.Recordset 'Multi-valued field recordset
Dim strSql As String 'SQL statement
Dim strOut As String 'Output string to concatenate to.
Dim lngLen As Long 'Length of string.
Dim bIsMultiValue As Boolean 'Flag if strField is a multi-valued field.
'Initialize to Null
ConcatRelated = Null
'Build SQL string, and get the records.
strSql = "SELECT " & strField & " FROM " & strTable
If strWhere <> vbNullString Then
strSql = strSql & " WHERE " & strWhere
End If
If strOrderBy <> vbNullString Then
strSql = strSql & " ORDER BY " & strOrderBy
End If
Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
'Determine if the requested field is multi-valued (Type is above 100.)
bIsMultiValue = (rs(0).Type > 100)
'Loop through the matching records
Do While Not rs.EOF
If bIsMultiValue Then
'For multi-valued field, loop through the values
Set rsMV = rs(0).Value
Do While Not rsMV.EOF
If Not IsNull(rsMV(0)) Then
strOut = strOut & rsMV(0) & strSeparator
End If
rsMV.MoveNext
Loop
Set rsMV = Nothing
ElseIf Not IsNull(rs(0)) Then
strOut = strOut & rs(0) & strSeparator
End If
rs.MoveNext
Loop
rs.Close
'Return the string without the trailing separator.
lngLen = Len(strOut) - Len(strSeparator)
If lngLen > 0 Then
ConcatRelated = Left(strOut, lngLen)
End If
Exit_Handler:
'Clean up
Set rsMV = Nothing
Set rs = Nothing
Exit Function
Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
Resume Exit_Handler
End Function
I have tried calling the function from a textbox control source on my form, using the following syntax:
=ConcatRelated("Suburb","aaqryTEST","LoadLineID = " & [LoadLineID])
where aaqryTEST has the following SQL:
SELECT tblLocal.LoadLineID, tblLocal.Suburb, tblLocal.JDNo, tblLocal.DeliveryDate
FROM tblLocal
WHERE (((tblLocal.JDNo)=[Forms]![TruckNo1]![lbl1]) AND ((tblLocal.DeliveryDate)=[Forms]![frmLoadAllocationScreen]![cboAllocationDate]));
When i run the query i keep getting the following error:
ERROR 3061: Too few parameters. Expected 2
I understand that the function is not passing the values of lbl1 & cboAllocationDate from my forms to aaqryTEST, can anyone help me in the right direction with this?
