Looping through records in a subform 2

[ETID]

Hello,

I would like to loop through each record in a subform and return a single field as a string variable, so I can pass each string to a vba routine.

Thanks for any help.

 

[Darrylles]

Hi,

    Set rstMyTable = CurrentDb.OpenRecordset("mytable", dbOpenDynaset)
        
    With rsMyTable
      .movelast
      .movefirst

      do while true
          myvar = rsMyTable.myField
          (use myvar with whatever function)
          rsMyTable.movenext
      loop
    end with

    rstMyTable.close

Try the above,

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[ETID]

Thanks for the reply,...but no luck


...I am using a query to pull in the subform data

the main form source is the table that is the "many" and the table that is the source for the subform, (via the query), is the "one" in a "many to one" relationship.


did I confuse you yet?

 

[ETID]

Ok...it sort of works.

(The T was missing in rsmytable....)

Got that straightened out, but its looping the entire source table instead of just what is displayed in the sub form.


how do i tame that down?

 

[ETID]

Correction, it's looping through all records in the main form instead of just the current one being viewed.

 

[Darrylles]

Hi,

OK, a 1:many relationship is USUALLY 1 outer - many inner in a form (I've got to assume some things - you certainly don't tell us much (lol).

Apply the code to the subform, where 'mytable' is the table in the subform (I would have thought that that was obvious ET).

Place the code in the subform also.

The outer form records will of course follow suit and scroll as each inner record (which is the one (from 1:many)) steps through.

I'm a little confused now - hope this gets you there.

If not - please tell me exactly what you are doing on the form.

Kind regards,

Darrylle







"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[KenReay]

Hi

To scroll trough the records on the sub form, in the sub form:

Dim Rs as Recordset

Set Rs = Me.RecordsetClone
Do Until Rs.EOF
.do your stuff here
Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ETID]

Thanks Darrylles ...sorry I wasn't clearer

As you state above

"The outer form records will of course follow suit and scroll as each inner record (which is the one (from 1:many)) steps through."

....That is my problem.

My main form has various header information for products.
My sub form has tests associated with these products.
Based on the type of product declared in the main form,
the sub form will list the appropriate tests for that type of product,

What I want to do is only loop through the tests displayed in the subform for the product that is being displayed and pass each test name to a variable.

I hope this helps, and thanks again!

 

[ETID]

Anyone there?

 

[KenReay]

Hi

I repeat:

Hi

To scroll trough the records on the sub form, in the sub form:

Dim Rs as Recordset

Set Rs = Me.RecordsetClone
Do Until Rs.EOF
.do your stuff here
Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing

have you tried that? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ETID]

Hi KenReay

Thanks for your reply,

2 questions for you...

What ref. library do I need to use ? (it errors out on Set Rs = Me.RecordsetClone)

and

where you have ".do your stuff here" is part of what I need,
how do I capture every item displayed for a certain field within the subform? (1 at a time as it goes through the loop?)

 

[KenReay]

Hi

You need DAO library

Assuming you are using RS as the recordset name it is rs!fieldname to reference each coulumn in the recordset, where fieldname is you column name

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ETID]

Ok. I get type mismatch error (13)...
I have dao 3.6 loaded...

here is my code


----------------
Private Sub Command16_Click()

Dim Rs As Recordset
Set Rs = Me.RecordsetClone
Do Until Rs.EOF
MsgBox (Rs!FieldName)
Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing

End Sub
-----------------

also....
How does it know what form to read from ...Main or Sub?

 

[KenReay]

HI

If you have ADO loaded as well, DAO needs to be 'above' it in the list of references, or you need to qualify the Rs object (which exists in ADO and DAO

so:

Private Sub Command16_Click()

Dim Rs As DAO.Recordset
Set Rs = Me.RecordsetClone
Do Until Rs.EOF
MsgBox (Rs!FieldName)
Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing

End Sub

as to which form Main or Sub, above uses the Me keyword, so if the code above in in the mainform, then me referes to the main form, if it is in the sub form, then Me refers to the subform. So you should have the button (Command16) on the sub form
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ETID]

Thanks a Mil....

I had to add an rs.movefirst

otherwise it would only work 1 time through

but,...

now it's exaclty what I needed!


------
Private Sub Command4_Click()

Dim Rs As DAO.Recordset
Set Rs = Me.RecordsetClone
Rs.MoveFirst
If IsNull(Rs!Tests) Then MsgBox ("No Tests listed&quot: GoTo end_me

Do Until Rs.EOF
MsgBox (Rs!Tests)
Rs.MoveNext
Loop

end_me:
Rs.Close
Set Rs = Nothing

End Sub

 

[ETID]

Oh and thanks to Darrylles also...I may be able to use that code in this project as well.