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

John-
I modified the code as you did, on the line:
If Left(rpt.Name, 23) <> "Employees Category Wise"
It was showing error message, Compile error Expected: Then or GoTo
So i added Then at the end:
If Left(rpt.Name, 23) <> "Employees Category Wise" Then

After that when i opened the form "All Reports" it is displaying all report name three times.
Regards,
Khalid

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> 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
>