Problem looping through table

[Ammarhm]

Hi Guys
I have 2 tables that i need to process with MS Access
The first table (PatDB) has, among others, 3 columns called Dx1ID, Dx2ID and Dx3ID
Those columns contain numerical data from 1 to 30
I have another column (Statistic) where i want to do some statistics about the occurens of each number in the fields Dx1D, Dx2ID sand Dx3ID all together
The Statistic table contains hence 30 raws (1-30)
I wrote a function to scan the contents of (PatDB) and update the contecnt of (Statistic) based on that


The code loops through all 30 items in (Statistic) table
However it is not working, can anyone tell me why please?


Option Compare Database
Private Sub Report_Open(Cancel As Integer)
Dim AntDx As Integer
Dim A As Integer
A = 1
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Statistic")

With rs
Do Until .EOF


AntDx = DCount("[Dx1ID]", "[PatDB]", "[Dx1ID]=" & A) + DCount("[Dx2ID]", "[PatDB]", "[Dx2ID]=" & A) + DCount("[Dx3ID]", "[PatDB]", "[Dx3ID]=" & A)

CurrentDb.Execute ("Update [Statistic] Set [Number]=" & AntDx & " Where [DxID]=" & A)

A = A + 1
.MoveNext

Loop
.Close

End Sub

The Dcount and the Update functions work pretty well but something halts the whole function, looping error??

 

[dkinley]

With rs
Do Until .EOF


.MoveFirst
...

-dK