Concatenate Function Module-HELP ~

[DBarker]

I am new at the VBA Any help would be appreciated. Below
is my code and this is the scenario: I have two tables
the first STi_Table has the records and no duplicates this
was created from a table with duplicates. The second
STi_Multiples contains duplicate records. What I am
trying to do is concatenate the Journals with the date of
those journals. I pull this data from a live database and
bring it into Access so that I can analyze the data.

Pleeeeeeeeeeze help

Also can I run this right from the module or create a
macro that runs the module?

*********************************************************
Sub Combine()
Dim strSQL As String
Dim dbs As DAO.Database
Dim rstFr As DAO.Recordset
Dim rstTo As DAO.Recordset
Dim varNoteBatch As Variant

Set dbs = CurrentDb()
strSQL = "SELECT STi_Table.* " _
& "FROM STi_Table " _
& "ORDER BY CaseID"
Set rstTo = dbs.OpenRecordset(strSQL, dbOpenDynaset)
strSQL = "SELECT STi_Multiples.* " _
& "FROM STi_Multiples " _
& "ORDER BY CaseID"
Set rstFr = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Do Until rstTo.EOF
varNoteBatch = Null
strSQL = "[CaseID]=" & rstTo!CaseID
rstFr.FindFirst strSQL
Do Until rstFr.NoMatch
varNoteBatch = varNoteBatch & ";" _
& rstFr!Journals
rstFr.FindNext strSQL
Loop
rstTo.Edit
rstTo!Journals = varNoteBatch
rstTo.Update
rstTo.MoveNext
Loop

rstFr.Close
rstTo.Close
Set rstFr = Nothing
Set rstTo = Nothing
End Sub

 

[Duane Hookom]

There is similar but generic code in a small sample database at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. There is
one function that can be used in a query or control source.