Report Format - values appear horizontally

[skamran]

Table: (Sales_Site, Area_Codes)

I am trying to list area codes values associated to each sales site across a page (report). Access lists values vertically.

The code below fails to work properly. I don't recieve an error message, but, no data shows up on report. -

Option Compare Database
Option Explicit

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim strAreaCodes As String
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT AC_Area_Codes FROM Area_Codes_tbl WHERE AC_Sales_Site_ID = ' " & Me.AC_Sales_Site_ID & " ';", dbOpenSnapshot)

With rst

Do Until rst.EOF
strAreaCodes = strAreaCodes & !AC_Area_Codes & " "
.MoveNext


Loop
.Close

 

[padinka]

Here some code as an example that I use to get a list of numbers (swCaseId)separated by a comma. If the list is only one number, the comma is eliminated.


Function Attached_Cases(myTicketId As Long, myCase As Long) As Variant


Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
Dim fld As Field
Dim myLen As Long
Dim Counter As Integer




Set dbs = CurrentDb()

strSQL = "SELECT dbo_SW_CASE.swCaseId FROM dbo_SW_CASE INNER JOIN dbo_SW_TICKET ON dbo_SW_CASE.dsTicketId = dbo_SW_TICKET.swTicketId WHERE dbo_SW_TICKET.swTicketId = " & myTicketId & ""
Set rst = dbs.OpenRecordset(strSQL)

Set fld = rst!swCaseId


While (Not (rst.EOF))
For Counter = 0 To rst.EOF

Counter = Counter + 1
If rst!swCaseId = myCase Then
Attached_Cases = Attached_Cases
Else:
Attached_Cases = Attached_Cases & rst!swCaseId & ", "
End If
rst.MoveNext
Next

'Debug.Print Attached_Cases
Wend

myLen = Len(Attached_Cases) - 2


If myLen > 0 Then
Attached_Cases = Left(Attached_Cases, myLen)
End If

' Close the recordset and the database.

rst.Close
dbs.Close

End Function
Trisha
padinka@yahoo.com