More problems passing values to reports

[jtice01]

I'm building a report based off of multiple queries, which uses a single combo box selection on a form.

One of the items I'm having trouble with is displaying a single dollar amount for a particular project selection. The project selection takes place on the above mentioned form.

I've approached this three ways so far.
1) I built a query which used the combo box selection as the criteria for the search to pull back the value I wanted. This worked.
I then tried to directly place the query into report by means of a subreport. The trouble is, that when I do this, the subreport asks for a parameter (the projectname) that should have been provided by the above query. The project names are very complex and I don't want the user to need to type it in every time they wish to look at the report.

2)I passed an integer using the open.report argument to my report then tried to use it as the text box control sourse.
On the form:
Private Sub Combo84_Click()
Dim comboselect As Integer

If Not IsNull(Me.Combo84) Then
comboselect = Me.Combo84
End If
End sub

Private Sub Command56_Click()
DoCmd.OpenReport "rptBudgetReport", acViewReport, "", "", , OpenArgs:="comboselect"
End sub

On the report in the textbox control source:
=DLookUp("[GMbudgetdollar]","[tblProjects]","[ID]=" & [comboselect])


When I run the report, the text box is filled with "#Name?"


3) I made a public variant called comboselect. The on-click event for the combo box (combo84) on the above mentioned form contains this code:
If Not IsNull(Me.Combo84) Then
comboselect = Me.Combo84
End If

This is meant to fill the comboselect with the ID number of the row I want.
Next, on the report, I created the text box (Text117) then went to the VBA for the report and made the following code.

Private Sub On_Load()

Dim GMDollarAmount As Currency
Dim GMtableLookup As Integer
'Uses the global variable Comboselect to pull the value of the combobox84 selection from the frmProjectSelector and use it to find the ID number of the field I want from the tblProjects
GMtableLookup = DLookup("[ID]", "tblProjects", "comboselect")
GMDollarAmount = DLookup("[GMbudgetdollar]", "tblProjects", "[ID]=" & [GMtableLookup])
Me.Text117 = GMDollarAmount

End Sub

However, on activation of the report, the text117 box remains empty.

Probably something very simple I am missing. Any help would be appreciated.

 

[Ranman256]

Don't fill values in the report, make the query pull all the data.
The query can pull data from combos on the form.

 

[jtice01]

Don't fill values in the report, make the query pull all the data.
The query can pull data from combos on the form.

The query works on its own, but does not work when I attempt to use it as a sub report on the report body. It asks for a parameter.

Can you give me an example of what you are suggesting?

 

[JHB]

...
..2)I passed an integer using the open.report argument to my report then tried to use it as the text box control sourse.
On the form:
Private Sub Combo84_Click()
Dim comboselect As Integer

If Not IsNull(Me.Combo84) Then
comboselect = Me.Combo84
End If
End sub

No you do not! You declare a variable in the Sub and set it to some value, and then it is destroyed because the sub finish.

...
DoCmd.OpenReport "rptBudgetReport", acViewReport, "", "", , OpenArgs:="comboselect"

There are things in your way to code which is not correct.

1. In the above line, the text = 'comboselect' is transfered in the open argument, not the value of "comboselect".
2. No need for using "OpenArgs:=", you have it already in the parameter place for the open argument. You use "OpenArgs:=" if you omit the other parameters.
3. Why do you transfer something in the open argument, you don't use it in the report!

...
On the report in the textbox control source:
=DLookUp("[GMbudgetdollar]","[tblProjects]","[ID]=" & [comboselect])

When I run the report, the text box is filled with "#Name?"

Yes, because you don't have a control/field called "[comboselect]"

...
3) I made a public variant called comboselect. The on-click event for the combo box (combo84) on the above mentioned form contains this code:
If Not IsNull(Me.Combo84) Then
comboselect = Me.Combo84
End If

No you didn't, see my first comments.

...
Private Sub On_Load()

Dim GMDollarAmount As Currency
Dim GMtableLookup As Integer
'Uses the global variable Comboselect to pull the value of the combobox84 selection from the frmProjectSelector and use it to find the ID number of the field I want from the tblProjects
GMtableLookup = DLookup("[ID]", "tblProjects", "comboselect")
GMDollarAmount = DLookup("[GMbudgetdollar]", "tblProjects", "[ID]=" & [GMtableLookup])
Me.Text117 = GMDollarAmount

GMtableLookup = DLookup("[ID]", "tblProjects", "comboselect")

Here you try, (I say try, because the setup isn't correct, you're missing the fieldname to search in), to search in the table = "tblProjects" after the text "comboselect" and not the value of "comboselect".
What it should have been:

GMtableLookup = DLookup("[ID]", "tblProjects", "SomeFieldName=" &  Me.OpenArgs)