Forms to supply parameters to a report

  • Thread starter Thread starter Fie
  • Start date Start date
F

Fie

Hey... can anyone guide me to more info on how to do this. I found some
stuff on the Microsoft site but my database dont seem to like it...!!


thanks
fie
 
Dear Fiona:

A report run under the Jet (default for Access MDB) can reference controls
directly. Your report probably does, or can be made to run with a query.
That query can reference an open form and get the value of any control
(well, not list boxes or labels, lines, etc. but most of the controls that
would have a single value displayed, text boxes, combo boxes, even check
boxes).

In your query, you could put a filter to be, say, 7. Where you would put
the 7, put this instead:

[Forms]![FormName]![ControlName]

In the above, replace FormName with the EXACT name of the form where the
control is. Similarly, replace ControlName with the exact name of that
control. For the most part, that's it. Should just jump up and work.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison
 
hey...

With the code that i got from the Microsoft site... when i click on
report the form pops up which i want it to do, but when i click OK
after i have selcted Customer, Start & finsh dates there is an error
with the Module code...

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


It doesnt seem to like:

Function IsLoaded(ByVal strFormName As String) As Boolean
Dim oAccessObject As AccessObject


any ideas... could it be cause am trying to do this with Access 97????
 
Yes, it is because you are using Access 97.

Here is an Access 97 function that is equivalent to the IsLoaded function
you are attempting to use.


Public Function IsFormLoaded(strFormName As String) As Boolean
'John Spencer UMBC-CHPDM
'Last Update: April 7, 2000
'Purpose: 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
IsFormLoaded = True
End If
End If

End Function

You can change IsFormLoaded to IsLoaded (Two places) or change any
References to IsLoaded to IsFormLoaded.
 
I think i might be using the wrong code for the OnOpen/OnClose events
in the report prob code for newer versions can you help with code that
i need as it... what i have is


Form code:

Option Compare Database

Private Sub Cancel_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 Sales By Category Report only", vbOKOnly
Cancel = True
End If
Form_Open_Exit:
Exit Sub
End Sub

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



Report code:

Option Compare Database

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

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 "Sales By Category Dialog", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("Sales By Category Dialog") = False Then Cancel = True

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


and in the querys i have:

Criteria for School Name ----> [Forms]![MealUptake]![School]
Criteria for Date ---->> Between [Forms]![Meal Uptake]![StartDate] And
[Forms]![MealUptake]![EndDate]


any ideas... on the good side.. i aint getting that errror with the
module LoL... :-)
 
Try removing the form_open code for now. I think that the
bINReportOpenEvent is probably another custom function. I have no idea what
it does other than detect if the report opened the form for input.
 
Hey...

It seems to open the form when i run the report but when i click on OK
or Cancel it dont
seem to like the module code it highlights lines

Public Function IsFormLoaded(strFormName As String) As Boolean
and

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then

think there must be some sort of error... as the ifSysCmd code appears
in red..

any ideas
 
It could be that the newsreader wrapped lines.

This following (between the ====) all be on one line of code
=========================================================
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
=========================================================

I've rewritten the code slightly to include the line continuation character.
You can either use this or just fix the line.

Public Function IsFormLoaded(strFormName As String) As Boolean
'John Spencer UMBC-CHPDM
'Last Update: April 7, 2000
'Purpose: 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
IsFormLoaded = True
End If
End If

End Function
 
Back
Top