For Each ... Next statement confusion

[Zacharaih]

Access Newbie here. I'm trying to use the For Each ...
Next statement to cycle through records in a subform:

For Each Forms!frmNewSubmission.Child4.Form![Social
Security].Value In Forms!frmNewSubmission.Child4.Form
DO CODE
Next

I get an error:
Variable required. Can't assign to this expression.

Any ideas what I'm doing wrong in my references to

 

[Dan Artuso]

Hi,
You need to use the form's recordset.

Dim rs As Recordset
Set rs = Forms!frmNewSubmission.Child4.Form!Social Security

Do While Not rs.EOF
'code
Loop

Set rs = Nothing

 

[John Vinson]

Access Newbie here. I'm trying to use the For Each ...
Next statement to cycle through records in a subform:

Wrong tool. For Each is used in VBA code to loop through the members
of a Collection. A Recordset is not a VBA collection (at least not in
this way).

For Each Forms!frmNewSubmission.Child4.Form![Social
Security].Value In Forms!frmNewSubmission.Child4.Form
DO CODE
Next

Instead, open the Form's recordset clone as a Recordset and use the
MoveNext method:

Dim rs As DAO.Recordset
Set rs = Forms!frmNewsubmission!Child4.Form.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
DO CODE using rs!fieldname
rs.MoveNext
Loop
Set rs = Nothing

 

[Zachariah]

I get a runtime error '438': Object doesn't support this
property or method

The reference seems okay. Child4 is my subform and Social
Security is the field I'm checking in the subform.

Here's my current code:

'Verify that City/State/ZipCode exist
Dim rs As Recordset
Set rs = Forms!frmNewSubmission.Child4.Form![Social
Security].RecordsetClone

Do While Not rs.EOF
CODE
Loop

-----Original Message-----
Whoops!
Should be:
Set rs = Forms!frmNewSubmission.Child4.Form![Social Security].Recordsetclone

Assuming Social Security is the name of your
subform control.

--
HTH
Dan Artuso, Access MVP


"Dan Artuso" <[email protected]> wrote in

message news:#[email protected]...

Hi,
You need to use the form's recordset.

Dim rs As Recordset
Set rs = Forms!frmNewSubmission.Child4.Form!Social Security

Do While Not rs.EOF
'code
Loop

Set rs = Nothing

--
HTH
Dan Artuso, Access MVP

Access Newbie here. I'm trying to use the For Each ...
Next statement to cycle through records in a subform:

For Each Forms!frmNewSubmission.Child4.Form![Social
Security].Value In Forms!frmNewSubmission.Child4.Form
DO CODE
Next

I get an error:
Variable required. Can't assign to this expression.

Any ideas what I'm doing wrong in my references to

.

 

[Zachariah73]

That did the trick. Thanks guys.

-----Original Message-----

Access Newbie here. I'm trying to use the For Each ...
Next statement to cycle through records in a subform:

Wrong tool. For Each is used in VBA code to loop through the members
of a Collection. A Recordset is not a VBA collection (at least not in
this way).

For Each Forms!frmNewSubmission.Child4.Form![Social
Security].Value In Forms!frmNewSubmission.Child4.Form
DO CODE
Next

Instead, open the Form's recordset clone as a Recordset and use the
MoveNext method:

Dim rs As DAO.Recordset
Set rs = Forms!frmNewsubmission!Child4.Form.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
DO CODE using rs!fieldname
rs.MoveNext
Loop
Set rs = Nothing


.