Ancalima
03-09-2009, 08:16 AM
Hi all,
I have an issue that has been bugging me for a few days now. I have a query that I set up to use a public function to get the data for it. The functions basically gets the Item's ID number and looks into a messages table for that ID, and for each listing in that messages table it combines them all into a single string for output into a single "Notes" field. Here is the code for the function:
Public Function GetItemNotes(Id As String) As String
Dim rs As DAO.Recordset
Dim strMessageString As String
strMessageString = ""
Set rs = CurrentDb.OpenRecordset("SELECT Message, MsgTime FROM ItemMessages WHERE Id=" & Id & " ORDER BY MsgTime")
Do Until rs.EOF
strMessageString = strMessageString & IIf(strMessageString <> "", " --- ", "") & Format(rs.Fields("MsgTime"), "mm/dd/yyyy") & " - " & rs.Fields("Message")
rs.MoveNext
Loop
GetItemNotes = strMessageString
End Function
Now this code causes the field to show up in the Access query perfectly fine. The problem I am having, however, comes when I try to export the query to an excel spreadsheet. Here is the code that creates the tab on the spreadsheet:
Public Sub exportItemDetail(xls As Excel.Application, strTabName As String, strFields As String, strSource As String, Optional strCriteria As String)
xls.Worksheets.Add
xls.ActiveSheet.Name = strTabName
xls.Range("a5").CopyFromRecordset CurrentDb.OpenRecordset("Select " & strFields & " from " & strSource & " " & strCriteria, dbOpenForwardOnly)
xls.Cells.Select
xls.Cells.EntireColumn.AutoFit
End Sub
No error messages occur, but when it performs the Copyfromrecordset, the field cuts off at 255 characters in the excel cell. I can't seem to find where the field is getting limited. I tried using a make-table query but that also cuts off at 255, and if i use an append query with the same table structure but change the "Notes" field to a memo I get an error saying it cannot append due to validation rules violations. Could anyone please help me with this issue? Thanks in advance for any help.
I have an issue that has been bugging me for a few days now. I have a query that I set up to use a public function to get the data for it. The functions basically gets the Item's ID number and looks into a messages table for that ID, and for each listing in that messages table it combines them all into a single string for output into a single "Notes" field. Here is the code for the function:
Public Function GetItemNotes(Id As String) As String
Dim rs As DAO.Recordset
Dim strMessageString As String
strMessageString = ""
Set rs = CurrentDb.OpenRecordset("SELECT Message, MsgTime FROM ItemMessages WHERE Id=" & Id & " ORDER BY MsgTime")
Do Until rs.EOF
strMessageString = strMessageString & IIf(strMessageString <> "", " --- ", "") & Format(rs.Fields("MsgTime"), "mm/dd/yyyy") & " - " & rs.Fields("Message")
rs.MoveNext
Loop
GetItemNotes = strMessageString
End Function
Now this code causes the field to show up in the Access query perfectly fine. The problem I am having, however, comes when I try to export the query to an excel spreadsheet. Here is the code that creates the tab on the spreadsheet:
Public Sub exportItemDetail(xls As Excel.Application, strTabName As String, strFields As String, strSource As String, Optional strCriteria As String)
xls.Worksheets.Add
xls.ActiveSheet.Name = strTabName
xls.Range("a5").CopyFromRecordset CurrentDb.OpenRecordset("Select " & strFields & " from " & strSource & " " & strCriteria, dbOpenForwardOnly)
xls.Cells.Select
xls.Cells.EntireColumn.AutoFit
End Sub
No error messages occur, but when it performs the Copyfromrecordset, the field cuts off at 255 characters in the excel cell. I can't seem to find where the field is getting limited. I tried using a make-table query but that also cuts off at 255, and if i use an append query with the same table structure but change the "Notes" field to a memo I get an error saying it cannot append due to validation rules violations. Could anyone please help me with this issue? Thanks in advance for any help.