Create a form to supply parameters to a report

[Fie]

Hi,

I have followed the instrucation on how to Create a form to supply
parameters to a report from the microsoft website
(http://office.microsoft.com/en-us/assistance/HA011170771033.aspx?mode=print)
.. When you run the report it brings up the dialog box which I select
the critera but when I click on OK there is an error in the module
code. The code I have used is detailed below:


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


I think the problem is because I am using Access 97. Please can you
advise


Fie

 

[Brendan Reynolds]

The AccessObject and CurrentProject objects were both new in Access 2000. If
you look in the Access 97 version of Northwind, I believe there's an Access
97-compatible version of the IsLoaded function there.

 

[Fie]

Hi,

I found the module code for Access 97, Only prob now is that the code
that I have when the form is opened now displays an error. This is the
code I have

Private Sub Report_Open(Cancel As Integer)
' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open Sales By Category Dialog
DoCmd.OpenForm "frmKerbside", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("frmKerbside") = False Then Cancel = True

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


It doesnt seem to like bInReportOpenEvent = True

any ideas?

 

[Douglas J. Steele]

What's the error you're receiving? How have you declared blnReportOpenEvent?

 

[Fie]

Right what I have done is...

set up a form (frmKerbside) with 2 combo boxes
- Route Number (comb20)
- Collection Type (comb21)

OK button:

Private Sub cmdOk_Click()
Me.Visible = False
End Sub

Cancel button:

Private Sub cmdCancel_Click()
DoCmd.Close acForm, Me.Name
End Sub


On from Open;

Private Sub Form_Open(Cancel As Integer)
If Not bInReportOpenEvent Then
' If we're not called from the report
MsgBox "For use from the Kerbside Report only", vbOKOnly
Cancel = True
End If
Form_Open_Exit:
Exit Sub
End Sub


The Report (rptKerbside) has record source qryKerbside

OnOpen Event is:

Private Sub Report_Open(Cancel As Integer)
' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open Sales By Category Dialog
DoCmd.OpenForm "frmKerbside", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("frmKerbside") = False Then Cancel = True

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


On Close event:

Private Sub Report_Close()
DoCmd.Close acForm, "Sales By Category Dialog"
End Sub


The module code:

Option Compare Database
Option Explicit

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet
view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

End Function


I have a button on my Main Form (frmMain) that when clicked is set to
bring up the form frmKerbside it comes up with Complie error: variable
not defined

and the line Private Sub Report_Open(Cancel As Integer)
and
bInReportOpenEvent = True
are highlighted...

 

[Douglas J. Steele]

"variable not defined" means just that: you haven't declared
bInReportOpenEvent anywhere (using a Dim statement)

 

[Fie]

so how do i do that? Where do I put it?

 

[Douglas J. Steele]

Since you need it to be accessible from both the form and the report, create
a new module (not a class module, and not the code associated with a form or
report), and put

Dim bInReportOpenEvent As Boolean

in the module.

On the other hand, it would probably be much simpler to use the OpenArgs
parameter when you're opening the form:

DoCmd.OpenForm "frmKerbside", , , , , acDialog, "Legitimate"

and then check the value of OpenArgs in the report:

Private Sub Form_Open(Cancel As Integer)
If Me.OpenArgs & "" <> "Legitimate" Then
' If we're not called from the report
MsgBox "For use from the Kerbside Report only", vbOKOnly
Cancel = True
End If
Form_Open_Exit:
Exit Sub
End Sub

 

[Fie]

So am I putting


DoCmd.OpenForm "frmKerbside", , , , , acDialog, "Legitimate"
Private Sub Form_Open(Cancel As Integer)
If Me.OpenArgs & "" <> "Legitimate" Then
' If we're not called from the report
MsgBox "For use from the Kerbside Report only", vbOKOnly
Cancel = True
End If
Form_Open_Exit:
Exit Sub
End Sub


In the On Open Even in frmKerbside???

 

[Brendan Reynolds]

Look at the code you posted in your original post in this thread. It started
like this ...

Option Compare Database
Option Explicit
Public bInReportOpenEvent As Boolean ' Is report in the Open event?

Function IsLoaded(ByVal strFormName As String) As Boolean

Etc.

In replacing the version of the IsLoaded function that did not work with
Access 97, you also accidentally replaced the line above it, the line that
reads ...

Public bInReportOpenEvent As Boolean ' Is report in the Open event?

Put that line back in, below the 'Option Explicit' but above the line
beginning 'Function IsLoaded', just as you had it in the original code.

 

[Douglas J. Steele]

No, you're replacing whatever you have for the Form_Open event event with
the routine I gave, and you're replacing wherever you currently have the
OpenForm method call with the what I suggested.