Missing a record at end of module run

[susanmgarrett]

I'm using a module to associate like records into a single text string.

My problem is that when I run this, one record (the last record) is always missing.

What can I change in this module to ensure that all of the records process correctly?

Thanks!

____________________
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 "category4createtemp", cn, adOpenDynamic, adLockOptimistic
rs2.Open "TempCategory", cn, adOpenDynamic, adLockOptimistic

rs1.MoveFirst
rs2.AddNew
rs2![fkTrack] = rs1![fkTrack]
rs2![Category] = rs1![Category]
rs1.MoveNext


Do While Not rs1.EOF
If rs1![fkTrack] = rs2![fkTrack] Then
rs2![Category] = rs2![Category] & ";" & rs1![Category]
Else
rs2.AddNew
rs2![fkTrack] = rs1![fkTrack]
rs2![Category] = rs1![Category]
End If

rs1.MoveNext

Loop

rs1.Close

MsgBox "Module Category Done"

End Sub

 

[wazz]

this might not solve it but could help with some ideas; hopefully you haven't tried all of these yet. on a quick read, i think you have to switch your 'Do While' line with the MoveNext before it. you obviously know to check for EOF, but you moved before checking. i think you should also be checking at the very beginning (instead/also). i think i see why you have the Move before the loop. perhaps play with Loop While Not rs.EOF. hth.

 

[WayneRyan]

Susan,

I think you forgot the .Update

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 "category4createtemp", cn, adOpenDynamic, adLockOptimistic
rs2.Open "TempCategory", cn, adOpenDynamic, adLockOptimistic

rs1.MoveFirst
rs2.AddNew
rs2![fkTrack] = rs1![fkTrack]
rs2![Category] = rs1![Category]
[B]rs2.Update[/B]

rs1.MoveNext

Do While Not rs1.EOF
   If rs1![fkTrack] = rs2![fkTrack] Then
      [B]rs2.Edit[/B]
      rs2![Category] = rs2![Category] & ";" & rs1![Category]
      [B]rs2.Update[/B]
   Else
      rs2.AddNew
      rs2![fkTrack] = rs1![fkTrack]
      rs2![Category] = rs1![Category]
      [B]rs2.Update[/B]
   End If

   rs1.MoveNext
   Loop

rs1.Close

MsgBox "Module Category Done"

End Sub

Wayne

 

[wazz]

this was really bugging me so i worked on it a bit.

- first, wayne is right about the edit/update so add those.
- oddly, the stored memory of which record you're on in rs2 is lost after updating and gives a "no current record" error the next time you compare rs1 to rs2. it seems to go to a new record or something (?). so, after every "rs2.Update" add: rs2.Bookmark = rs2.LastModified.
- still, the loop wasn't working. change the "do while not..." to: Do Until rs1.EOF.