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

Buttons to Filter Report

P: 17
Hello there,

I have created number of Buttons on unbound form to filter the report based on a pivot query called filters. Report named AnnualReport-ProjectFilter.

What I would like to do is that:
1. When I click a button named btnCT then it should search in a Pivot query where the field named ProjectName = CT then views only all the records related to CT Project in the Report.

Now when I click a button name btnCP, it should exhibit only all records which belong to this criteria so on...

Here is some codes has been utilized but however it does not work. in fact it brings up a blank report with no data.
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCT_Click()
  2. On Error GoTo Err_btnCT_Click
  3.  
  4. Dim stDocName As String
  5. Dim stLinkCriteria As String
  6.  
  7. stDocName = "AnnualReport-ProjectFilter"
  8. stLinkCriteria = "[ProjectName] like ' CT ' "
  9. DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
  10.  
  11. Exit_btnCT_Click:
  12. Exit Sub
  13.  
  14. Err_btnCT_Click:
  15. MsgBox Err.Description
  16. Resume Exit_btnCT_Click
  17.  
  18. End Sub
Please, any kind of help would be appreciated.

Big thanks in advance
Feb 7 '08 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,438
Check out Example Filtering on a Form.
Feb 7 '08 #2

P: 17
Check out Example Filtering on a Form.

Thank you so much for sending this Tutorial. In fact, it is very useful. However, I can not work it out nor apply to my database. This sample is for unbound button, text field... filter which has a table resource bounded.

Do you have anything likely to view report based on a text or button as a criteria then filter the record accordingly?

Thank you
Feb 11 '08 #3

NeoPa
Expert Mod 15k+
P: 31,438
A filter for a report is simply passed as one of the parameters (fourth) to DoCmd.OpenReport().
Format the WHERE clause as you would whaen adding it to a SQL SELECT string except you leave off the "WHERE " at the beginning.

If your ProjectName needs to have, say, "CT" IN it, then something like the following is needed.
Expand|Select|Wrap|Line Numbers
  1. "[ProjectName Like '*" & Me.txtSearch & "*'"
This assumes a TextBox field called txtsearch and that the operator entered "CT" into it of course.
Feb 11 '08 #4

P: 17
A filter for a report is simply passed as one of the parameters (fourth) to DoCmd.OpenReport().
Format the WHERE clause as you would whaen adding it to a SQL SELECT string except you leave off the "WHERE " at the beginning.

If your ProjectName needs to have, say, "CT" IN it, then something like the following is needed.
Expand|Select|Wrap|Line Numbers
  1. "[ProjectName Like '*" & Me.txtSearch & "*'"
This assumes a TextBox field called txtsearch and that the operator entered "CT" into it of course.
Thank you so much for great hint, but I am really sorry I do not get it. The code shew aboved had it from googling.

Could you tell me how please.

Thank you

Regards,
Feb 12 '08 #5

P: 17
Thank you so much for great hint, but I am really sorry I do not get it. The code shew aboved had it from googling.

Could you tell me how please.

Thank you

Regards,

Hang on.....

I got it.

Thank you so much. Really appreciated
Feb 12 '08 #6

P: 17
Hang on.....

I got it.

Thank you so much. Really appreciated
On top of this,

How Can I Msgbox if no record found?

Thank you in advance

Regards,
Feb 12 '08 #7

NeoPa
Expert Mod 15k+
P: 31,438
That depends on the circumstances.
Pop in your current code for using the filter and I'll tell you what you need to do to handle an empty recordset. It's different for Reports; Queries; etc.
Feb 13 '08 #8

Post your reply

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