ACCESS FEED: RE: [MS_AccessPros] Re: Opening reports through Option Group

Khalid-

If ReportNamesDelete clears the table, and you always run Delete before this
code, then it should be:

Public Sub ReportNamesToTable()
On Error GoTo Err_ReportNamesToTable

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rpt As AccessObject
Dim strRptName As String

Set db = CurrentDb
Set rst = db.OpenRecordset("tblReportsList", dbOpenDynaset, dbAppendOnly)

' First, add the special entry
rst.AddNew
rst!ReportName = "Employees Category Wise"
rst.Update

' Loop through all the Report Names in the Database
For Each rpt In CurrentProject.AllReports
strRptName = rpt.Name ' Get the Name
' Check for Employees Category Wise
If Left(rpt.Name, 23) <> "Employees Category Wise"
With rst
.AddNew
!ReportName = strRptName
.Update
End With
End If
Next rpt
rst.Close

Exit_ReportNamesToTable:
' Release Memory
Set rst = Nothing
Set db = Nothing
Exit Sub

Err_ReportNamesToTable:
MsgBox "Error " & Err.Number & " (" & Err.Description & ")" & _
" In procedure ReportNamesToTable"
Resume Exit_ReportNamesToTable
End Sub

That puts one entry for "Employees Category Wise" in the table along with all
the other report names. In the form that lets the user select from the list,
you have to check to see if the user has selected this "special" entry and open
your option form instead of opening the report selected.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
Sent: Sunday, July 17, 2011 9:16 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Opening reports through Option Group

John-
Below is the code for ReportNamesToTable, and thanks to you it was provided to
me by you:

Public Sub ReportNamesToTable()
On Error GoTo Err_ReportNamesToTable

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rpt As AccessObject
Dim strRptName As String
Dim strFind As String

Set db = CurrentDb
Set rst = db.OpenRecordset("tblReportsList", dbOpenDynaset)

' Loop through all the Report Names in the Database
For Each rpt In CurrentProject.AllReports
strRptName = rpt.Name ' Get the Name

With rst
strFind = "ReportName = """ & strRptName & """"
.FindFirst strFind
If .NoMatch Then
.AddNew
!ReportName = strRptName
.Update
End If
End With
Next rpt
rst.Close

Exit_ReportNamesToTable:
' Release Memory
Set rst = Nothing
Set db = Nothing
Exit Sub

Err_ReportNamesToTable:
MsgBox "Error " & Err.Number & " (" & Err.Description & ")" & _
" In procedure ReportNamesToTable"
Resume Exit_ReportNamesToTable
End Sub

Regards,
Khalid

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Khalid-
>
> I would need to see the code for ReportNamesToTable. You could "customize" it
> to replace the two reports in the list with one entry, then if the user
selects
> that record, open your form with the option buttons.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
> Sent: Sunday, July 17, 2011 8:43 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Opening reports through Option Group
>
>
>
> John-
> ReportNamesDelete and ReportNamesToTable was required to run to autofill
report
> names and remove any reports deleted from the form "All Reports". Now in the
> form "Select your choice" i have done as you mentioned to create a command
> button to trigger opening the correct report, the code is as folows for OK
> button:
>
> Private Sub Command7_Click()
> If Frame0 = 0 Then
> 'Open All Projects
> DoCmd.OpenReport "Employees Category Wise All Projects", acPreview
> Else
> DoCmd.Close
> 'Open Selective Project
> DoCmd.OpenReport "Employees Category Wise Selective Project", acPreview
> End If
> Exit Sub
> End Sub
>
> Code for Cancel button is:
>
> Private Sub Command9_Click()
> On Error GoTo Err_Command9_Click
>
>
> DoCmd.Close
> DoCmd.OpenForm "All Reports"
> Exit_Command9_Click:
> Exit Sub
>
> Err_Command9_Click:
> MsgBox Err.Description
> Resume Exit_Command9_Click
>
> End Sub
>
> The main thing which i want to solve is that all reports names are there in
Form
> "All Reports" when i open this form the following two reports in question are
> shown there:
> "Employees Category Wise All Projects"
> "Employees Category Wise Selective Project"
> Would it be not better that a report named "Employees Category Wise" shown in
> the list, after opening it, the form "Select your choice" opens and user
selects
> his required report.
> BUT again my question is should i stop auto filling the form "All Reports" by
> not running procedure ReportNamesToTable
> Regards,
> Khalid Tanweer
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Khalid-
> >
> > Why do you need to run ReportNamesDelete and ReportNamesToTable when the
form
> > opens?
> >
> > If you have an option group, you need a command button to trigger opening
the
> > correct report after the user picks the report wanted in the group. The
code
> > behind the command button might look like:
> >
> > Private Sub cmdOpenReport_Click()
> > ' Check the option wanted
> > If Me.optReport = 1 Then
> > ' Open All projects
> > DoCmd.OpenReport "Employees Category Wise All Projects",
acViewPreview
> > Else
> > ' Open selective
> > DoCmd.OpenReport " Employees Category Wise Selective Project",
> > acViewPreview
> > End If
> > End Sub
> >
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid Tanweer
> > Sent: Saturday, July 16, 2011 1:45 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Opening reports through Option Group
> >
> > Hi John,
> > Hope you doing well. Long ago you gave me procedures to automatically
populate
> > Report Names in a form "All Reports" and if any report is deleted it is
> > automatically deleted from the form. Every thing is working there smoothly.
> > Now i have come to a situation that if i had two reports:
> >
> > "Employees Category Wise All Projects"
> > "Employees Category Wise Selective Project"
> >
> > I have created a form "Select your choice" having option Group for the above
> two
> > reports. This form works and opens both reports simultaneously.
> >
> > My question is that, what should i do for the report names residing in form
> "All
> > Reports", and on form "All Reports" On Open event this code is running:
> >
> > Private Sub Form_Open(Cancel As Integer)
> > 'This provides an informative and professional
> > ' appearance to our Form Title Bar
> > Me.Caption = " Report Date Manager" & _
> > "Today is " & Format(Date, "dddd"", ""d/m/yyyy")
> >
> > ' We call these two Procedures to get the
> > ' current Report Names in our table
> > ReportNamesDelete
> > ReportNamesToTable
> > End Sub
> >
> > Now where should i place this form "Select your choice". Please also suggest
> is
> > this a good practice to make these type of forms asking user to select his
> > choice from Option Group, or keeping all report names in form "All Reports"
is
> > better. I am thinking that if i make such forms asking user to select his
> choice
> > would reduce the number of reports in form "All Reports"
> >
> > Regards,
> > Khalid Tanweer
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

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

Yahoo! Groups Links