Concatenate Memo Fields

  • Thread starter Thread starter DBarker
  • Start date Start date
D

DBarker

I had originally posted this question in the Queries
section because I was looking at working with an append
query but it has seem to have gotten much more
complicated. This is the situation. I have one table
that contains duplicate case IDs and different Notes
fields associated with that case ID. Then I have my main
table where I want to insert the comments concatenate
them from the first table based on the case ID. I am
having an issue with the syntax. Below is what I have so
far but it is not working.

Public Sub Combine()

Dim dbs As Database
Dim FromTbl As TableDef
Dim ToTbl As TableDef
Dim FromFld As Variant
Dim ToFld As Variant
Dim Multiple_Journals_Table1 As TableDef
Dim STi_Table As TableDef


Set dbs = CurrentDb
Set FromTbl = Multiple_Journals_Table1
Set ToTbl = STi_Table
Set FromFld = Multiple_Journals_Table1.Fields
Set ToFld = STi_Table.Fields

If FromTbl.caseID = ToTbl.case# Then
ToFld = ToFld & JFrom
Else
.MoveNext

If EOF Then
End
End If
End If


End Sub
 
Here is a better way to accomplish what you're after.
You'll need to set a reference to the 'Microsoft DAO 3.6
Object Library' for this code to work. From your code
window go to Tools / References and check the
corresponding box. Good luck.

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 Multiple_Journals_Table1.* " _
& "FROM Multiple_Journals_Table1 " _
& "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!MultiNoteFieldname
rstFr.FindNext strSQL
Loop
rstTo!MainNoteFieldname = varNoteBatch
rstTo.MoveNext
Loop

rstFr.Close
rstTo.Close
Set rstFr = Nothing
Set rstTo = Nothing
End Sub
 
I am getting closer. I pasted your code and understand
somewhat of what it is doing. I take it that since it
does it in order that is why you can use the variable
strSQL twice? Now I get the error "Update or
CancelUpdate without AddNew or Edit" and it points to

rstTo!Journals = varNoteBatch

Any ideas? I really appreciate your help.
-----Original Message-----
Here is a better way to accomplish what you're after.
You'll need to set a reference to the 'Microsoft DAO 3.6
Object Library' for this code to work. From your code
window go to Tools / References and check the
corresponding box. Good luck.

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 Multiple_Journals_Table1.* " _
& "FROM Multiple_Journals_Table1 " _
& "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!MultiNoteFieldname
rstFr.FindNext strSQL
Loop
rstTo!MainNoteFieldname = varNoteBatch
rstTo.MoveNext
Loop

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

-----Original Message-----
I had originally posted this question in the Queries
section because I was looking at working with an append
query but it has seem to have gotten much more
complicated. This is the situation. I have one table
that contains duplicate case IDs and different Notes
fields associated with that case ID. Then I have my main
table where I want to insert the comments concatenate
them from the first table based on the case ID. I am
having an issue with the syntax. Below is what I have so
far but it is not working.

Public Sub Combine()

Dim dbs As Database
Dim FromTbl As TableDef
Dim ToTbl As TableDef
Dim FromFld As Variant
Dim ToFld As Variant
Dim Multiple_Journals_Table1 As TableDef
Dim STi_Table As TableDef


Set dbs = CurrentDb
Set FromTbl = Multiple_Journals_Table1
Set ToTbl = STi_Table
Set FromFld = Multiple_Journals_Table1.Fields
Set ToFld = STi_Table.Fields

If FromTbl.caseID = ToTbl.case# Then
ToFld = ToFld & JFrom
Else
.MoveNext

If EOF Then
End
End If
End If


End Sub
.
Click to expand...
.
Click to expand...
 
Doh! I forgot that... oops. You'll need to add a line of
code before and after the line that's giving you the error.

rstTo.Edit
rstTo!Journals = varNoteBatch
rstTo.Update


-----Original Message-----
I am getting closer. I pasted your code and understand
somewhat of what it is doing. I take it that since it
does it in order that is why you can use the variable
strSQL twice? Now I get the error "Update or
CancelUpdate without AddNew or Edit" and it points to

