Use a variable in a report for DLookup

[killerflappy]

Hi guys,

I have a report. In this report I show a Routenumber.
This routenr can be variable.

So I thought I make an inputbox in VBA at the open-Event of the report.
Then the inputbox ask for the routenumber, and I can use this variable in the report for Dsum, Dlookup and as tekst. The inputbox ask for the number, but I don't see how to use this variable.
I tried a messagebox right after the input and it shows a "0" every time.

Maybe it's better to do this in a query. I have the routenumber in a query, but I can't use it in the report.

Any suggestions? What is the right way?

 

[jdraw]

You might consider a Form on which you offer some options for the proposed report.

If user selects the Route number from a combo, or multi selects from a listbox,
then you could use the after update of the form to open your report ( a button) with appropriate openargs or recordsource.

So you use the selected options on the form to create/modify the OpenArgs of the report, or
use the selected options to modify the recordsource of the report, when user clicks the OpenReport button, the report will open with appropriate "values".

Good luck

 

[Solo712]

You might consider a Form on which you offer some options for the proposed report.

If user selects the Route number from a combo, or multi selects from a listbox,
then you could use the after update of the form to open your report ( a button) with appropriate openargs or recordsource.

So you use the selected options on the form to create/modify the OpenArgs of the report, or
use the selected options to modify the recordsource of the report, when user clicks the OpenReport button, the report will open with appropriate "values".

Good luck

In the alternative, you may pass the variable (that you extracted with the Input box) in as the "WHERE" argument for the report, e.g.

DoCmd.OpenReport "myReport", acViewPreview, , "[Route Number] = routenr"

where [Route Number] is the field that you want the report on and "routenr" is the extracted variable. If you go with jdraw's suggestion of a combobox (which I recommend) then you substitute the selected value.

Best,
Jiri

 

[arnelgp]

use Val() after the inputbox and change Recordsource of Report :

Private Sub Report_Open(Cancel As Integer)
dim var as Variant
var = Inputbox(....)
var = Val(var)
Me.Recordsource = "Select * From yourTableOrQuery Where RouteNumber=" & var
End Sub