Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58

    Question Unanswered: Recordsets and Arrays

    Hey there,

    I felt that I was working on something pretty simple, but I just cannot get my head around it:

    I have a union query with a column of email addresses. I want to take the records in the query, join them in a string, and display them in a text box.

    Here is an example of the code I am employing...

    Sub Thatthing()

    Dim Foo, Bar
    Dim rst As Recordset

    Set rst = CurrentDb.OpenRecordset("UnionQuery")

    Foo = rstCanCC.RecordCount

    Array = rst.GetRows(Foo)

    Bar = Join (Array)

    End Sub

    Any help would be appreciated. Thanks.

    -Brian

  2. #2
    Join Date
    Nov 2002
    Posts
    150

    Re: Recordsets and Arrays

    Sorry, there are no JOIN or SPLIT functions in Access. Declare a string variable and roll through your recordset appending each record to it. Going to be slow because of VB/VBAs terrible string manipulation.

  3. #3
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58

    Re: Recordsets and Arrays

    I see. Well, I'll try attacking it from another angle. However, I did see the JOIN in my object browser, but then again I am a little new to the coding aspects of Access.

    Thanks.

  4. #4
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58

    Talking Re: Recordsets and Arrays

    I figured it out, and used a JOIN function to get me result. I just wanted to post my code in case others might find it useful:

    Private Sub Form_Load()

    Dim DryCCval, DryToval, Copack As String
    Dim rstDryCC, rstDryTo As Recordset
    Dim dbs As Database
    Dim DryCC, DryTo

    intX = DCount("[Mail]", "Drycc")
    intY = DCount("[Mail]", "Dryto")

    Set dbs = CurrentDb
    Set rstDryCC = dbs.OpenRecordset("Drycc")

    If Forms![Main Menu]![FS Flag].Value = True Then
    Set rstDryTo = dbs.OpenRecordset("DrytoFS")
    intY = DCount("[Mail]", "DrytoFS")

    ElseIf Forms![Main Menu]![FS Flag].Value = False Then
    Set rstDryTo = dbs.OpenRecordset("Dryto")
    intY = DCount("[Mail]", "Dryto")
    End If

    ReDim DryCC(intX)
    ReDim DryTo(intY)


    rstDryCC.MoveFirst
    For I = 0 To (intX - 1)
    DryCC(I) = rstDryCC![Mail]
    rstDryCC.MoveNext
    Next I

    rstDryTo.MoveFirst
    For I = 0 To (intY - 1)
    DryTo(I) = rstDryTo![Mail]
    rstDryTo.MoveNext
    Next I

    DryToval = (Join(DryTo))
    DryCCval = (Join(DryCC))

    Copack = Nz(Forms![Main Menu]![Copack].Value, "")

    If Copack <> "" Then
    Forms![Distribution Dry]![DryToval] = DryToval & Copack & ";"

    ElseIf Copack = "" Then
    Forms![Distribution Dry]![DryToval] = DryToval
    End If

    Forms![Distribution Dry]![DryCCval] = DryCCval

    End Sub

    My main problem with how I was doing it before was that I did not include a field name when pulling data from my recordset.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •