loop through rows on a continuos form

[mcnewsxp]

how do you loop throuhg the rows on a continuos form?
ala:

If TypeOf ctlCntrl Is SubForm Then
strForm = ctlCntrl.Name
For Each ctlSubCntrl In
Forms(strCurrentForm).Form(strForm).Controls
'''need to loop through here i suppose
If TypeOf ctlSubCntrl Is TextBox Or TypeOf ctlSubCntrl Is
ComboBox Then
Debug.Print ctlSubCntrl.Value
End If
Next ctlSubCntrl
End If

 

[Marshall Barton]

how do you loop throuhg the rows on a continuos form?
ala:

If TypeOf ctlCntrl Is SubForm Then
strForm = ctlCntrl.Name
For Each ctlSubCntrl In
Forms(strCurrentForm).Form(strForm).Controls
'''need to loop through here i suppose
If TypeOf ctlSubCntrl Is TextBox Or TypeOf ctlSubCntrl Is
ComboBox Then
Debug.Print ctlSubCntrl.Value
End If
Next ctlSubCntrl
End If

That code makes no attempt to loop through records, it's
only trying to loop through the controls in a subform's
current record.

While that code is archaic, I think Access will still
process it as intended.

If you really want to loop through the records in a form
(subform?), how you do it depends on what you are trying to
accomplish.

 

[mcnewsxp]

That code makes no attempt to loop through records, it's
only trying to loop through the controls in a subform's
current record.

right.
i need to go from there and loop through the records.

While that code is archaic, I think Access will still
process it as intended.

sure it works.
is there a better way?

If you really want to loop through the records in a form
(subform?), how you do it depends on what you are trying to
accomplish.

i need to loop through the values on the continuous subform's records to
compare them to what is already in another existing table.

 

[Guest]

Use the RecordsetClone property of the subform to loop through the records of
the subform.

Enjoy. j.

 

[Marshall Barton]

right.
i need to go from there and loop through the records.


sure it works.
is there a better way?


i need to loop through the values on the continuous subform's records to
compare them to what is already in another existing table.

OK, it sounds like you are not trying to scroll the records,
so Jen is right about using the subform's RecordsetClone.

If the code is in the main form's module, then I think this
aircode should give you the general idea of how to loop
through thesubform's records and their fields:

If ctlCntrl.ControlType = acSubForm Then
With ctlCntrl.Form.RecordstClone
If .RecordCount > 0 Then .MoveFirst
Look at each record in the form's dataset
Do Until .EOF
Look at each field in the record
For Each fld In ctlCntrl.Form.Fields
Debug.Print fld.Name, fld.Value
Next fld
.MoveNext
Loop
End With
End If

Be sure to check Help for anything in the above that you are
not familiar with.

 

[mcnewsxp]

That code makes no attempt to loop through records, it's

OK, it sounds like you are not trying to scroll the records,
so Jen is right about using the subform's RecordsetClone.

If the code is in the main form's module, then I think this
aircode should give you the general idea of how to loop
through thesubform's records and their fields:

If ctlCntrl.ControlType = acSubForm Then
With ctlCntrl.Form.RecordstClone
If .RecordCount > 0 Then .MoveFirst
Look at each record in the form's dataset
Do Until .EOF
Look at each field in the record
For Each fld In ctlCntrl.Form.Fields
Debug.Print fld.Name, fld.Value
Next fld
.MoveNext
Loop
End With
End If

Be sure to check Help for anything in the above that you are
not familiar with.

thanks, i will try that on monday.
that is kind of the direction i was heading, but couldn't figure out syntax
for recordsetclone with all the abstraction.

 

[mcnewsxp]

this line is giving me an "Application-defined or object-defined error"

ctlCntrl.Form does give me the correct subform name, but doesn't let me use
it as i need to.

 

[Marshall Barton]

this line is giving me an "Application-defined or object-defined error"

ctlCntrl.Form does give me the correct subform name, but doesn't let me use
it as i need to.

You rely too much on my typing skills. It should be:

With ctlCntrl.Form.RecordsetClone

I noticed another mistake in my posted code. Change the For
statemet to:

For Each fld In .Fields

Note that the For is looping through the **fields** in the
subform's recordset, not through the controls on the
subform.

 

[mcnewsxp]

With ctlCntrl.Form.RecordstClone

You rely too much on my typing skills. It should be:

With ctlCntrl.Form.RecordsetClone

I noticed another mistake in my posted code. Change the For
statemet to:

For Each fld In .Fields

Note that the For is looping through the **fields** in the
subform's recordset, not through the controls on the
subform.

gosh that's kind of embarrassing - on my part.
i should have caught that.
thanks.

 

[Marshall Barton]

gosh that's kind of embarrassing - on my part.
i should have caught that.

Yeah, well, my typing skills are knd of embarrasing :-(

More important, did you get the results you want?

 

[mcnews]

More important, did you get the results you want?

i think i can get there from here.
one more question, tho.
can i get the name of the source table or query for the recordsetclone?

 

[Marshall Barton]

i think i can get there from here.
one more question, tho.
can i get the name of the source table or query for the recordsetclone?

Depends. The form's RecordSource property contains the
table/query name or an SQL statement.

If the form is based on a table or query, then use that. If
it's an SQL statement, you'll have to decide how you want to
use it, expecially if the FROM clause contains one or more
JOINs.

Why is this important?

 

[mcnewsxp]

Depends. The form's RecordSource property contains the
table/query name or an SQL statement.

If the form is based on a table or query, then use that. If
it's an SQL statement, you'll have to decide how you want to
use it, expecially if the FROM clause contains one or more
JOINs.

Why is this important?

this app allows users to do double entry to very that everything was read
from a paper form and entered properly. the first pass enters the data to
the real tables. the second pass holds the data in a temp table and does a
validation routine when the form is closed or the next form is open. if the
user is in double entry mode i program the subforms to use the temp table in
the form load method. i use a slicker method for the main forms, but
haven't adopted that for the subforms yet so i need to find out
progratically the name of the table rather than simply hard coding.

 

[mcnewsxp]

recordsource will due nicely.
thanks.