rstTo!Journals = varNoteBatch

Any ideas? I really appreciate your help.
-----Original Message-----
Here is a better way to accomplish what you're after.
You'll need to set a reference to the 'Microsoft DAO 3.6
Object Library' for this code to work. From your code
window go to Tools / References and check the
corresponding box. Good luck.

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 Multiple_Journals_Table1.* " _
& "FROM Multiple_Journals_Table1 " _
& "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!MultiNoteFieldname
rstFr.FindNext strSQL
Loop
rstTo!MainNoteFieldname = varNoteBatch
rstTo.MoveNext
Loop

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

-----Original Message-----
I had originally posted this question in the Queries
section because I was looking at working with an append
query but it has seem to have gotten much more
complicated. This is the situation. I have one table
that contains duplicate case IDs and different Notes
fields associated with that case ID. Then I have my main
table where I want to insert the comments concatenate
them from the first table based on the case ID. I am
having an issue with the syntax. Below is what I have so
far but it is not working.

Public Sub Combine()

Dim dbs As Database
Dim FromTbl As TableDef
Dim ToTbl As TableDef
Dim FromFld As Variant
Dim ToFld As Variant
Dim Multiple_Journals_Table1 As TableDef
Dim STi_Table As TableDef


Set dbs = CurrentDb
Set FromTbl = Multiple_Journals_Table1
Set ToTbl = STi_Table
Set FromFld = Multiple_Journals_Table1.Fields
Set ToFld = STi_Table.Fields

If FromTbl.caseID = ToTbl.case# Then
ToFld = ToFld & JFrom
Else
.MoveNext

If EOF Then
End
End If
End If


End Sub
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
YOU ARE THE ABSOLUTE BOMB~ THANK YOU SO MUCH~ IT
WORKS~ I CAN NOT THANK YOU ENOUGH.

Debbie
-----Original Message-----
Doh! I forgot that... oops. You'll need to add a line of
code before and after the line that's giving you the error.

rstTo.Edit
rstTo!Journals = varNoteBatch
rstTo.Update


-----Original Message-----
I am getting closer. I pasted your code and understand
somewhat of what it is doing. I take it that since it
does it in order that is why you can use the variable
strSQL twice? Now I get the error "Update or
CancelUpdate without AddNew or Edit" and it points to

rstTo!Journals = varNoteBatch

Any ideas? I really appreciate your help.
-----Original Message-----
Here is a better way to accomplish what you're after.
You'll need to set a reference to the 'Microsoft DAO 3.6
Object Library' for this code to work. From your code
window go to Tools / References and check the
corresponding box. Good luck.

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 Multiple_Journals_Table1.* " _
& "FROM Multiple_Journals_Table1 " _
& "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!MultiNoteFieldname
rstFr.FindNext strSQL
Loop
rstTo!MainNoteFieldname = varNoteBatch
rstTo.MoveNext
Loop

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


-----Original Message-----
I had originally posted this question in the Queries
section because I was looking at working with an append
query but it has seem to have gotten much more
complicated. This is the situation. I have one table
that contains duplicate case IDs and different Notes
fields associated with that case ID. Then I have my main
table where I want to insert the comments concatenate
them from the first table based on the case ID. I am
having an issue with the syntax. Below is what I
Click to expand...
have
so
far but it is not working.

Public Sub Combine()

Dim dbs As Database
Dim FromTbl As TableDef
Dim ToTbl As TableDef
Dim FromFld As Variant
Dim ToFld As Variant
Dim Multiple_Journals_Table1 As TableDef
Dim STi_Table As TableDef


Set dbs = CurrentDb
Set FromTbl = Multiple_Journals_Table1
Set ToTbl = STi_Table
Set FromFld = Multiple_Journals_Table1.Fields
Set ToFld = STi_Table.Fields

If FromTbl.caseID = ToTbl.case# Then
ToFld = ToFld & JFrom
Else
.MoveNext

If EOF Then
End
End If
End If


End Sub
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top