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" wrote:
> 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" wrote:
>
> > 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??
Similar Threads
