Parameter thru Form Dialog Box for REPORT

[Sandy]

Getting Run time error in code : Run Time Error '2467' the expression u have
entered refers to an object that is closed or does'nt exist.

Set oAccessObject = CurrentProject.AllForms(strFormName)

when we open report..

(Am working on MDB file)
Am trying to build parameter form which accepts value and then process
reports. This i have done as per lessons in MS Access Coding where codes have
been writen in form/report and module created.

Following are the codes :

Module Level: Module created by name of Report Parameter
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 Datasheet 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


Under Report "Test Appl" following codes defined in open and close event

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 Daily Report", , , , , acDialog
'Cancel Report if user clicked the Cancel Button
If IsLoaded("Test Appl") = False Then Cancel = True

'Set the public variable to false to indicate the
' Open event is completed
bInReportOpenEvent = False
End Sub


In Form "Options Rpt Daily Report" following codes defined in ok, cancel and
open form event

Option Compare Database


Private Sub Command11_Click()
DoCmd.Close
End Sub

Private Sub Form_Open(Cancel As Integer)
If Not bInReportOpenEvent Then
'IF we re not called from the Report
MsgBox "For use from the Daily Report Only", vbOKOnly
Cancel = True
End If
Form_Open_Exit:
Exit Sub
End Sub
Private Sub Command10_Click()
Me.Visible = False
End Sub

Report is liked to query "Qry Rpt App" where parameters are mentioned and
these parameters are linked with above form

 

[Jeff Boyce]

Sandy

If you are using a form to "gather" criteria, and a query that looks to that
form for its criteria, and a report that is based on the query, I don't
understand why you are creating an Access Object.

 

[Sandy]

Its working now! I have given report name instead of form name in below code

If IsLoaded("Test Appl") = False Then Cancel = True

The file reference was wrong. It should have been "Options Rpt Daily Report"

Thanks

 

[Sandy]

I have one more report.
This report has similar functionality as below but with following changes:
Report is based on table
Table gets updated by 2 queries : Clear Query and Update Query.
This should get evoked on report open event.
So i have added following codes just below following code as mentioned under :

Report open event>>

'Open Daily Report 'This was already there below"
DoCmd.OpenForm "Options Rpt Daily Report", , , , , acDialog


'Open Clear Query'" Have added"
DoCmd.OpenQuery "Air Summary Qry Clear", , acEdit

'Open Air Summary Inv Qry' "Have added--This also has parameter linked to
options form
"
DoCmd.OpenQuery "Air Summary Inv Qry", , acEdit

But problem arises, if i cancel report in report parameter form mode by
pressing cancel button, Report cancels but parameter from update query, pops
up and if i cancel those, it asks for debug.

Please advice are problems with codes or need to add something more to
it.

 

[Jeff Boyce]

Sandy

It sounds like you are saying that your code is doing what you told it to!

If you want to cancel the overall procedure if the report is cancelled,
you'll need to include error handling to know when the report was cancelled,
then bypass (i.e., If ... Else ...) the rest.

 

[Sandy]

I would request for more step by step notes on it. I will elaborate further
on it :

Problem arises when i cancel report. IF the report was based on one table or
query, then yours first suggestion works perfectly fine.
But when my report requires 2 queries (append and clear) to run, it creates
problem if i cancel report. If i press cancel button in report, append query
is not disabled and it asks for parameter "Forms![Report Options
Flight]![Opening Date]" and if i cancel them, it gives error. (Ask for
debug). How should i put codes that append query dont ask for inputs, if i
cancel report. When the report is open, parameter form gets linked to append
querty parameter inputs, (as have given command in open report event, to run
append query) but when i close parameter form, append query does'nt get
disabled.. Please advice sir>>

 

[Jeff Boyce]

Sandy

Please re-read my previous response.

The generic idea is (the following is untested aircode--your syntax may
vary):

<your "launch report" code starts here>
On Error GoTo ErrorHandler

DoCmd.OpenReport "YourReport",...

<your delete & append query code starts here>

ExitHere:
Exit Sub

ErrorHandler:
If Err.Number = #### Then 'use error number from your "cancelled"
message here
Resume ExitHere
ElseIf Err.Number = 9999 Then 'if you want to test for another error
number,...
'do something else
Else
'do something else if any other error
End If

If you are not familiar with error handling, error codes, or VBA code,
you'll probably need to get some practice with them!

Good luck!

Jeff Boyce
<Office/Access MVP>

I would request for more step by step notes on it. I will elaborate further
on it :

Problem arises when i cancel report. IF the report was based on one table or
query, then yours first suggestion works perfectly fine.
But when my report requires 2 queries (append and clear) to run, it creates
problem if i cancel report. If i press cancel button in report, append query
is not disabled and it asks for parameter "Forms![Report Options
Flight]![Opening Date]" and if i cancel them, it gives error. (Ask for
debug). How should i put codes that append query dont ask for inputs, if i
cancel report. When the report is open, parameter form gets linked to append
querty parameter inputs, (as have given command in open report event, to run
append query) but when i close parameter form, append query does'nt get
disabled.. Please advice sir>>

Sandy

It sounds like you are saying that your code is doing what you told it to!

If you want to cancel the overall procedure if the report is cancelled,
you'll need to include error handling to know when the report was cancelled,
then bypass (i.e., If ... Else ...) the rest.

--
Regards

Jeff Boyce
<Office/Access MVP>

query,
pops below
code expression
u have mentioned
and

 

[Sandy]

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, It
should'nt do anything..

 

[Sandy]

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, It
should'nt do anything..

 

[Jeff Boyce]

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...

 

[Sandy]

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]

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."

 

[Sandy]

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

 

[Jeff Boyce]

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?

 

[Sandy]

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 Datasheet 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]

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>

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 Datasheet 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.

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>

Summary
Inv data
into
------------------------------------------------------------------------ my
mind to
open a to
the describe
what What
this more
on in
ok,

 

[Sandy]

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..

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>

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 Datasheet 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.

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>

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.


:

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>

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



:

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>

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,