DWB
asked on
A97 - Report Manager Form with Filter Buttons
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
DoCmd.ApplyFilter "", "[qryReportList]![ReportTy
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.
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
DoCmd.ApplyFilter "", "[qryReportList]![ReportTy
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.
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
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
ASKER
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
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
Try DoCmd.OpenReport Report_name (not .OpenForm)
Dave,
because [ReportList].[Column](1) contains report name, try this:
DoCmd.OpenReport [ReportList].[Column](1)
and let me know.
regards,
Dedushka
because [ReportList].[Column](1) contains report name, try this:
DoCmd.OpenReport [ReportList].[Column](1)
and let me know.
regards,
Dedushka
ASKER
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].[
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].[
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
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
ASKER
Very frustrating just can't seem to make it happen!
ASKER
Very frustrating just can't seem to make it happen!
Can you send me this .mdb file for looking over?
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
Function OpenReport(Report_type, Report_name)
Select Case Report_type
Case "rpt"
DoCmd.OpenReport Report_name
End Select
End Function
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].[
On Dbl Click new setting =OpenReport([ReportList].[
!!! - 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
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].[
On Dbl Click new setting =OpenReport([ReportList].[
!!! - 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, something wrong, because I didn't found my previous comment.
ASKER
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
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
ASKER
ReportName text
ReportType text
Calls text
ShortDesc text
UsageTips Memo
ReportCategory text
Remarks text