Daniel, Just looking at your response and are getting error messages, my code
is as below and im getting error 3061, too few parameters when the code
executes line Set rs = db.OpenRecordset("Alarms - Module Totals")
any ideas what im doing wrong??
Function GenStr() As String
On Error GoTo Error_Handler
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Alarms - Module Totals")
MsgBox "1"
If rs.RecordCount <> 0 Then
With rs
.MoveFirst
Do While Not .EOF
GenStr = GenStr & ![Module] & " " & ![CountOfModule] & ", "
.MoveNext
Loop
End With
'Remove trailing , if applicable
If Right(GenStr, 2) = ", " Then
GenStr = Left(GenStr, Len(GenStr) - 2)
End If
Else
'No records returned by your table or query
GenStr = ""
End If
Error_Handler_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function
Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & Err.Number & vbCrLf & "Error Source: GenStr" & vbCrLf &
"Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
Daniel Pineault said:
You could use a function such as:
Function GenStr()
On Error GoTo Error_Handler
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("YourQueryName")
If rs.RecordCount <> 0 Then
With rs
.MoveFirst
Do While Not .EOF
GenStr = GenStr & ![QueryFieldName1] & " " &
![QueryFieldName2] & ", "
.MoveNext
Loop
End With
'Remove trailing , if applicable
If Right(GenStr, 2) = ", " Then
GenStr = Left(GenStr, Len(GenStr) - 2)
End If
Else
'No records returned by your table or query
GenStr = ""
End If
Error_Handler_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function
Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: GenStr" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
--
Hope this helps,
Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples:
http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
StuJol said:
using access 2003 i have a qry that returns top 5 values
1 Item1 123
2 Item2 101
3 Item3 32
4 Item4 2
5 Item5 76
im trying to create a function that retuens a string like
"Item1 123, Item2 101, Item3 32" etc etc
I can create a function that returns the first row but not all 5 rows. can
anyone help please??
