Not sure if I need an array

[mstromboli]

I am trying to produce an extract file of data from two tables in an Access database. What I am getting stumped on is when I have more than one value for a field. So far, in my meager attempts, I am getting multiple rows, sort of like the following (without column headings of course)

ID Associate
112 AAA
112 BBB
112 CCC
113 ZZZ
113 YYY

What I need to do is to have one line with all of the 'Associates' in it. I've thought that maybe my new file should have three columns for the maximum number of Associates, but have gotten stumped on how to do it. My knowledge of using Arrays is not good.

So, in the end, I am trying to have a file that looks like the following...

112 AAA BBB CCC
113 ZZZ YYY <blank>

BIG thanks for any help you can provide!!

 

[KenHigg]

Post #5 in the thread link below is the only way I have found to do this:

http://www.access-programmers.co.uk/forums/showthread.php?t=99777

 

[mstromboli]

Ken, this looks promising. I'm going to give it a try.

 

[pono1]

Another example attached.

Regards,
Tim

 

[mstromboli]

Pono1, when I try to use the file, I get a Visual Basic error. It can't open the Visual Basic Project within it. So, I don't see the example that you are trying to display.

 

[pono1]

OK, this time I didn't zip the file; instead I just added the zip extension to the name so the forum software would let me upload it. So before attempting to open the file make sure you download it to your PC and then rename it by removing the zip extension.

Regards,
Tim

 

[pono1]

Here's the code if that doesn't work...

Private Sub Command0_Click()

 ' Get the data.
 Dim datalist As String
 datalist = GetFunnyList("CustomerProducts")
 
 ' Save the data to a text file.
 WriteToFile "c:\funnylist.txt", Trim(datalist)
 
End Sub

Function GetFunnyList(myQuery As String) As String

  Dim rs As New ADODB.Recordset
  With rs
    .LockType = adLockReadOnly
    .CursorType = adOpenKeyset
    .Source = myQuery
    .ActiveConnection = CurrentProject.Connection
    .Open
  End With

  ' Get the first customer and product.
  Dim myList As String
  myList = rs(0).Value & " " & rs(1).Value & " "
  
  Dim customername As String
  customername = rs(0).Value
  
  ' Move forward a record.
  rs.MoveNext
  
 ' And now get all other customers and their products.
  Do While Not rs.EOF
    
    If customername = rs(0).Value Then
      myList = myList & rs(1).Value & " "
    Else
      myList = myList & vbCrLf & rs(0).Value & " " & rs(1).Value & " "
    End If
    
    ' Remember the last customer.
    customername = rs(0).Value
    ' Onward.
    rs.MoveNext
    
  Loop
  
  ' Destroy the reference.
  If rs.State = adStateOpen Then
    rs.Close
    Set rs = Nothing
  End If

  GetFunnyList = myList

End Function

Sub WriteToFile(path As String, text As String)


Dim i As Integer
i = FreeFile
Open path For Append As #i
Print #i, text
Close #i


End Sub

Regards,
Tim