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
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.
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 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.
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