How do I get the graph to grab data from the report instead of query?

[CTOROCK]

I have a report that has a graph in it. I have set a date range parameter in the query it is pulling it from. I also have a graph pulling info from the query, So when I run the report, it asks for the date range twice; once for the data and another for the graph. How can It only ask me once? Or how can I set the report to grab the data from the report during run-time instead of from the query?

 

[btaber]

You can try this, create a form that has the two date fields and a button. For this example, I called the form Dates, and the fields date1 and date2. For the button:

Private Sub Button_Click()
'You can add error checking or other things before this
Me.Visible = False
End Sub

In your report, add this vb code:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Dates", , , , , acDialog, "Dates"
If Not IsLoaded("Dates&quot Then
Cancel = True
End If
End Sub

In the report, set the graphs source start date to [Forms]![Dates]![date1] and the graphs end date to [Forms]![Dates]![date2]

in the source query, do the same for the start date and end date

Hope this helps!

 

[CTOROCK]

I really don't understand. What exactly does the button do, and how do you set the graph data to pull from the form. And how does the form know what dates I want, If i specify, won't i have to do it again for the query.. I'm sorry I'm a little confused!m ????

 

[btaber]

Sorry it took soo long to get back.

In the example, when the report loads (Report_Open), it calls the form as a dialog box to get the data from it. when you click the button, it hides the form, and the report loads, grabbing the start date (date1) and the end date (date2) from the form. The two dates are to set the between query, which the criteria on the query and the graph would look like Between [Forms]![Date]![date1] And [Forms]![Date]![date2] . the [Forms]![Date]![date1] is how the hidden form is referenced to get the dates in the fields. I also forgot something, the IsLoaded routine that checks if the form is open:

Public Function IsLoaded(ByVal strFormName As String) As Integer
' 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 hope this didn't confuse things more, the only other way to describe it would to send you and example database

 

[btaber]

Also, you don't open the form to run the report ,you open the report, and it calls the form, otherwise, it won't work