Using For Each...In...Next to loop through recordset

[Brian]

I can't remember the correct object references to loop through a
table/recordset in VBA using the ForEach <Record>In <RecordSet>...Next Loop.

Of course, it is easy if there is an AutoNumber field, but I'm looking for
the correct references to do it on tables that do not have an AutoNumber
field.

 

[Allen Browne]

It's generally easiest to use a Do Loop so you can test for EOF:

Dim rs As DAO.Recordset
Set rs = dbEngine(0)(0).OpenRecordset("MyTable")
Do While Not rs.EOF
'useful stuff in here
rs.MoveNext
Loop
rs.Close

 

[Brian]

Thanks, Allen.

It's generally easiest to use a Do Loop so you can test for EOF:

Dim rs As DAO.Recordset
Set rs = dbEngine(0)(0).OpenRecordset("MyTable")
Do While Not rs.EOF
'useful stuff in here
rs.MoveNext
Loop
rs.Close

 

[DC]

I have a similar question. I have set up a continue form, which displays
records from the underlying table. Upon opening the form, I would like to
set a command button's enabled property to false, based on EACH RECORD's
value of true in the underlying table. I had thought I would use a "For each
Record in Recordset" loop, but am seriously struggling trying to write it.
The underlying table does have an Autonumber field, if that helps?

DC

 

[Brian]

Since buttons are attached to forms and not records, enabling a button for
one record enables it for all. You have a couple of options:

1. Put the button in the form's header instead of the detail section. In
Form_Current, use an If statement to enable/disable it:

If ABC then
Button1.Enabled = True
Else
Button1.Enabled = False
End if

2. Set the Enabled property of Button1 to "= [ABC]" where ABC is the name of
the control containing the True value
3. If you really must have the button in the detail section, you can
enable/disable it as you enter the record, but it will look very strange
because the button next to all the other records will be enabled/disabled
along with the current one. Instead, you might leave it enabled but put an
If...Then loop in the Button1_Click event:

Button1_Click()
If ABC Then
'stuff you want to do on button click goes here
Else
MsgBox "You can't do this right now."
End If

 

[David C. Holley]

If the command button is in the detail section of the form, use
CONDITIONAL FORMATING to control wether or not the button is enabled or
not. Right click on the button and select CF.