By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,857 Members | 1,768 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,857 IT Pros & Developers. It's quick & easy.

Filter Loop with OpenReport

P: 19
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".
Expand|Select|Wrap|Line Numbers
  1. Public Function OrgIDReports()
  2.  
  3. Dim db As DAO.Database
  4. Dim rs As DAO.Recordset
  5. Dim sqlStr As String
  6.  
  7. sqlStr = "SELECT * FROM [Temp Table]"
  8.  
  9. Set db = CurrentDb
  10. Set rs = db.OpenRecordset(sqlStr)
  11.  
  12. rs.MoveFirst
  13.  
  14. Do While Not rs.EOF
  15.   DoCmd.OpenReport "I# Report", acViewNormal, , "[Carrier Report].[I#]= & rs"
  16.   rs.MoveNext
  17. Loop
  18.  
  19. MsgBox ("End of Client Org IDs")
  20.  
  21. End Function
  22.  
I am currently receiving a "Run time 3075 - Syntax Error (missing operator) in query expression '([Carrier Report].[I#]=&rs)'. With the below line:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "I# Report", acViewNormal, , "[Carrier Report].[I#]= & rs"
  2.  
Any ideas what I am doing wrong???
Oct 11 '11 #1

✓ answered by NeoPa

In that case :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.OpenReport("I# Report", acViewNormal, , "[I#]=" & rs![I#])

Share this Question
Share on Google+
10 Replies


P: 16
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.

Expand|Select|Wrap|Line Numbers
  1.   DoCmd.OpenReport "I# Report", acViewNormal, , "[Carrier Report].[I#]=" & chr(34) & rs & chr(34)
  2.  
  3.  
Oct 11 '11 #2

NeoPa
Expert Mod 15k+
P: 31,660
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.
Oct 11 '11 #3

P: 19
When I enter your code, I am now getting a "Type Mismatch" error
Oct 11 '11 #4

P: 19
Neopa - I'm not quite sure I follow. Can you post an example?
Oct 11 '11 #5

NeoPa
Expert Mod 15k+
P: 31,660
@EpiphanyGirl
Expand|Select|Wrap|Line Numbers
  1. 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?
Oct 11 '11 #6

P: 19
I# is a number and the column in [Temp Table] is called I# as well.
Oct 11 '11 #7

NeoPa
Expert Mod 15k+
P: 31,660
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.
Oct 11 '11 #8

NeoPa
Expert Mod 15k+
P: 31,660
In that case :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.OpenReport("I# Report", acViewNormal, , "[I#]=" & rs![I#])
Oct 11 '11 #9

P: 19
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?
Oct 11 '11 #10

NeoPa
Expert Mod 15k+
P: 31,660
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.
Oct 11 '11 #11

Post your reply

Sign in to post your reply or Sign up for a free account.