I have a database that records samples received into a lab. This includes the sample type and the area/region it's come from:

Material Region
Sliced portions of brain Frontal
Sliced portions of brain Parietal
Sliced portions of brain Temporal
Sliced portions of brain Occipital
Sliced portions of brain Cerebellum
Sliced portions of brain Basal Ganglia
Sliced portions of brain Thalamus
Organ sample Pituitary


To enable the data to be exported to a Word template for reporting purposes, I have to string all the information together so it goes out as a paragraph and is placed using a Bookmark. However the code I'm currently using is giving me the data like this:

Sliced portions of brain, Frontal; Sliced portions of brain, Parietal; Sliced portions of brain, Temporal; Sliced portions of brain, Occipital; Sliced portions of brain, Cerebellum; Sliced portions of brain, Basal Ganglia; Sliced portions of brain, Thalamus; Organ Sample, Pituitary.

This is the code I'm using to get it like this is:

Dim strSQL1 As String
Dim Answer As String
Dim i As Integer
Dim rs As New ADODB.Recordset

On Error GoTo HandleErr

strSQL1 = "SELECT qryFrozenReceived.MatRecCase, qryFrozenReceived.MatOption, qryFrozenReceived.MaterialType, qryFrozenReceived.Area " _
& " FROM qryFrozenReceived " _
& " WHERE qryFrozenReceived.MatRecCase = " & CaseID & ""

rs.Open strSQL1, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rs.RecordCount >= 1 Then
rs.MoveFirst
Answer = rs!MaterialType & " - "
rs.MoveFirst
MatType = rs!MaterialType


For i = 1 To rs.RecordCount
Answer = Answer & (rs!Area) & ", "

rs.MoveNext
Next i

FrozenReceived = Left(Answer, Len(Answer) - 2)
End If

Ideally I want the data to show as:

Sliced portions of brain: Frontal, Parietal, Temporal, Occipital, Cerebellum, Basal Ganglia, Thalamus;
Organ Sample, Pituitary.

I know that I need to create two recordsets and open the first one, the MaterialType, and then loop through and add that Material Type and the associated areas to the string before moving on to the next material type but I just can't get it to work.

If you had any thoughts on the matter I'd love to hear them.