Help with comma separated values in report

Help with comma separated values in report

• Wojtek

  Hi

  The below example uses a recordset to scroll through the records and add the entries into one string. Hope this helps

  Public Sub test()
  Dim rs As Recordset
  Dim MyStr As String
  Dim FirstRec As Boolean
  Dim Responce

  Set rs = CurrentDb.OpenRecordset("SELECT Table1.Field1 FROM Table1;")
  If rs.RecordCount > 0 Then
  MyStr = ""
  fisrtrec = True
  While Not rs.EOF
  If FirstRec Then
  MyStr = rs!field1
  Else
  MyStr = MyStr & ", " & rs!field1
  End If
  rs.MoveNext
  Wend
  End If
  rs.Close
  Set rs = Nothing
  Responce = MsgBox("My String = " & MyStr, vbOKOnly, " Example")
  End Sub
  

  
  

• Chandrakanttt

  Hi Kdee, did u find the solution, me too have a same problem, can u post here the final code that solved ur problem.

  Thanx

  shah

  
  
  

• nsam

  Where do I put this code Is it behind the report

  In which textbox could I display the result of this code. I changed the code with the real names of fields. So I expect it would work.

  Thanks

  
  
  

• AussieNoobie

  Hi!

  I tried following:

  On reports open event:

  Private Sub Report_Open(Cancel As Integer)
  '------------------------------------------------------------
  ' Dodaj_stevilke_faktur_na_report
  '
  '------------------------------------------------------------
  On Error GoTo Dodaj_stevilke_faktur_na_report_Err

  Dim rs As Recordset
  Dim MyStr As String
  Dim FirstRec As Boolean
  Dim Responce
  Dim okno As TextBox
  okno = [Report_Faktura-lot-osnovna].CMR_ji
  Set rs = CurrentDb.OpenRecordset("SELECT [CMR-ji za fakturo].[CMR] FROM [CMR-ji za fakturo];")
  If rs.RecordCount > 0 Then
  MyStr = ""
  FirstRec = True
  While Not rs.EOF
  If FirstRec Then
  MyStr = rs![CMR]
  Else
  MyStr = MyStr & ", " & rs![CMR]
  End If
  rs.MoveNext
  Wend
  End If
  rs.Close
  Set rs = Nothing
  Responce = MsgBox("My String = " & MyStr, vbOKOnly, " Example")

  okno.Text = MyStr

  Dodaj_stevilke_faktur_na_report_Exit:
  Exit Sub

  Dodaj_stevilke_faktur_na_report_Err:
  MsgBox Error$
  Resume Dodaj_stevilke_faktur_na_report_Exit

  End Sub

  The problem is that it gives me back an error (Object variable or With block variable not set)

  It seems that we are on the right track here but having some minor dificulties.

  
  
  

• #pragma

  Yes!

  It should be very simple - but I just can't find the way.

  in a query is following:

  Field1

  Value1

  Value2

  Value3

  Value4

  I want those values to be shown on a report like:

  Value1,Value2,Value3,Value4 etc...

  they could be inside one textbox or many it doesn't matter just so I can see the progress going on - because this is frustrating.

  Since I'm quite familiarized with common procedures in access and now I'm trying to get familiarized with coding I need help.

  Thanks for your reply.

  
  
  

• mkoren

  Hi Kdee

  Can you post an example of the data in the field and the output you are trying to achieve. I cannot quite visualise what you are trying to do

  regards

  ADG

  
  

• Joe G

  Try using Set i.e.

  Set okno = [Report_Faktura-lot-osnovna].CMR_ji
  

  
  

• Andres Aguiar

  You will also need to pass the recordset or SQL as a parameter
  

• sandyJ

  This works for me.

  Option Compare Database

  Private Sub Odpri_cmrjizafakturo_Click()

  Dim dbs As Database
  Dim rs As DAO.Recordset
  Dim fakture As Form
  Dim tfakture As String
  Dim strsql As String
  Dim firstrec As Boolean
  Set dbs = CurrentDb()
  Set fakture = [Form_Fakture]
  fakture.SetFocus
  tfakture = fakture.[ tevilkafakture].Value
  strsql = "SELECT [CMR] FROM [Prevozi-lot] WHERE [ tev fakture] = '" & tfakture & "'"
  Set rs = CurrentDb.OpenRecordset(strsql)
  If rs.RecordCount > 0 Then
  MyStr = ""
  fisrtrec = True
  While Not rs.EOF
  If firstrec Then
  MyStr = rs![CMR]
  Else
  MyStr = MyStr & ", " & rs![CMR]
  End If
  rs.MoveNext
  Wend
  End If
  MyStr = Right(MyStr, Len(MyStr) - 1)

  DoCmd.OpenForm ("cmrizafakturo")

  With [Form_CMRIzafakturo].okno
  .Value = MyStr
  End With

  rs.Close
  Set rs = Nothing
  Debug.Print MyStr
  DoCmd.OpenQuery "CMRJI_zafakturo"
  DoCmd.OpenReport "cmrji", acViewPreview
  DoCmd.OpenReport "Faktura-lot-osnovna", acViewPreview
  DoCmd.Close acReport, "cmrji"
  DoCmd.Close acForm, "cmrizafakturo"

  
  
  End Sub

  
  
  

• Davids Learning

  I'll do my best.

  If I don't succeede - then I'd apreciate your further help.

  Thanks for all the help so far..

  
  
  

• DenisCote

  I would put the code in a module and change it to a fuction that returns the string that is wanted, then you could call it from where required.
  

• neotom

  1. Have you check your SQL string Is there any typo in it Try to build a query to verify it.

  2. The textbox.text wouldn't work, try textbox.value instead.

  
  
  

• Lostlogic

  Nice try - wanna.....

  

  But maybe a step closer to the solution.

  I'm not receiving that error but instead another :

  Too few parameters. Expected 1

  
  
  

• ShaneShowers

  rs (recordset) has a value (rs = nothing) when i run throuhg line by line.

  I don't think VBA recognizes the filed [CMR-ji za fakturo].[CMR] or even the query [CMR-ji za fakturo] which are members of a database.

  It recognizes docmd.openquery "CMR-ji za fakturo" (it opens query)

  but not like this docmd.openquery [cmr-ji za fakturo]

  could it be the same with sql's select from How could I then refer to a field (column) inside the "CMR-ji za fakturo"

  SELECT "cmr-ji za fakturo".cmr from "cmr-ji za fakturo" doesn't work.

  Thanks in advance

  
  
  
• Help with comma separated values in report