Creating a string from a query in vba

  • Thread starter Thread starter StuJol
  • Start date Start date
S

StuJol

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??
 
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.
 
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??
Click to expand...
Click to expand...
 
it appears to be giving errors as a result of opening a qry that is based on
another qry with criteria. is there any way round this??

StuJol said:
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??
Click to expand...
Click to expand...
Click to expand...
 
Back
Top