?
Solved

A97 - Report Manager Form with Filter Buttons

Posted on 2000-03-05
15
Medium Priority
?
363 Views
Last Modified: 2010-08-05
Access 97:

I wish to have a form with an unbound field to show specific reports based on several filter buttons, i.e.:
Form Report Buttons: General, Management, etc...

If the "General" button was pressed, all reports designated, as "GEN" would show in the unbound field.

Progress to date:

Table:  tblListOfReports

Fields:

ReportName            text            User friendly name for user to view
ReportType            text            Reports by prefix: rpt, srpt etc...
Calls                  text            Name of the item to call or refer to
ShortDesc            text            Short Description
UsageTips            Memo            General tips to guide the user
ReportCategory            text            Category used for Filtering, i.e. GEN, MGMT, INV, etc ...
Remarks            text            General Remarks for later updates etc...

***************
Form: frmReportMgr

Buttons on the left: General Reports, Management Reports, etc...
----------------
Unbound Field on the right:

Name: ReportList
Row Source Type: Table/Query
Row Source: qryReportList
-----------------
Field on top of Unbound Field: (This field simply shows the selected item in a bold text)

Name: SelectedRpt
Control Source: =[ReportList].[Column](0)
-----------------

Query: qryReportList

--------------------------------

To continue to make a short story long, I can not seem to get the buttons on the Report Manager form to filter the items in the unbound field

I wish to keep the unbound field blank, until one of the buttons is depressed to filter the reports.

For each button's On Click Event procedure I run the following:

********** Code Starts **********
Private Sub BtnGeneral_Click()
On Error GoTo BtnGeneral_Click_Err

    Forms!ReportMgr!BtnGeneral = "GEN"
    DoCmd.ApplyFilter "", "[qryReportList]![ReportType]=""GEN"""
    DoCmd.Requery "ReportList"


BtnGeneral_Click_Exit:
    Exit Sub

BtnGeneral_Click_Err:
    MsgBox Error$
    Resume BtnGeneral_Click_Exit

End Sub
********** Code Ends **********

Any help would be greatly appreciated.
0
Comment
Question by:DWB
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
15 Comments
 

Author Comment

by:DWB
ID: 2585549
A not so bunched up look at the table structure:

ReportName              text
ReportType            text      
Calls                  text
ShortDesc            text      
UsageTips            Memo            
ReportCategory            text            
Remarks              text            

0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2585691
Hi DWB!

You need not to use saved query and filter, just create Row Source string directly in the code:

Option Compare Database
Option Explicit
Dim strSQL As String

Private Sub btnGeneral_Click()
strSQL = "SELECT [ReportCategory], [ReportName] FROM tblListOfReports"
strSQL = strSQL & " WHERE [ReportCategory] = 'GEN'"
Me.ReportList.RowSource = strSQL
Me.ReportList.Requery
End Sub

'***********
'Here locate procedures for other command buttons
'***********

Private Sub cmdAllCategories_Click()
strSQL = "SELECT [ReportCategory], [ReportName] FROM tblListOfReports;"
Me.ReportList.RowSource = strSQL
Me.ReportList.Requery
End Sub

Private Sub Form_Open(Cancel As Integer)
strSQL = "SELECT [ReportCategory], [ReportName] FROM tblListOfReports;"
Me.ReportList.RowSource = strSQL
End Sub

Cheers,
Dedushka
0
 

Author Comment

by:DWB
ID: 2585818
Dedushka :-))

Thank you, form seems to work rather well!

Would it be difficult to introduce a double-click option to open the selected report?  I tried the following in a module; however, I'm not having much luck.

----------------------------
Function OpenReport(Report_type, Report_name)
  Select Case Report_type
    Case "rpt"
      DoCmd.Close
      DoCmd.OpenForm (Report_name)
  End Select
End Function
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 7

Expert Comment

by:Believer
ID: 2585863
Try DoCmd.OpenReport Report_name (not .OpenForm)
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2585875
Dave,
because [ReportList].[Column](1) contains report name, try this:

DoCmd.OpenReport [ReportList].[Column](1)

and let me know.

regards,
Dedushka
0
 

Author Comment

by:DWB
ID: 2585922
No Luck.

Receive Error Msg:

Compile Error
External Name not defined

Module: basOpenReport
---------------------------------
Option Compare Database
Option Explicit

Function OpenReport(Report_type, Report_name)
  Select Case Report_type
    Case "rpt"
      DoCmd.Close
      DoCmd.OpenReport [ReportList].[Column](1)

  End Select
End Function
---------------------------------

I call it from the unbound fields On Click Event Procedure as follows:

=OpenReport([ReportList].[Column](1),[ ReportList].[Column](3))
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2585936
Dave,
if youuse Report_name parameter, so you just need to open report with this parameter:

