Report..Urgent!!

[jrdn1st]

Guys i created a simple Form with a button, Query & a Report, i want this button to call a Report that shows the Quert, how can i do so?

[jrdn1st]

P.S: i want the report to show the query depending on the value chosen in the form, i have created a relation between 2 tables, now i need to retrieve the data in the report according to what is chosen in the form.

[kdee]

Put a query inside the report by using a subreport tool (in report designer).

[lmawler]

Hi,

I have some reports that work based on form input, and I handle them this way.

On the form, I have code for a button that opens the report. I set a global variable for my sql query. (I can't figure out how to pass a variable effectively to an opening report - openargs works but only for one variable? But I digress.)

Let's say your form has a field called "inputText", and a button called "GoReport."

In the general section (on the form), I declare my global variable:

public mySQLString as String

In the code for clicking the button (on the form), I put this code:

mySQLString = "Select * from MyTable where MyField like '*" & me("inputText") & "'"

doCmd.openReport("myReport"), acViewPreview

Note that if you want the report to print and not show on the screen, you can get rid of acViewPreview.

Then, on the report, I put in this code on the Open Report event like this:

dim myLocalSql as string
myLocalSql = Form_myForm.mySQLString

Reports!myReport.RecordSource = myLocalSql

You don't have to use a separate variable here (i.e., you can just call the global variable directly, but I have better luck tracing errors this way.)

I'm not a very advanced Access person, so this isn't terribly elegant, but I had a great deal of trouble getting this stuff to work, so I'm happy to share if it will save you some of the beating your head against the wall that I went through.

:-)
Lea Ann

[NeoPa]

I too have found passing variables to a report to be very complicated.

To get around this I design the report to work in 2 ways :-
1. If the form is open - get info from there
2. If not - set your own defaults

In the code sample below, fraFrom is a frame on the form that is HOPED to be open

    On Error Resume Next
    'If next line fails then intFrom stays 0 - otherwise it will be > 0
    intFrom = Forms("frmName").fraFrom
    On Error GoTo 0
    If intFrom = 0 Then
        'Form not there - set variables to your own defaults
    Else
        'Form there - set variables from form
    End If
    'Use variables set above to determine course of program...
 

Another alternative is to set up access to global or static variables via a function interface.
It's a bit of a kludge but can be effective.
One drawback to this method is that it equally requires the data to be available at run time - therefore prepared previously.

I find the whole issue quite messy myself.

Just in case this can be helpful, I've included my function to do this but with no guarantees.

The calling code should call it with intSetGet set to 0 and a list of parameters to pass to a report.
The Report can use the variables by referencing as RptParms(n) where n refers to the nth parameter in the list.
Underlying queries can also reference them in the same way if required.

'RptParms sets and returns a set of parameters required by a report.
Public Function RptParms(intSetGet As Integer, _
                         ParamArray avarParams() As Variant) As Variant
    Static avarParms() As Variant
    Dim intIdx As Integer
 
    RptParms = 0
    If intSetGet = 0 Then
        intSetGet = UBound(avarParams) + 1 - LBound(avarParams)
        If intSetGet < 1 Then
            ReDim avarParms(1 To 1)
            avarParms(1) = "Error"
            Exit Function
        End If
        ReDim avarParms(1 To intSetGet)
        For intIdx = 1 To intSetGet
            avarParms(intIdx) = avarParams(intIdx - 1)
        Next intIdx
    Else
        'If outside bounds then it drops through and is set to "Error"
        On Error Resume Next
        If avarParms(intSetGet) = "Error" Then
            RptParms = "Error"                  'On Error
        Else
            RptParms = avarParms(intSetGet)
        End If
    End If
End Function
 

[PeterDavis]

Guys i created a simple Form with a button, Query & a Report, i want this button to call a Report that shows the Quert, how can i do so?

In the OnOpen Event of the report, use conditional code to change the recordSource of the report to alternative queries determined by your form