Report with Form Criteria

[Jolted_Chicken]

Hi,

My report calls a query with criterias in a form
eg [Forms]![fmrMonthlyReports]![ComboBranch]

When the form closes, the information on the report disappears.

Is there any way of stopping this, like passing the parameters of the forms to some temp variable then to the query?

 

[Rich]

Toggle the form Visible property

 

[ListO]

Another approach would be to create a global variable. Before opening the report or closing the form, assign that value to the global variable, and have the report use the global variable rather than the value directly from the form.

 

[Jolted_Chicken]

Another approach would be to create a global variable. Before opening the report or closing the form, assign that value to the global variable, and have the report use the global variable rather than the value directly from the form.

And how can i do that?

I'm not very familar with VBA.

my guess is
In on_click field, select Code expression
then have something like
A string = [Forms]![fmrMonthlyReports].[comboMonth]

but what will i put in the query criteria?

 

[ListO]

1. Create a new module with the following:

Option Compare Database
Option Explicit
Public pPubStringName as String

2. Then add this function to the same (or any) module:

Public Function gGlblStringName() As String
   gGlblStringName=pPubStringName
End Function

Save and close the module(s).

3. On the entry form you're using, add this code to the OnExit event of the field you're entering this data from:

pPublicStringName = [Forms]![fmrMonthlyReports]![ComboBranch]

4. In the query's criteria, enter: =gGlblStringName() .

 

[Jolted_Chicken]

I tried the method above to create a module named gGlblStringName:

Option Compare Database
Option Explicit
Public pPubStringName As String

Public Function gGlblStringName() As String
   gGlblStringName = pPubStringName
End Function

Instead of using it in a query, i tried to use it in a form and report.
In the form, i have a option group with a set of radio buttons,
in the after_update field, i have:

Private Sub OptRptType_AfterUpdate()
Select Case Me.OptRptType.Value
Case 1
    pPublicStringName = "Monthly"
Case 2
    pPublicStringName = "Quarterly"
Case 3
    pPublicStringName = "Half - Yearly"
Case 4
    pPublicStringName = "Annual"
Case 5
    pPublicStringName = " "
End Select
End Sub

in the report, i tried a text box with control source: =gGlblStringName()

that didnt work, when opening the report, I get prompted with that field in a dialog box.

I then tried it with a Label and in the On_open field of the report

Private Sub Report_Open(Cancel As Integer)

Me.LabelRptType.Caption = gGlbStringName()
End Sub

this didnt work either. I get "Sub or Function not defined"

I am trying to get the user to be able to select what appears in the report. eg if the user select "Monthly reports" radio button, then the report will show Monthly Report

 

[ListO]

You're almost there.

I thought you were using the global variable in a query. If you're just using the value in a form or report, it's simpler.

You may delete the function:
Public Function gGlblStringName()
and all that comes with it.

In the OnOpen event of the form (or report) in which you want the variable used, use the simpler implementation:

Private Sub Form_Open(Cancel As Integer)
 Me.LabelRptType.Caption = pPubStringName
End Sub

In anything except a query, the public variables act just like any others, except that they're available to the entire database. Somebody smarter than I can tell you why you have to go through the function thing to use them in queries.

Good luck.

-Curt

 

[Jolted_Chicken]

I was planning to use in a query, but the manager decided to changed things around.

Still cant get it to work. But shouldnt be far off.

Where to i declare the global variable?

Private Sub Report_Open(Cancel As Integer)
pPublicStringName = "Monthly"
Me.LabelRptType.Caption = pPublicStringName
End Sub

This works

but if

pPublicStringName = "Monthly"

is placed in another function doesnt work. In my case, it is in the case statements in the option group

 

[ListO]

You've got to dimension the public variable.

At the top of any module block, right after the Option Explicit statement, add:

Public pPubStringName as String

That creates the public string which you may use globally.

 

[Jolted_Chicken]

This is wot i have in my form module

Option Compare Database
Option Explicit
Public pPublicStringName1 As String

Private Sub CmdBranchPerf_Click()
On Error GoTo Err_CmdBranchPerf_Click
    
    Dim stDocName As String
    pPublicStringName1 = "Monthly"
    stDocName = "rptMinisterialPeriodicBranchPerformance"
    
    DoCmd.OpenReport stDocName, acPreview

Exit_CmdBranchPerf_Click:
    Exit Sub

Err_CmdBranchPerf_Click:
    MsgBox Err.Description
    Resume Exit_CmdBranchPerf_Click

End Sub

pPublicStringName1 is declared public here. It is assigned a value when the button is clicked (for testing)

in the report that is opened by that command button.

Option Compare Database

Private Sub Report_Open(Cancel As Integer)

Me.LabelRptType.Caption = pPublicStringName1
End Sub

This does not work and i totally stumped

 

[ListO]

You stump pretty easily.

I don't want to sound all mean and nasty or anything, but you could experiment a little on your own. Or you could look up public variables in the help files or in some book. Or you could re-read my original post and see if there's something you missed. Or you could use some of the debugging tools to see when or if a variable has a particular value.

I was inexplicit in my last post when I said you should put the dim statement at the top of any module. What I should have said was to put it at the top of any stand-alone module. It doesn't work if it's in a form module.

Go to the Database Window and click on the Module Tab.
Click NEW, an in the third line of the window type:

Public pPubStringName1 as String

Save the module with any unique name, and now the public variable should work for you.

 

[Pat Hartman]

The answer is actually quite simple. Remove the selection criteria from the query and just use the "where" argument of the OpenReport method.

 

[Jolted_Chicken]

finally got it working... thank you all
ListO:
I tried some debugging tools but none of them did wot I wanted.
The problem is, I havnt had any training in VB and no idea how it works in Access.
I know Ada/Java/C/C++ but not VB.

I borrowed this book off my colleague
Viescas, John, Running Microsoft Access 2000, Micosoft Press, 1999.
It's not very good. but it is very hard to find Access 2000 text books

Can anyone recommend a good Access book.

The answer is actually quite simple. Remove the selection criteria from the query and just use the "where" argument of the OpenReport method.

Unfortunately, its not that simple. The report calls a query which are depended on a couple of queries and those queries are fairly complex.