Solved Reuse a query to string function (1 Viewer)

swingline

Member
Local time
Today, 04:07
Joined
Feb 18, 2020
Messages
51
I Use the free Microsoft Bug tracking template in my office. They use the following function to get comments for any bug and push them out to an unbound text box on a bug detail form. I would like to reuse this code for almost identical reasons. Below is their relations ships

Capture1.PNG


Code:
Option Compare Database

' Grabs all the comments for a given bug and pushes them into the details box on a given form
Public Function GetComments()

    On Error GoTo ErrorHandler

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim query As String
    Dim commentsStr As String
    commentsStr = ""
    
    ' Construct our parameterized query to select the relevent comments
    query = "SELECT * FROM Comments, Users WHERE Comments.User = Users.ID AND Comments.Bug = " & TempVars!CurrentID & " ORDER BY CTimestamp desc"
    
    ' Open the resulting record set for the query
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(query)

    '
    With rst
        ' While there exist records in the record set, iterate through them and construct our string
        Do While Not rst.EOF
            ' If this comment has been marked as a history entry, then display it as such
            If rst.Fields("History") = 1 Then
                commentsStr = commentsStr & "<font color=""#3F3F3F"" size=""2""><i>"
                commentsStr = commentsStr & rst.Fields("Comment") & " by "
                commentsStr = commentsStr & rst.Fields("Username") & " at "
                commentsStr = commentsStr & rst.Fields("CTimestamp") & ""
                commentsStr = commentsStr & "</i></font>"
                commentsStr = commentsStr & "<br /><br />"
            ' Otherwise simply construct our standard comment string
            Else
                commentsStr = commentsStr & "<strong>"
                commentsStr = commentsStr & rst.Fields("CTimestamp")
                commentsStr = commentsStr & "<font color=""#0F0F0F"" size=""2"">  by  "
                commentsStr = commentsStr & rst.Fields("Username")
                commentsStr = commentsStr & "</strong>"
                commentsStr = commentsStr & "<br />"
                commentsStr = commentsStr & rst.Fields("Comment")
                commentsStr = commentsStr & "<br /><br />"
            End If
            ' Go to the next record (comment) in the set
            rst.MoveNext
        Loop
    End With
    
    rst.Close

    ' Ensure that we properly display newlines
    commentsStr = Replace(commentsStr, vbCrLf, "<br />")
    
    ' Push the string into the comment box on the form (display it!)
    Forms![Bug Details]!CommentsBox.Value = commentsStr

ExitProcedure:
    Exit Function
    
ErrorHandler:
    Resume ExitProcedure

End Function

I not sure how to restructure the query to fit my needs. What I would like to do is Query comments by EquipSN and convert them to a string to be pushed to an unbound box.

Capture.PNG


My Attempt to convert it to my needs.
Code:
Option Compare Database

' Grabs all the comments for a given bug and pushes them into the details box on a given form
Public Function GetComments()

    On Error GoTo ErrorHandler

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim query As String
    Dim commentsStr As String
    commentsStr = ""
    
    ' Construct our parameterized query to select the relevent comments
    query = "SELECT * FROM StatusComments, Users WHERE StatusComments.User = Users.ID AND StatusComments.EquipSN = " & TempVars!CurrentID & " ORDER BY CTimestamp desc"
    
    ' Open the resulting record set for the query
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(query)

    '
    With rst
        ' While there exist records in the record set, iterate through them and construct our string
        Do While Not rst.EOF
            ' If this comment has been marked as a history entry, then display it as such
            If rst.Fields("History") = 1 Then
                commentsStr = commentsStr & "<font color=""#3F3F3F"" size=""2""><i>"
                commentsStr = commentsStr & rst.Fields("StatusComments") & " by "
                commentsStr = commentsStr & rst.Fields("User") & " at "
                commentsStr = commentsStr & rst.Fields("CTimeStamp") & ""
                commentsStr = commentsStr & "</i></font>"
                commentsStr = commentsStr & "<br /><br />"
            ' Otherwise simply construct our standard comment string
            Else
                commentsStr = commentsStr & "<strong>"
                commentsStr = commentsStr & rst.Fields("CTimestamp")
                commentsStr = commentsStr & "<font color=""#0F0F0F"" size=""2"">  by  "
                commentsStr = commentsStr & rst.Fields("User")
                commentsStr = commentsStr & "</strong>"
                commentsStr = commentsStr & "<br />"
                commentsStr = commentsStr & rst.Fields("StatusComments")
                commentsStr = commentsStr & "<br /><br />"
            End If
            ' Go to the next record (comment) in the set
            rst.MoveNext
        Loop
    End With
    
    rst.Close

    ' Ensure that we properly display newlines
    commentsStr = Replace(commentsStr, vbCrLf, "<br />")
    
    ' Push the string into the comment box on the form (display it!)
    Forms![EquipDetails]!CommentsBox.Value = commentsStr

ExitProcedure:
    Exit Function
    
ErrorHandler:
    Resume ExitProcedure

End Function
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:07
Joined
Oct 29, 2018
Messages
13,766
Hi. Can you show us a sample data and a sample output from that data? Thanks.
 

bastanu

Registered User.
Local time
Yesterday, 17:07
Joined
Apr 13, 2010
Messages
474
Check the table and field names, you have [User], not [Users] for table, UName not USername...
Cheers,
 

