By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 969 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

Report..Urgent!!

jrdn1st
P: 4
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?
Oct 8 '06 #1

✓ answered by 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

Expand|Select|Wrap|Line Numbers
  1.     On Error Resume Next
  2.     'If next line fails then intFrom stays 0 - otherwise it will be > 0
  3.     intFrom = Forms("frmName").fraFrom
  4.     On Error GoTo 0
  5.     If intFrom = 0 Then
  6.         'Form not there - set variables to your own defaults
  7.     Else
  8.         'Form there - set variables from form
  9.     End If
  10.     'Use variables set above to determine course of program...
  11.  
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.

Expand|Select|Wrap|Line Numbers
  1. 'RptParms sets and returns a set of parameters required by a report.
  2. Public Function RptParms(intSetGet As Integer, _
  3.                          ParamArray avarParams() As Variant) As Variant
  4.     Static avarParms() As Variant
  5.     Dim intIdx As Integer
  6.  
  7.     RptParms = 0
  8.     If intSetGet = 0 Then
  9.         intSetGet = UBound(avarParams) + 1 - LBound(avarParams)
  10.         If intSetGet < 1 Then
  11.             ReDim avarParms(1 To 1)
  12.             avarParms(1) = "Error"
  13.             Exit Function
  14.         End If
  15.         ReDim avarParms(1 To intSetGet)
  16.         For intIdx = 1 To intSetGet
  17.             avarParms(intIdx) = avarParams(intIdx - 1)
  18.         Next intIdx
  19.     Else
  20.         'If outside bounds then it drops through and is set to "Error"
  21.         On Error Resume Next
  22.         If avarParms(intSetGet) = "Error" Then
  23.             RptParms = "Error"                  'On Error
  24.         Else
  25.             RptParms = avarParms(intSetGet)
  26.         End If
  27.     End If
  28. End Function
  29.  

Share this Question
Share on Google+
5 Replies


jrdn1st
P: 4
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.
Oct 8 '06 #2

P: 9
Put a query inside the report by using a subreport tool (in report designer).
Oct 18 '06 #3

P: 14
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
Oct 18 '06 #4

NeoPa
Expert Mod 15k+
P: 31,660
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

Expand|Select|Wrap|Line Numbers
  1.     On Error Resume Next
  2.     'If next line fails then intFrom stays 0 - otherwise it will be > 0
  3.     intFrom = Forms("frmName").fraFrom
  4.     On Error GoTo 0
  5.     If intFrom = 0 Then
  6.         'Form not there - set variables to your own defaults
  7.     Else
  8.         'Form there - set variables from form
  9.     End If
  10.     'Use variables set above to determine course of program...
  11.  
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.

Expand|Select|Wrap|Line Numbers
  1. 'RptParms sets and returns a set of parameters required by a report.
  2. Public Function RptParms(intSetGet As Integer, _
  3.                          ParamArray avarParams() As Variant) As Variant
  4.     Static avarParms() As Variant
  5.     Dim intIdx As Integer
  6.  
  7.     RptParms = 0
  8.     If intSetGet = 0 Then
  9.         intSetGet = UBound(avarParams) + 1 - LBound(avarParams)
  10.         If intSetGet < 1 Then
  11.             ReDim avarParms(1 To 1)
  12.             avarParms(1) = "Error"
  13.             Exit Function
  14.         End If
  15.         ReDim avarParms(1 To intSetGet)
  16.         For intIdx = 1 To intSetGet
  17.             avarParms(intIdx) = avarParams(intIdx - 1)
  18.         Next intIdx
  19.     Else
  20.         'If outside bounds then it drops through and is set to "Error"
  21.         On Error Resume Next
  22.         If avarParms(intSetGet) = "Error" Then
  23.             RptParms = "Error"                  'On Error
  24.         Else
  25.             RptParms = avarParms(intSetGet)
  26.         End If
  27.     End If
  28. End Function
  29.  
Oct 19 '06 #5

P: 8
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
Oct 19 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.