Re: Parameter thru Form Dialog Box for REPORT

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thanks its working now!! Thanks a lot of yr patience and reply! In case i
find it some problem, i will get back but as of now its ok..

"Jeff Boyce" wrote:

> Sandy
>
> Please read through my earlier responses. Based on your descriptions to
> date, were I faced with doing what you seem to be trying to do, I would:
>
> 1. create a form to collect parameters (?your "Options Rpt Air")
> 2. add a command button to the form
> 3. add a procedure to the command button -- in the procedure:
> a. run the delete query
> b. run the first append query
> c. run the second append query
> d. open the report
>
> --
> Regards
>
> Jeff Boyce
> <Office/Access MVP>
>
> "Sandy" <Sandy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:402EF9F7-8B59-44F5-A3A9-242186153D84@xxxxxxxxxxxxxxxx
> > My basic purpose is to open report "Air Summary Report". This report has
> > linked form "Options Rpt Air" for collecting parameters from user ie month
> > range. From Month and To Month. The Report "Air Summary Report" is based
> on
> > table "Air Summary Report Query", which gets updated from 3 queries
> (delete
> > and append) ie "Air Summary Qry Clear", "Air Summary Inv Qry" (Append) and
> > "Air Summary Crn Qry" (Append). These 2 append queries collect parameter
> > from "Options Rpt Air" user parameter form, as these have month ranges:
> from
> > month and to month.
> > So when i run report "Air Summary Report", it first of all open user
> > paramter form, and upon user months inputs, should run delete and append
> > queries by passing on parameter information to 2 of append queries and
> > finally provide report.
> > Append queries has following criteria :
> > Between [Forms]![Options Rpt Air]![StartMonth] And [Forms]![Options Rpt
> > Air]![EndMonth]
> > Also there's main module which has codes for Report Event as under,
> besides
> > codes already mentioned in report open event:
> >
> > Option Compare Database
> > Option Explicit
> > Public bInReportOpenEvent As Boolean ' Is report in the Open event?
> >
> > Function IsLoaded(ByVal strFormName As String) As Boolean
> > ' Returns True if the specified form is open in Form view or Data***
> view.
> > Dim oAccessObject As AccessObject
> >
> > Set oAccessObject = CurrentProject.AllForms(strFormName)
> > If oAccessObject.IsLoaded Then
> > If oAccessObject.CurrentView <> acCurViewDesign Then
> > IsLoaded = True
> > End If
> > End If
> >
> > End Function
> >
> >
> >
> > Please guide me if am still not clear.
> >
> >
> > "Jeff Boyce" wrote:
> >
> > > Sandy
> > >
> > > There is no right/wrong naming conventions, but consistency and
> > > identifiability can certainly help.
> > >
> > > I'm afraid seeing the code you are using, and the query and table names
> you
> > > are using have not clarified what you hope to accomplish, only how you
> are
> > > trying to do something I can't quite envision.
> > >
> > > If you were explaining what business need you are trying to achieve, to
> an
> > > 80-year old grandmother, you wouldn't use "table" or "query" or "report"
> or
> > > "code". Can you describe "what" you want to do, as if you were not
> using a
> > > computer at all?
> > >
> > > --
> > > Regards
> > >
> > > Jeff Boyce
> > > <Office/Access MVP>
> > >
> > > "Sandy" <Sandy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > news:027FD3A1-07A6-4BE3-B8AD-708534DF51E3@xxxxxxxxxxxxxxxx
> > > > Am elaborating further:
> > > > Report Name : Air Summary Report
> > > > Form Name associated with above report for giving options to user :
> > > Options
> > > > Rpt Air. This is linked with parameters of below 2 queries "Air
> Summary
> > > Inv
> > > > Qry and Air Summary Crn Qry. This forms collect parameters and give it
> two
> > > > these queries.
> > > > Table Name which is basis for above report : Air Summary Report Query
> > > >
> > > > Queries which will update table " Air Summary Report Query" when
> report is
> > > > run :
> > > > Air Summary Qry Clear, Air Summary Inv Qry and Air Summary Crn Qry.
> > > >
> > > > Codes are given in Report "Air Summary Report"
> > > >
> > > > Codes basically evoke 3 queries and open "Options Rpt Air" .
> > > >
> > > > Following are the codes :
> > > >
> > > > Private Sub Report_Open(Cancel As Integer)
> > > > 'Set public variable to true to indicate that the report
> > > > 'is in the Open Even
> > > >
> > > > bInReportOpenEvent = True
> > > >
> > > > 'Open Daily Report--This is Form which give user options and collect
> > > > parameter information "
> > > > DoCmd.OpenForm "Options Rpt Air", , , , , acDialog
> > > >
> > > >
> > > >
> > > > 'Hide System Messages on Append Queries and Clear Queries'
> > > >
> > > > DoCmd.SetWarnings False
> > > >
> > > > On Error GoTo ErrorHandler
> > > > 'Open Clear Query---This clear data from Table "Air Summary Report
> Query"
> > > > on which report is based'
> > > > DoCmd.OpenQuery "Air Summary Qry Clear", , acEdit
> > > >
> > > > 'Open Air Summary Inv Qry----This run append query which will feed
> data
> > > into
> > > > table "Air Summary Report Query".Paramters are collected from Form
> Options
> > > > Rpt Air'
> > > >
> > > >
> > > > DoCmd.OpenQuery "Air Summary Inv Qry", , acEdit
> > > >
> > > > 'Open Air Summary Crn Qry--------This run append query which will feed
> > > data
> > > > into table "Air Summary Report Query. Paramters are collected from
> Form
> > > > Options Rpt Air'
> > > >
> > > > DoCmd.OpenQuery "Air Summary Crn Qry", , acEdit
> > > >
> > > > 'Cancel Report if user clicked the Cancel Button
> > > > If IsLoaded("Options Rpt Air") = False Then Cancel = True
> > > >
> > > > 'Set the public variable to false to indicate the
> > > > ' Open event is completed
> > > > bInReportOpenEvent = False
> > > >
> > > > ExitHere:
> > > >
> > > > Exit Sub
> > > >
> > > > ErrorHandler:
> > > > If Err.Number = 2001 Then
> > > > Resume ExitHere
> > > > End If
> > > > End Sub
> > >
> > ------------------------------------------------------------------------
> > > > Hope above is clear Sir. Regret for using wrong nomenclatures in names
> of
> > > > tables/query and report. I wil try correcting them.
> > > >
> > > >
> > > > "Jeff Boyce" wrote:
> > > >
> > > > > Sandy
> > > > >
> > > > > I hadn't understood that your Report opened the forms/queries. In
> my
> > > mind
> > > > > (a dark & scary place!), a form would be opened first, and include a
> > > command
> > > > > button to open a report.
> > > > >
> > > > > Part of my confusion may be terminology. In your code, you appear
> to
> > > open a
> > > > > form:
> > > > >
> > > > > DoCmd.OpenForm "Options Rpt Air", , , , , acDialog
> > > > >
> > > > > but you call it opening a daily report.
> > > > >
> > > > > Since you've opened this form in Dialog mode, how do you "get back"
> to
> > > the
> > > > > report (Access report, not your definition of report/form)?
> > > > >
> > > > > Would you be willing to pursue this a bit further? Could you
> describe
> > > what
> > > > > you are trying to do WITHOUT using the word "form" or "report"?
> What
> > > this
> > > > > would do is take the discussion away from the "how do I", and focus
> more
> > > on
> > > > > "what I need to accomplish."
> > > > >
> > > > > --
> > > > > Regards
> > > > >
> > > > > Jeff Boyce
> > > > > <Office/Access MVP>
> > > > >
> > > > > "Sandy" <Sandy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > > news:50E46260-3FCB-4B22-9A32-6B7FC2B5CA6C@xxxxxxxxxxxxxxxx
> > > > > > Sure! Thanks for interest!
> > > > > > Private Sub Report_Open(Cancel As Integer)
> > > > > > 'Set public variable to true to indicate that the report
> > > > > > 'is in the Open Even
> > > > > >
> > > > > > bInReportOpenEvent = True
> > > > > >
> > > > > > 'Open Daily Report'
> > > > > > DoCmd.OpenForm "Options Rpt Air", , , , , acDialog
> > > > > >
> > > > > >
> > > > > >
> > > > > > 'Hide System Messages on Append Queries and Clear Queries'
> > > > > > DoCmd.SetWarnings False
> > > > > > On Error GoTo ErrorHandler
> > > > > > 'Open Clear Query'
> > > > > > DoCmd.OpenQuery "Air Summary Qry Clear", , acEdit
> > > > > >
> > > > > > 'Open Air Summary Inv Qry'
> > > > > > DoCmd.OpenQuery "Air Summary Inv Qry", , acEdit
> > > > > >
> > > > > > 'Open Air Summary Crn Qry'
> > > > > > DoCmd.OpenQuery "Air Summary Crn Qry", , acEdit
> > > > > >
> > > > > > 'Cancel Report if user clicked the Cancel Button
> > > > > > If IsLoaded("Options Rpt Air") = False Then Cancel = True
> > > > > >
> > > > > > 'Set the public variable to false to indicate the
> > > > > > ' Open event is completed
> > > > > > bInReportOpenEvent = False
> > > > > >
> > > > > > ExitHere:
> > > > > >
> > > > > > Exit Sub
> > > > > >
> > > > > > ErrorHandler:
> > > > > > If Err.Number = 2001 Then
> > > > > > Resume ExitHere
> > > > > > End If
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Jeff Boyce" wrote:
> > > > > >
> > > > > > > Sandy
> > > > > > >
> > > > > > > Consider posting the code you are now using. It's a bit tough
> to
> > > > > diagnose
> > > > > > > why something isn't working without a look at it...
> > > > > > >
> > > > > > > --
> > > > > > > Regards
> > > > > > >
> > > > > > > Jeff Boyce
> > > > > > > <Office/Access MVP>
> > > > > > >
> > > > > > > "Sandy" <Sandy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > > > > news:7B74B2AF-09DB-4644-9285-B2F45F139FC0@xxxxxxxxxxxxxxxx
> > > > > > > > Thanks its working ok! But 2 things which needs to be
> cancelled :
> > > > > > > > If we cancel report form, the parameter pop up comes from
> queries.
> > > > > Also
> > > > > > > > blanks reports come with null values. If we enter parameters,
> then
> > > > > system
> > > > > > > > keep on poping all parameter information and after that
> nothing
> > > > > > > displays..
> > > > > > > > I wanted to switch of parameter pop up when i cancel report.
> > > Secondly,
> > > > > > > > processed report with null values should not come if i cancel
> > > report,
.

Quantcast