Minggu, 17 Juli 2011

[MS_AccessPros] Re: Opening reports through Option Group

 

John-
I have three files in my folder D:\Mve
Mve_be
Mve (Microsoft Office Access Application)
Mve (Microsoft Office Access MDE Database)
If you need MDE file, then should i uncheck "View Database window" in startup options. OR you need me to upload complete folder zipped.

Regards,
Khalid Tanweer

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Khalid-
>
> Can you upload a copy of your database (compact and zip first) to the
> 2_Assistance_Needed folder on the group website?
>
> The macro needs to be changed to first test to see if the item selected is
> "Employees Category Wise". If that's the value, open your form with the Option
> Group instead of opening the report. In your macro, it'll look something like:
>
> Condition
> Action
> [Forms]![All Reports]![List5] = "Employees Category Wise" OpenForm .. name
> of your option group form
> ...
> StopMacro
>
> 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 2:07 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [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
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar