Having Trouble with Looping

[mcfischer91]

I have a code that is based on a query. Basically when I press the reports button, it is supposed to start with the first record, open the report based on the store number that's in the first column, and then send that in a pdf file. It's then supposed to move on to the next record and repeat the process. When I run it however, it sends the correct number of emails, but the information never seems to change from the first record, so it sends based on the email address and store number of what is in the first record. My code is below.

Private Sub Reports_Click()

Dim MyDB As DAO.Database, RS As DAO.Recordset
Dim Email As String
Set MyDB = DBEngine.Workspaces(0).Databases(0)


Set RS = MyDB.OpenRecordset _
    ("CMR Submits")


RS.MoveLast
RS.MoveFirst
Do Until RS.EOF
    Email = Me.Email
    DoCmd.OpenReport "CMR Submit Store Report", acViewReport, , "[Store]=" & Me.Store__ & ""
    DoCmd.SendObject acSendReport, "CMR Submit Store Report", acFormatPDF, Email, , , "CMR Reports", , True
    RS.MoveNext
Loop


RS.Close
MyDB.Close
Set RS = Nothing
Set MyDB = Nothing
Close


End Sub

[fredz]

It appears that you are looping thru a recordset (rs), but you are not using the recordset values.
I think you want to use rs!Email instead of Me.Email, and rs!Store instead of Me.Store.
Fred

[mcfischer91]

When I change to RS!Store_#, I get an error <Item Not Found in this Collection> I know that it is definitely in the query in that syntax.

[June7]

Should not use spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be Store_Num or StoreNum.

Try:

rs![Store_#]

or

rs.Fields("[Store_#]")

[mcfischer91]

Yeah I did that before I realized I was going to be using it for any coding. I changed it to just store and.....it works! Thanks a bunch you guys are great!