Choose Parameter combo box for report

[RobRay]

Hi, I would like to make the Choose Parameter box into a combo box, I've been trying to use a form to no avail so far, and if it does work, it still opens a Choose Parameter box, then opens the query as well as the report.
Here's the code I've been playing with, a.t.m. the Run my Report button does nothing, so I click cancel and then the Choose Parameter box pops up.


Option Compare Database
Private Sub Report_Close()
DoCmd.Close acForm, "ChooseForm"
End Sub
Private Sub Report_Open(Cancel As Integer)
' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True
' Open Sales By Category Dialog
DoCmd.OpenForm "ChoosePark", , , , , acDialog

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False
End Sub



I just want to choose from a drop down box and have the corresponding report appear with all of it's subreports referring to the same thing.

How can I do this?



Thanks!

[NTC]

Your post is unclear. Generically, a Report has a record source (table or query).

If you have multiple reports - then one could, as you suggest, set up a dropdown (combo or list box) to select which report to open; rather than having an Open button per report.

If you instead have a single report - but want it to report on differing data; which is common; then you have to vary the parameter of the underlying query record source so that the record source has differing data. This can also be done via a dropdown type control.

One should have a textbook, easily found at Amazon or any big book store that will have examples of a parameter query and calling parameters from form controls.

Hope this helps.

[RobRay]

Sorry about the clarity I'll try again:

I do have a single report, and I want it to report on differing data.
The trouble I am having is getting the underlying query recordsource to vary its data by a drop down box.

I've tried [Form]![ChoosePark]![ParkName] (in the query critera), Which should link to a form called ChoosePark, with a combobox called ParkName but when I click on the Run Report button I either get a blank report or nothing happens, so I'm not sure how to get the command button to work, any ideas?


I've almost got it now, only problem is the form won't auto close after the report is open...

[slave138]

If you want the form to close when the report loads you can try these to see if they work:

Set the OnLoad event for your Report to:

DoCmd.Close acForm, "frmYourFormName", acSaveNo

If that doesn't work you can try a timer by setting the OnTimer event of your form to the same code as above. Then use whatever event triggers the report to open from the form and add some code:

Me.Form.TimerInterval = x

Where 'x' is the time in miliseconds (1000 = 1 sec).

[Access_Blaster]

I just want to choose from a drop down box and have the corresponding report appear with all of it's subreports referring to the same thing.

How can I do this?

Thanks!

Is this what your trying to do? See sample.

[RobRay]

Thanks for the replys,

Slave: I'm using 2003, as far as I can see there is no "on load" event, just "on open" and I tried that code there, but no dice...

The second one I also couldn't get to work, although I did get an error telling me that Access can't find the macro DoCmd

The event that triggers the report to open is a command button, and that is where I put the time.


Access Blaster: Thanks, that's what I want, but I also want the form to close? Any more help on that front?

[Access_Blaster]

Ok now it closes.

[RobRay]

Awesome, thanks Access Blaster, got mine working now

[slave138]

Sample worked great for me and I was able to swap in the code to have it close the form in the report's OnLoad event without any problems. If he is getting errors for DoCmd, I'd say there is something bigger causing a problem...

UPDATE: Nevermind - looks like it's not a problem anymore.

[Access_Blaster]

Awesome, thanks Access Blaster, got mine working now

Glad it worked out for you.


Richard

[thbaig1]

Ok now it closes.

Hi Access_Blaster,
I have followed your code instructions, but for my db , it is giviing some OLE ,OLX server error.
Can you please help me out?

I have attached the db file and placed form at startup.