ACCESS FEED: [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
>