For Each loop syntax problem

[Ron Wan]

I am inexperienced at this (posting and writitng code).
I am using an Access2000 datasheet form with checkboxes to select PDF documents for printing. The PDFs are given filenames based on the doc_id assigned as the primary key in the doc table when the doc is entered into the database. What I can't do is use a form to select which docs to print with AdobeReader. I have been trying For Each loops but can't get the syntax and proper variable types to work. What I need to know is how to write this:
Dim (what???)
For Each (doc_id???) in (form control or underlyingtable ???)
If PrintThis field = "Yes" then
(Print using AdobeReader)
Next

Thanks,
Ron

[ADezii]

I am inexperienced at this (posting and writitng code).
I am using an Access2000 datasheet form with checkboxes to select PDF documents for printing. The PDFs are given filenames based on the doc_id assigned as the primary key in the doc table when the doc is entered into the database. What I can't do is use a form to select which docs to print with AdobeReader. I have been trying For Each loops but can't get the syntax and proper variable types to work. What I need to know is how to write this:
Dim (what???)
For Each (doc_id???) in (form control or underlyingtable ???)
If PrintThis field = "Yes" then
(Print using AdobeReader)
Next

Thanks,
Ron

Something similar to this should work:
[CODE=vb]Dim MyRS As DAO.Recordset

Set MyRS = Me.RecordsetClo ne
MyRS.MoveFirst

Do While Not MyRS.EOF
If MyRS![Print] Then 'Check Box selected
'Here are the Document Names that correspond with the Check
'Boxes that you selected - Print using ADOBE READER
Debug.Print "You selected Document '" & MyRS![Document] & "' for Printing"
End If
MyRS.MoveNext
Loop[/CODE]
Sample OUTPUT:
[CODE=text]
You selected Document 'One' for Printing
You selected Document 'Two' for Printing
You selected Document 'Three' for Printing
You selected Document 'Four' for Printing
You selected Document 'Five' for Printing
You selected Document 'Six' for Printing
You selected Document 'Seven' for Printing
You selected Document 'Eight' for Printing
You selected Document 'Nine' for Printing[/CODE]

[Ron Wan]

Thanks for the reply. So I should use a Do Loop instead of For Each?

I tried it and I had to add the DAO reference to get past the [Dim MyRS As DAO.Recordset] line then [Me.RecordsetClo ne] returns an invalid reference error also. Do I add more references in the references dialog box? if so what?

Thanks,
Ron

[ADezii]

Thanks for the reply. So I should use a Do Loop instead of For Each?

I tried it and I had to add the DAO reference to get past the [Dim MyRS As DAO.Recordset] line then [Me.RecordsetClo ne] returns an invalid reference error also. Do I add more references in the references dialog box? if so what?

Thanks,
Ron

Did you use this syntax after the Declaration?
[CODE=vb]Set MyRS = Me.RecordsetClo ne[/CODE]

[Ron Wan]

Yes I did. Here it is:

Dim MyRS As DAO.Recordset
Set MyRS = Me.Recordset.Cl one
MyRS.MoveFirst

I get this error: "Object variable or With block variable not set (Error 91)"
Do I need to check another reference to the reference library and if so what?

Thanks,
Ron

[ADezii]

Yes I did. Here it is:

Dim MyRS As DAO.Recordset
Set MyRS = Me.Recordset.Cl one
MyRS.MoveFirst

I get this error: "Object variable or With block variable not set (Error 91)"
Do I need to check another reference to the reference library and if so what?

Thanks,
Ron

Try
[CODE=vb]
Set MyRS = Me.RecordsetClo ne[/CODE]
instead of
[CODE=vb]
Set MyRS = Me.Recordset.Cl one[/CODE]

[Ron Wan]

I fixed that but still get an error

Dim MyRS As Recordset
Set MyRS = Me.RecordsetClo ne
MyRS.MoveFirst

Run-time error 7951 You entered and expression that has an invalid reference to the RecordsetClone property.

The form with the recordset on it is actually a subform. The command button is on the main form. When I change Me.Recordset to Forms![MySubform] it returns a can't find the form error. I think maybe the recordset is no being found.

Any ideas?

[ADezii]

I fixed that but still get an error

Dim MyRS As Recordset
Set MyRS = Me.RecordsetClo ne
MyRS.MoveFirst

Run-time error 7951 You entered and expression that has an invalid reference to the RecordsetClone property.

The form with the recordset on it is actually a subform. The command button is on the main form. When I change Me.Recordset to Forms![MySubform] it returns a can't find the form error. I think maybe the recordset is no being found.

Any ideas?

The form with the recordset on it is actually a subform.

This is a slight detail that you should have mentioned earlier on. To reference a Sub-Form's Recordset from the Parent Form via the RecordsetClone Property, the syntax is different since you are not referencing the Sub-Form Control, but the Form contained within the Sub-Form Control. In any event, the code would be:
[CODE=vb]
Dim MyRS As DAO.Recordset
Set MyRS = Me![MySubform].Form.Recordset Clone
MyRS.MoveFirst[/CODE]
NOTE: MySubform is the Name of the Sub-Form Control NOT the Form.

[Ron Wan]

IT WORKED!

Thanks so much for your help,
Ron

[ADezii]

IT WORKED!

Thanks so much for your help,
Ron

Glad we got it working.