Trying to convert field data to string

[bistec]

Trying to take the data out a field from several records and create a string using VB. e.g.

Description (field)
--------------------
Lounge
Chair
Television
etc

I want to get this output as a string variable

Lounge, Chair, Television, etc

I've been trying to get the following working but I keep getting a 3061 error on the recordset(strSQL) statement.

-------------------------------
Function GetAccessory(vUnitId As Integer) as String

'Purpose: Return the accessories installed on a unit.
'Arguments: vUnitID = the unit to report on.
'Return: List in String.
Dim db As Database 'CurrentDb()
Dim rsAccess As DAO.Recordset 'Various recordsets.
Dim strSQL As String 'SQL statement.
Dim accessStr As String 'List of Accessories.

If Not IsNull(vUnitId) Then
'Initialize: Validate and convert parameters.
Set db = CurrentDb()
accessStr = ""

'Get the accessories for unit.
strSQL = "SELECT tblUnitAccessories.Description FROM tblUnitAccessories " & _
"WHERE (((tblUnitAccessories.UnitID)=vUnitId));"

Set rsAccess = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rsAccess.RecordCount > 0 Then
rsAccess.MoveFirst
Do While Not Null
accessStr = accessStr & " " & rsAccess!Description
rsAccess.MoveNext
Loop
End If
rsAccess.Close

'Assign the return value
GetAccessory = accessStr
End If

Set rsAccess = Nothing
Set db = Nothing
Exit Function
End Function

---------------------------------------------

 

[ToeShot]

try changing this
"WHERE (((tblUnitAccessories.UnitID)=vUnitId));"

to this
"WHERE (((tblUnitAccessories.UnitID)= " & me.vUnitId & &quot);"

HTH
Dave
ToeShot@Hotmail.com
Today Is Tomorrows Yesterday. So Why Wait

 

[bistec]

Errr... No that didn't work.

The full error code is

Run-time Error '3061';

Too Few Parameters. Expected 1.

From what I can see I'm only passing 1
parameter to it.

Hmmmm....

 

[WildHare]

Assuming your SQL code does indeed return some records, try this:

Set rsAccess = db.OpenRecordset(strSQL)




There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[bistec]

I've tried that one too. Actually the dbOpenSnapshot was added out of desperation as was the setting of it as a DAO Recordset.

The SQL Statement works and returns 4 records (as it should)

 

[ToeShot]

Another consideration would be to make sure that you have the Microsoft DAO Object Library 3.X check and placed above the Microsoft ADO 2.X library Other wise you would need to use ADO instead of DAO Dave
ToeShot@Hotmail.com
Today Is Tomorrows Yesterday. So Why Wait

 

[bistec]

YEAH! fixed it.

I found this article

ACC2000: Errors Concatenating Variables or Controls (Q210244)

@Microsoft

and modified the following

strSQL = "SELECT tblUnitAccessories.Description FROM tblUnitAccessories WHERE ((tblUnitAccessories.UnitID)=" & vUnitId & ""

That fixed it!

Thanks All