Loop though datasheet rows

[gjruppert]

Hi,
I want to loop through the elements of a datasheet but I can't seem to get this to work. I assumed it would behave like an array but I've put my (i) in all the obvious places and I still error out.

i = 0
Do Until i = Forms!Allocations!AllocationTypesSubform.Properties.Count - 1
MsgBox (Forms!Allocations!AllocationTypesSubform(i)! Code.Value)
i = i + 1
Loop

Any help would be appreciated.

 

[blarson0]

this isn't quite how you loop through forms. Use this to go through each record:

DoCmd.GoToRecord , , acNext

so you're code would look like this:

Do Until *******
MsgBox (Forms!Allocations!AllocationTypesSubform! Code.Value)
DoCmd.GoToRecord , , acNext
Loop

The problem is the until catch... you can just use an error handler, or you can get the number of records in the table, or I'm sure that there is a way to test if you are at the last record, but it is much easier (and faster) to use ADO:

Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Table Name&quot

Do Until rst.EOF
MsgBox rst!fieldname
rst.MoveNext
Loop

HTH!

-Brad

 

[gjruppert]

Thanks but I have a new problem. I get an Object Not Found on the set mySql :

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim mySql As String

'ERROR on NEXT LINE
Set mySql = "SELECT Allocation_Type_ID FROM AllocationTypes WHERE SALES_TYPE = '" & Forms!Allocations!SALES_TYPE.Value & "'"

MsgBox mySql

Set db = CurrentDb

Set rst = db.OpenRecordset(mySql)

Do Until rst.EOF
MsgBox rst!Allocation_Type_ID
rst.MoveNext
Loop

 

[gjruppert]

Hey, never mind. I took off the "Set" an it works
great!

Thanks!

Gerard