Loop through all records in a table

[kwstriker299]

Hello All--

I am trying to loop through all the records in a table named: tbl_Scan_Index. I am using MS Access 2003. Here is my code(VBA):

Dim drawer As Integer
Dim Folder As Integer
Dim msg As String
Dim count As Integer

drawer = 11
Folder = 1

Dim DB As Database
Dim rst As DAO.Recordset

Set DB = CurrentDb()
Set rst = DB.OpenRecordset("tbl_Scan_Index")

rst.MoveLast
rst.MoveFirst
While Not rst.EOF
If Me.Folder_Num.Value = Folder Then
        count = count + 1
End If
Wend

msg = MsgBox(count, vbOKOnly)

My goal is to loop through all of the records, and count the number of records where Folder_Num (field in table) is equal to 1. I get an overflow error, and the counter goes way over the number of records that are in the table. Any ideas as to why? I am very new to DOA, and access programming. Thanks in advance.

[blad3runn69]

maybe try a DCount function like
intX = DCount("[Folder_Num]", "tbl_Scan_Index ", "[Folder_Num] = 'Folder'")

welcome 2 bytes :)

[blad3runn69]

maybe try a DCount function like
intX = DCount("[Folder_Num]", "tbl_Scan_Index ", "[Folder_Num] = 'Folder'")

welcome 2 bytes :)

sorry

intX = DCount("[Folder_Num]", "tbl_Scan_Index ", "[Folder_Num] = 1")

[MikeTheBike]

Hi

I agree with blade3runner69 regarding that DCount() is the method of choice (or perhaps "SELECT Count([Folder_Num]) As Count FROM tbl_Scan_Index WHERE [Folder_Num] = 1" for very large tables), I think, for the record, the error in the code should be noted/corrected ie.


Dim drawer As Integer
Dim Folder As Integer
Dim msg As String
Dim count As Integer

drawer = 11
Folder = 1

Dim DB As Database
Dim rst As DAO.Recordset
 
Set DB = CurrentDb()
Set rst = DB.OpenRecordset("tbl_Scan_Index")
 
rst.MoveLast
rst.MoveFirst
While Not rst.EOF
    If rst("Folder_Num") = Folder Then
        count = count + 1
    End If
    rst.MoveNext
Wend
 
msg = MsgBox(count, vbOKOnly)

??

MTB

[kwstriker299]

Thanks guys!!!

Your suggestions worked wonderfully!!! Its good to know there is a place where I can come if I have questions. Your help is much apprecited :)

[kwstriker299]

Another question,

If i use the DCount() methode like this:

DCount("[Folder_Num]", "tbl_Scan_Index ", "[Folder_Num] = 1")

It works fine, and returns the correct result. Is there any way that it can be used with a variable (folder) instead of hard coding [Folder_Num] = 1. I would like to be able to increment the folder.

Thanks in advance!!!

[pdebaets]

Try:

DCount("[Folder_Num]", "tbl_Scan_Index ", "[Folder_Num] = " & folder)