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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian - 03 Aug 2005 18:02 GMT

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.

Reply to this Message

Allen Browne - 03 Aug 2005 18:08 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I can't remember the correct object references to loop through a
> table/recordset in VBA using the ForEach <Record>In <RecordSet>...Next
[quoted text clipped - 3 lines]
> the correct references to do it on tables that do not have an AutoNumber
> field.

Reply to this Message

Brian - 03 Aug 2005 18:19 GMT

Thanks, Allen.

> It's generally easiest to use a Do Loop so you can test for EOF:
>
[quoted text clipped - 13 lines]
> > the correct references to do it on tables that do not have an AutoNumber
> > field.

Reply to this Message

DC - 16 Sep 2005 17:07 GMT

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

> It's generally easiest to use a Do Loop so you can test for EOF:
>
[quoted text clipped - 13 lines]
> > the correct references to do it on tables that do not have an AutoNumber
> > field.

Reply to this Message

Brian - 16 Sep 2005 17:27 GMT

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

> 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
[quoted text clipped - 22 lines]
> > > the correct references to do it on tables that do not have an AutoNumber
> > > field.

Reply to this Message

David C. Holley - 16 Sep 2005 23:10 GMT

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.

> 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
[quoted text clipped - 22 lines]
>>>the correct references to do it on tables that do not have an AutoNumber
>>>field.

Reply to this Message