Merging Fields - some help with my code

[robjones23]

Hi all,

I'm trying to create a database to manage contacts coming in to us from another company. Their extract has a reference number and updates (kind of like a call logging system). Every time an update it placed into the system, it's put onto a new line so you see this:

Reference Update UpdateTime
00001 blah1 09:00
00001 blah2 10:00
00001 blah3 11:23
00002 blah1 10:45

etc.....

I've got some code which merges the update fields wherever the reference field matches, however I also need to have the update time before it. So it'd read:

Reference Update
00001 09:00 blah1 10:00 blah2 11:23 blah3

etc....

My current code takes all the updates and merges them however the time take is only the first of the times so it's currently looking like this:

Reference Update
00001 09:00 blah1 09:00 blah2 09:00 blah3

Can anyone help me?

My code looks like this:

Public Function mergeDesc(ID) As String
   Dim cnn As ADODB.Connection
   Dim cnn2 As ADODB.Connection
   Dim rst As New ADODB.Recordset
   Dim rst2 As New ADODB.Recordset
   Dim SQL As String
   Dim SQL2 As String
   Dim sDesc As String
   
   
   Set cnn = CurrentProject.Connection
   Set cnn2 = CurrentProject.Connection
   
   SQL = "Select [Note]" & _
         " from [TPP_Fix_Times_Calculated]" & _
         " where [ContactKey]='" & ID & "'"
   rst.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
   '----------------
   
      SQL2 = "Select [NoteCreated]" & _
         " from [TPP_Fix_Times_Calculated]" & _
         " where [ContactKey]='" & ID & "'"
  rst2.Open SQL2, cnn2, adOpenForwardOnly, adLockReadOnly
   
   '----------------
   Do While Not rst.EOF
      sDesc = sDesc & rst2![Notecreated] & rst![Note]
          rst.MoveNext
   Loop
      
   Set rst = Nothing
   Set cnn = Nothing
      
   mergeDesc = Mid(sDesc, 5)

End Function

Thanks in advance!!!

 

[Brian1960]

Moving in Recordsets

Not sure why your doing 2 recordsets but your missing RST2.Movenext

 

[robjones23]

Bloody hell how stupid of me!

Well spotted and thanks!!! Working fine now!

Rob.