For...Next in subform

In the 'On open' event of a main form, I need to check the values of a
nested subform (i.e. subform of a subform). I am able to pass the
first value, and even though it goes through each time, it is not
passing the new value to the code..so if the first value is what I
want, great, but if it is in record 2 thru x, it acts as if it is not
there.

BTW, I am just having the code bring up a message box to test. Once
this works, I will be enabling or disabling other fields in the main
form.

Here's the code:

Private sub Form_Current()
Dim pr as variant
Dim rs as variant

pr = Forms!frmchart!subChartProc!frmChartProcsubform.Fo rm ("ProcID")
rs =
Forms!frmchart!subChartProc!frmChartProcsubform.fo rm.RecordsetClone

For each pr in rs
if Me.subchartproc.form!frmchartprocsubform("procID") = 20 then
msgbox "bingo", vbcritical, 'bingo"
exit for
End if
next
end sub

Like I said, I get no errors, but it only works if the first record in
the subform = 20. When I step through the code, it goes through the
loop for each record in the recordset, but doesn't update the ProcID

Thanks in advance for any input.

re: For...Next in subform

There could be a timing issue here.

Assuming the subform control has something in its
LinkMasterFields/LinkChildFields properties, Access will reload the subform
when the main form moves record. The form's Current fires when you move
record, so you may find that the subform has not loaded yet when this event
fires. The sub-subform will then have to reload after the subform does, so
it seems unlikely that your logic could work successfully. (If you are
actually using the Open event of the form as you stated, the situation would
be worse.)

You might be able to work around that if you performed a DLookup() or
DCount() directly on the sub-subform's table to examine the records that
will be loaded.

--
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.

"dataeagle" <mcook@cmitsolutions.com> wrote in message
news:1141689823.042194.72880@u72g2000cwu.googlegro ups.com...[color=blue]
> In the 'On open' event of a main form, I need to check the values of a
> nested subform (i.e. subform of a subform). I am able to pass the
> first value, and even though it goes through each time, it is not
> passing the new value to the code..so if the first value is what I
> want, great, but if it is in record 2 thru x, it acts as if it is not
> there.
>
> BTW, I am just having the code bring up a message box to test. Once
> this works, I will be enabling or disabling other fields in the main
> form.
>
> Here's the code:
>
> Private sub Form_Current()
> Dim pr as variant
> Dim rs as variant
>
> pr = Forms!frmchart!subChartProc!frmChartProcsubform.Fo rm ("ProcID")
> rs =
> Forms!frmchart!subChartProc!frmChartProcsubform.fo rm.RecordsetClone
>
> For each pr in rs
> if Me.subchartproc.form!frmchartprocsubform("procID") = 20 then
> msgbox "bingo", vbcritical, 'bingo"
> exit for
> End if
> next
> end sub
>
> Like I said, I get no errors, but it only works if the first record in
> the subform = 20. When I step through the code, it goes through the
> loop for each record in the recordset, but doesn't update the ProcID
>
> Thanks in advance for any input.
>[/color]

re: For...Next in subform

dataeagle wrote:[color=blue]
> In the 'On open' event of a main form, I need to check the values of a
> nested subform (i.e. subform of a subform). I am able to pass the
> first value, and even though it goes through each time, it is not
> passing the new value to the code..so if the first value is what I
> want, great, but if it is in record 2 thru x, it acts as if it is not
> there.
>
> BTW, I am just having the code bring up a message box to test. Once
> this works, I will be enabling or disabling other fields in the main
> form.
>
> Here's the code:
>
> Private sub Form_Current()
> Dim pr as variant
> Dim rs as variant[/color]

Variant? No, no.

Dim pr as Long '(I assume it's a Long Integer).
Dim rs as DAO.Recordset

(assuming your are using DAO, else 'Dim rs as ADODB.Recordset')
[color=blue]
> pr = Forms!frmchart!subChartProc!frmChartProcsubform.Fo rm ("ProcID")
> rs =
> Forms!frmchart!subChartProc!frmChartProcsubform.fo rm.RecordsetClone[/color]


If you are referencing this form more than once create a variable to store a
pointer to it.

Dim myForm as Form
Set myForm = Forms!frmchart!subChartProc!frmChartProcsubform.Fo rm
pr = myForm("ProcID")

SET rs = pr.RecordsetClone

(you need to use SET now we're actually using a recordset)
[color=blue]
> For each pr in rs[/color]

That doesn't seem correct to me.

Why not just loop through the recordset?

rs.Movefirst
Do While Not rs.EOF
If rs("ProcID") = 20 Then 'note reference the recordsetclone, not the
form!!
Msgbox "bingo"
Exit While
End If
rs.Movenext
Loop
[color=blue]
> if Me.subchartproc.form!frmchartprocsubform("procID") = 20 then
> msgbox "bingo", vbcritical, 'bingo"
> exit for
> End if
> next
> end sub
>
> Like I said, I get no errors, but it only works if the first record in
> the subform = 20. When I step through the code, it goes through the
> loop for each record in the recordset, but doesn't update the ProcID
>
> Thanks in advance for any input.[/color]

The main reson it only worked for one record is that you refrenced the form
which only has one current record that you can access (which is why you use
a copy of the recorset, ie. recordsetclone)
--
regards,

Br@dley

re: For...Next in subform

Thanks Bradley!
Your post was the ticket to getting this right. I did make the pr a
long and rs a DAO.recordset. However, in the end I did not need the pr
statement at all as I Set rs = myform.recordsetClone instead of just
the field. The Do While statement is something I should have thought
of and thank you for hitting me in the head with that...my wife is
usually the one who likes to do that to me.

Anyway, all is right with the world (for now), thanks again.