Concatenating Problem

[mohammadagul]

hello friend
i got the following code from a download on this site. basically what is does it concatenates the field "Medication" with itesef if the Clinet id is Same.
the only problem is that it concatenates all records except the last record.
Please help me with this
Option Compare Database


Public Sub s_runMe()
Dim cn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset

Set cn = CurrentProject.Connection
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset


rs1.Open "query1", cn, adOpenDynamic, adLockOptimistic
rs2.Open "tempMedications", cn, adOpenDynamic, adLockOptimistic

rs1.MoveFirst
rs2.AddNew
rs2![client id] = rs1![client id]
rs2![med description] = rs1![med description]
rs1.MoveNext


Do While Not rs1.EOF
If rs1![client id] = rs2![client id] Then
rs2![med description] = rs2![med description] & ", " & rs1![med description]
Else
rs2.AddNew
rs2![client id] = rs1![client id]
rs2![med description] = rs1![med description]
End If

rs1.MoveNext

Loop

rs1.Close

MsgBox "Done (KenHigg is so smart!)"

End Sub

 

[mohammadagul]

Any help willbe appretited..
thankyou

 

[WayneRyan]

mohammadagul,

See your other post, you shouldn't need a temporary table. You
should be able to accomplish this with a function call.

The "<--" below show where your missing data is.

Public Sub s_runMe()
Dim cn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset

Set cn = CurrentProject.Connection
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset

rs1.Open "query1", cn, adOpenDynamic, adLockOptimistic
rs2.Open "tempMedications", cn, adOpenDynamic, adLockOptimistic

rs1.MoveFirst
rs2.AddNew
rs2![client id] = rs1![client id]
rs2![med description] = rs1![med description]
rs1.MoveNext

Do While Not rs1.EOF
   If rs1![client id] = rs2![client id] Then
      rs2![med description] = rs2![med description] & ", " & rs1![med description]
   Else
      rst.Update <-- Technically, you should have this one too!
      rs2.AddNew
      rs2![client id] = rs1![client id]
      rs2![med description] = rs1![med description]
   End If
   rs1.MoveNext
   Loop
rs2.Update <-- That's why you missed the last one!
rs1.Close

MsgBox "Done (KenHigg is so smart!)"

End Sub

Wayne

 

[mohammadagul]

thanks a lot Wayne...
it worked well

now a simple question

basically the rs2 is updated in the loop , why it is needed to add the
rs2.update at the last..

please explain..

Thankyou


Muhammad Atif Gul

 

[WayneRyan]

Muhammad,

The basic flow is:

rs.AddNew
rs!SomeField "Something"
rs!SomeOtherField = "SomethingElse"
rs.Update

Every time that the name changes you want to do a rs.Update to
save the changes for that name.

However, you ALSO have to do a .Update when it is rs.EOF! Otherwise,
as you have seen, the LAST one won't be saved.

Wayne