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

John-
Now when i am opening Form "All Reports". Row "Employees Category Wise" is displaying. Next two rows are marked #Deleted, even if i close the form and reopen two rows are marked with #Deleted under row "Employees Category Wise".
Secondly while opening report "Employees Category Wise" i got the error message:The report name'Employees Category Wise'you entered in either the property sheet or macro is misspelled or referes to a report that does'nt exist.......

For your reference:
I have one macro "View Reports Macros" it has two macro names
cmdOpenReport : On Click
List5 : On Dbl Click
Both have Action OpenReport

Under Action Arguments:
Report Name =[Forms]![All Reports]![List5]
View ----- Print Preview
And how would the form "Select your choice" open so that user can select his choice?
Regards,
Khalid Tanweer

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Khalid-
>
> Ah, so the "delete" procedure simply checks for any in the table that aren't
> there anymore and deletes just those rows. Let's go back to the other one and
> to this:
>
> -----------------------------------------------
> 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
> ' Make sure the "special" row is there
> rst.FindFirst "ReportName = ""Employees Category Wise"""
> ' If not there,
> If rst.NoMatch Then
> ' Put it back
> rst.AddNew
> rst!ReportName = "Employees Category Wise"
> rst.Update
> End If
> 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
> ------------------------------------------
>
> .. and make this change to the "delete" procedure:
>
> ------------------------------------------
> Public Sub ReportNamesDelete()
>
> Dim db As DAO.Database
> Dim rst As DAO.Recordset
> Dim rpt As AccessObject
> Dim booFound As Boolean
>
> Set db = CurrentDb
> Set rst = db.OpenRecordset("tblReportsList", dbOpenDynaset)
>
> With rst
> ' Make sure there are records in the recordset
> Do While .EOF = False
> ' Set booFound as False for a Default setting
> booFound = False
> ' Special processing for Employees Category Wise
> If Left(rst!ReportName, 23) <> "Employees Category Wise" Then
> ' This is the fast way to check every name
> For Each rpt In CurrentProject.AllReports
> If !ReportName = rpt.Name Then
> booFound = True
> ' This Report is there so exit this loop
> ' and move on to the next Record
> Exit For
> End If
> Next rpt
> ' If the Report was not found
> ' booFound remains false
> If booFound = False Then
> .Delete
> End If
> Else
> ' If this is the "special" record,
> If rst!ReportName = "Employees Category Wise" Then
> ' Keep it
> Else
> ' Throw it away
> .Delete
> End If
> End If
> ' Move to the next record
> .MoveNext
> Loop
> End With
> rst.Close
>
> ' Release Memory
> Set rst = Nothing
> Set db = Nothing
>
> 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: Sunday, July 17, 2011 12:14 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Opening reports through Option Group
>
>
> John-
> This the code for ReportNamesDelete procedure:
>
> Public Sub ReportNamesDelete()
>
> Dim db As DAO.Database
> Dim rst As DAO.Recordset
> Dim rpt As AccessObject
> Dim booFound As Boolean
>
> Set db = CurrentDb
> Set rst = db.OpenRecordset("tblReportsList", dbOpenDynaset)
>
> With rst
> ' Make sure there are records in the recordset
> Do While .EOF = False
> ' Set booFound as False for a Default setting
> booFound = False
> ' This is the fast way to check every name
> For Each rpt In CurrentProject.AllReports
> If !ReportName = rpt.Name Then
> booFound = True
> ' This Report is there so exit this loop
> ' and move on to the next Record
> Exit For
> End If
> Next rpt
> ' If the Report was not found
> ' booFound remains false
> If booFound = False Then
> .Delete
> End If
> ' Move to the next record
> .MoveNext
> Loop
> End With
> rst.Close
>
> ' Release Memory
> Set rst = Nothing
> Set db = Nothing
>
> End Sub
>
> Regards,
> Khalid Tanweer
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Khalid-
> >
> > What's in the ReportNamesDelete procedure? I would expect it to simply be:
> >
> > CurrentDb.Execute "DELETE * FROM tblReportsList", dbFailOnError
> >
> >
> > 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 10:47 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [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
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>