MoveNext Problem

[tbohon]

Following is code for an On Click event in an Access 97 database.&nbsp;&nbsp;For some reason (probably obvious to someone not as close to this thing as I am!) it isn't advancing from record to record.&nbsp;&nbsp;The Do Loop repeats the correct number of times - but, as displayed by the MsgBox function, it never leaves record #1 in the database.<br><br>Any suggestions/ideas/fixes appreciated.<br><br>Here is the (actual) code:<br><br>Private Sub Command404_Click()<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim MyDb As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim MyTbl As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim MySums As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim admitdir As Integer, admiter As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;admitdir = 0<br>&nbsp;&nbsp;&nbsp;&nbsp;admiter = 0<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim myAdmitType As String<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set MyDb = DBEngine.Workspaces(0).Databases(0)<br>&nbsp;&nbsp;&nbsp;&nbsp;Set MyTbl = MyDb.OpenRecordset(&quot;tblAdmitDischarge&quot;, DB_OPEN_TABLE)<br>&nbsp;&nbsp;&nbsp;&nbsp;Set MySums = MyDb.OpenRecordset(&quot;tblSums&quot;, DB_OPEN_TABLE)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;If MySums.RecordCount &gt; 0 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MySums.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Do Until MySums.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MySums.Delete<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MySums.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;MyTbl.MoveLast<br>&nbsp;&nbsp;&nbsp;&nbsp;MyTbl.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Do Until MyTbl.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;myAdmitType = Trim$(AdmitType)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;Value of Encounter Nr is:&nbsp;&nbsp;&quot; & EncounterNr<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If myAdmitType = &quot;Direct&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;admitdir = admitdir + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ElseIf myAdmitType = &quot;Through ER&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;admiter = admiter + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MyTbl.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;'--------------- all after this will stay!!! ----------------<br>&nbsp;&nbsp;&nbsp;&nbsp;MySums.AddNew<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;MySums![SumAdmitTypeDirect] = admitdir<br>&nbsp;&nbsp;&nbsp;&nbsp;MySums![SumAdmitTypeER] = admiter<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;MySums.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;MyTbl.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;MySums.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;MyDb.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.OpenReport &quot;Report1&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br><br><br>Thanks in advance!!!<br><br>Tom

 

[jimmythegeek]

If I am reading it right, I think the following should work. Also, it is good practice to use an &quot;rs&quot; prefix for recordsets.<br><br>Let me know if it doesn't work.<br><br><br>Private Sub Command404_Click()<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rs As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim admitdir As Integer, admiter As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;admitdir = 0<br>&nbsp;&nbsp;&nbsp;&nbsp;admiter = 0<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDB()<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = db.OpenRecordset(&quot;tblAdmitDischarge&quot;, dbOpenSnapshot)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;rs.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;With rs<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Do Until .EOF&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If !AdmitType = &quot;Direct&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;admitdir = admitdir + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ElseIf !AdmitType = &quot;Through ER&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;admiter = admiter + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;End With<br>&nbsp;&nbsp;&nbsp;&nbsp;rs.Close<br><br>&nbsp;&nbsp;&nbsp;&nbsp;db.Execute &quot;DELETE * FROM tblSums&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = db.OpenRecordset(&quot;tblSums&quot;, dbOpenDynaset)&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;With rs<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.AddNew<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;![SumAdmitTypeDirect] = admitdir<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;![SumAdmitTypeER] = admiter<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;End With<br>&nbsp;&nbsp;&nbsp;&nbsp;rs.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;db.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.OpenReport &quot;Report1&quot;<br>End Sub <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development

 

[tbohon]

Jim:<br><br>Works like a charm!!!&nbsp;&nbsp;I really, REALLY appreciate it - this has been bugging me for a couple of days.&nbsp;&nbsp;Tomorrow - when I'm really awake (!) - I'm going to compare your code to what I was doing to see why mine didn't work.<br><br>However, with your fix, I can now finish the project and get the managers off of my back.<br><br>Thanks so much for the assist!<br><br>Best,<br><br>Tom