Function OpenReport(Report_type, Report_name)
  Select Case Report_type
    Case "rpt"
      DoCmd.Close
      DoCmd.OpenReport Report_name
  End Select
End Function
0
 

Author Comment

by:DWB
ID: 2585959
Very frustrating just can't seem to make it happen!
0
 

Author Comment

by:DWB
ID: 2585967
Very frustrating just can't seem to make it happen!
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2585982
Can you send me this .mdb file for looking over?
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2585986
BTW, you need not DoCmd.Close in your function:

Function OpenReport(Report_type, Report_name)
  Select Case Report_type
    Case "rpt"
      DoCmd.OpenReport Report_name
  End Select
End Function


0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2586172
Congratulations, Dave!
Only 5 steps to resolve your problem.

1. frmReportMgr - record source deleted, now form is unbounded.
2. ReportList properties:
   Column Count - 5
   Column Width - 7cm;0cm;0cm;0cm;0cm

   
Because you check Report_Type in your function:
-----
Function OpenReport(Report_type, Report_name)
  Select Case Report_type
    Case "rpt"
      DoCmd.Close
      DoCmd.OpenReport Report_name
  End Select
End Function
-----
RowSource for ReportList changed to

"SELECT [ReportName], [ReportType], [ReportCategory], [ShortDesc], [Calls] FROM tblListOfReports;"

in all OnClick procedures for buttons and in OnOpen procedure for the form, for example:

Private Sub Form_Open(Cancel As Integer)
strSQL = "SELECT [ReportName], [ReportType], [ReportCategory], [ShortDesc], [Calls] FROM tblListOfReports;"
Me.ReportList.RowSource = strSQL
End Sub

Adding new column require to shift column numbers:
3. rptType textbox properties:
   Control Source =[ReportList].[Column](2)
4. Notes textbox properties:
   Control Source =[ReportList].[Column](3)


5. ReportList listbox properties:
   On Dbl Click old setting =OpenReport([ReportList].[Column](1);[ ReportList].[Column](0))
   On Dbl Click new setting =OpenReport([ReportList].[Column](1);[ReportList].[Column](4))

   !!! - here is your main problem, i.e. [ ReportList].[Column](0) contains a space before listbox name.
   And certainlly you should use actual report name from Column(4), i.e. Calls.

That's all.
Try to make this changes in your database and let me know.
If you need, I can send back to you the database with my proves.
Cheers,
Dedushka
0
 
LVL 7

Accepted Solution

by:
Dedushka earned 400 total points
ID: 2586786
Congratulations DWB!
Only 5 steps to solve your problem :-)

1. frmReportMgr - record source deleted, now form is unbounded.
2. ReportList properties:
   Column Count - 5
   Column Width - 7cm;0cm;0cm;0cm;0cm

   
Because you check Report_Type in your function:
-----
Function OpenReport(Report_type, Report_name)
  Select Case Report_type
    Case "rpt"
      DoCmd.Close
      DoCmd.OpenReport Report_name
  End Select
End Function
-----
RowSource for ReportList changed to

"SELECT [ReportName], [ReportType], [ReportCategory], [ShortDesc], [Calls] FROM tblListOfReports;"

in all OnClick procedures for buttons and in OnOpen procedure for the form, for example:

Private Sub Form_Open(Cancel As Integer)
strSQL = "SELECT [ReportName], [ReportType], [ReportCategory], [ShortDesc], [Calls] FROM tblListOfReports;"
Me.ReportList.RowSource = strSQL
End Sub

Adding new column require to shift column numbers:
3. rptType textbox properties:
   Control Source =[ReportList].[Column](2)
4. Notes textbox properties:
   Control Source =[ReportList].[Column](3)


5. ReportList listbox properties:
   On Dbl Click old setting =OpenReport([ReportList].[Column](1);[ ReportList].[Column](0))
   On Dbl Click new setting =OpenReport([ReportList].[Column](1);[ReportList].[Column](4))

   !!! - here is your main problem, i.e. [ ReportList].[Column](0) contains a space before listbox name.
   And certainlly you should use actual report name from Column(4), i.e. Calls.

That's all.
Try to make this changes in your database and let me know.
If you need, I can send back to you the database with my proves.
Cheers,
Dedushka
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2586791
Sorry, something wrong, because I didn't found my previous comment.
0
 

Author Comment

by:DWB
ID: 2589173
Dedushka,

How can I thank you, you've come through again, all worked well.  

I had some minor issues with the buttons to filter the report categories, but I was able to sort that out.  As I progress with this particular database, I'm sure I have more questions, and/or difficulties to pose to you and the other experts.

Many Thanks,

Dave
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question