swingline

Member
Local time
Today, 04:07
Joined
Feb 18, 2020
Messages
51
After a bit of googling, I came up with this query. When I open Update form then run the query, I get the desired results.
SQL:
SELECT * FROM StatusComments WHERE EquipSN = Forms![Update]!EquipSN ORDER BY CTimeStamp desc

So I move on to changing the code get the comments to push to the CommentsBox on the Update form. On the Update form "On Load" I add RunCode and use GetComments(). Save and relaunch the form and nothing, no errors, and no comments in the comment box. Here is a screenshot of the expected result from the original file I am attempting to repurpose this

output.PNG


Code:
Option Compare Database

' Grabs all the comments for a given bug and pushes them into the details box on a given form
Public Function GetComments()

    On Error GoTo ErrorHandler

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim query As String
    Dim commentsStr As String
    commentsStr = ""
    
    ' Construct our parameterized query to select the relevent comments
    query = "SELECT * FROM StatusComments WHERE EquipSN = Forms![Update]!EquipSN ORDER BY CTimeStamp desc"
    
    ' Open the resulting record set for the query
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(query)

    '
    With rst
        ' While there exist records in the record set, iterate through them and construct our string
        Do While Not rst.EOF
            ' If this comment has been marked as a history entry, then display it as such
            If rst.Fields("History") = 1 Then
                commentsStr = commentsStr & "<font color=""#3F3F3F"" size=""2""><i>"
                commentsStr = commentsStr & rst.Fields("StatusComments") & " by "
                commentsStr = commentsStr & rst.Fields("User") & " at "
                commentsStr = commentsStr & rst.Fields("CTimeStamp") & ""
                commentsStr = commentsStr & "</i></font>"
                commentsStr = commentsStr & "<br /><br />"
            ' Otherwise simply construct our standard comment string
            Else
                commentsStr = commentsStr & "<strong>"
                commentsStr = commentsStr & rst.Fields("CTimeStamp")
                commentsStr = commentsStr & "<font color=""#0F0F0F"" size=""2"">  by  "
                commentsStr = commentsStr & rst.Fields("User")
                commentsStr = commentsStr & "</strong>"
                commentsStr = commentsStr & "<br />"
                commentsStr = commentsStr & rst.Fields("StatusComments")
                commentsStr = commentsStr & "<br /><br />"
            End If
            ' Go to the next record (comment) in the set
            rst.MoveNext
        Loop
    End With
    
    rst.Close

    ' Ensure that we properly display newlines
    commentsStr = Replace(commentsStr, vbCrLf, "<br />")
    
    ' Push the string into the comment box on the form (display it!)
    Forms![Update]!CommentsBox.Value = commentsStr

ExitProcedure:
    Exit Function
    
ErrorHandler:
    Resume ExitProcedure

End Function
 

Slap

Registered User.
Local time
Today, 01:07
Joined
May 21, 2011
Messages
30
query = "SELECT * FROM StatusComments WHERE EquipSN =" & Forms![Update]!EquipSN & " ORDER BY CTimeStamp desc"

You need to break the sql and allow vba to parse the actual value of the form box.
 

swingline

Member
Local time
Today, 04:07
Joined
Feb 18, 2020
Messages
51
Thanks for the reply, I tried your amended query but still face the same issue no errors and no string output to the CommentsBox.
 

Slap

Registered User.
Local time
Today, 01:07
Joined
May 21, 2011
Messages
30
So to troubleshoot I would do the following:

Insert line after:
query = "SELECT * FROM StatusComments WHERE EquipSN =" & Forms![Update]!EquipSN & " ORDER BY CTimeStamp desc"

New line:
Debug.print query

Insert breakpoint on line after debug.print and then step through the code using F8 in the VBA editor (CTRL+F11)

That should determine if the SQL is valid and if the Forms! reference actually has a value.

Good luck!
 

arnelgp

error reading drive A:
Local time
Today, 09:07
Joined
May 7, 2009
Messages
11,201
Did you try to call the function on the Current event of the form?
 

swingline

Member
Local time
Today, 04:07
Joined
Feb 18, 2020
Messages
51
Did you try to call the function on the Current event of the form?
The same result.

Using F8 to step through gets to "Set rst = dbs.OpenRecordset(query)" line then jumps down to "Resume ExitProcedure" line
 

arnelgp

error reading drive A:
Local time
Today, 09:07
Joined
May 7, 2009
Messages
11,201
change "query" to:

query = "SELECT * FROM StatusComments WHERE EquipSN = " & Forms![Update]!EquipSN & " ORDER BY CTimeStamp desc"
 

June7

AWF VIP
Local time
Yesterday, 16:07
Joined
Mar 9, 2014
Messages
3,379
Then use apostrophe delimiters.
query = "SELECT * FROM StatusComments WHERE EquipSN = '" & Forms![Update]!EquipSN & "' ORDER BY CTimeStamp desc"
 

swingline

Member
Local time
Today, 04:07
Joined
Feb 18, 2020
Messages
51
Then use apostrophe delimiters.
query = "SELECT * FROM StatusComments WHERE EquipSN = '" & Forms![Update]!EquipSN & "' ORDER BY CTimeStamp desc"
That did it. Thank you for your help.
 

Users who are viewing this thread

Top Bottom