Filter Loop with OpenReport

[epifinygirl]

With my code below, I am trying to filter the 1 report for each record set in the "Temp Table". The report is filtered from a query "Carrier Report".

Public Function OrgIDReports()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlStr As String

sqlStr = "SELECT * FROM [Temp Table]"

Set db = CurrentDb
Set rs = db.OpenRecordset(sqlStr)

rs.MoveFirst

Do While Not rs.EOF
  DoCmd.OpenReport "I# Report", acViewNormal, , "[Carrier Report].[I#]= & rs"
  rs.MoveNext
Loop

MsgBox ("End of Client Org IDs")

End Function

I am currently receiving a "Run time 3075 - Syntax Error (missing operator) in query expression '([Carrier Report].[I#]=&rs)'. With the below line:

DoCmd.OpenReport "I# Report", acViewNormal, , "[Carrier Report].[I#]= & rs"

Any ideas what I am doing wrong???

[Marc Brown]

I would guess it's how you are sending over the variable rs. Try taking it out of the quotations so it would look like this.

  DoCmd.OpenReport "I# Report", acViewNormal, , "[Carrier Report].[I#]=" & chr(34) & rs & chr(34)

[NeoPa]

Marc's on the right lines but the code's a bit more broken than just that it seems. You also need to provide some value from your rs recordset and it needs to be surrounded by the relevant characters for the type of data. A reference of rs on its own simply points to a recordset object which is unusable. One of the properties must be specified.

[epifinygirl]

When I enter your code, I am now getting a "Type Mismatch" error

[epifinygirl]

Neopa - I'm not quite sure I follow. Can you post an example?

[NeoPa]

@EpiphanyGirl

Call DoCmd.OpenReport("I# Report", acViewNormal, , "[I#]=" & rs.?)

What type of data is [I#]?
Which field within [Temp Table] are you trying to match it with?

[epifinygirl]

I# is a number and the column in [Temp Table] is called I# as well.

[NeoPa]

EpiphanyGirl:
NeoPa - I'm not quite sure I follow. Can you post an example?

Your subsequent post (#4) illustrates exactly what I was saying.

If you answer the questions in my post #6 I can illustrate the point. I don't yet have enough information from you to do so.

PS. Clearly we are cross-posting ATM, so be patient. I'm sure we'll get it resolved shortly.

[NeoPa]

In that case :

Call DoCmd.OpenReport("I# Report", acViewNormal, , "[I#]=" & rs![I#])

[epifinygirl]

Perfect!! Thanks so much!!

One last question - When the OpenReport happens, I have the settings set-up so that it automatically prints the report to PDF (using Scansoft), however, since it's filtered from the same report, everything is saved with the same file name (ie - the report name). Would you know of a way to alter this so that it would also include the I# field that is it filtering on?

[NeoPa]

That may be a problem. I understand that the name of the file is directly linked to the name of the report. Unless you were to create a copy of the report for every time you ran it, possible as a standard database but not when rolled out as an MDE or ACCDE, this would not be possible.

If you're interested in exploring that further, post a question on it and I'll go into more detail. I can't do so here as it's a thread for a different question.