I have an acces db set up with some standard reports which can be called up from different forms depending on the user. Eventually this will be split up into different front/back applications for the different users, but, I'm looking for a shortcut so that I don't have to modify the reports in each application. The problem is this:
The reports have fields that I am populating from the Form, on the form, these are date fields that I am using as filters. Curently, the control source for the fields looks like this:
=[Forms]![frmMAIN]![StartDate]
Is there anyway that I can change this so that the date is pulled from whichever form is active, such as [frmAdmin] or [frmDataEntry]??

What if both forms are active?

I am not sure if the query can do this, but you can pass the code from the button that opens the form. You would do this on EACH form, and remove the criteria from the query of report.

Do you want to do that?

mmcdonal

Look it up at: http://wrox.books24x7.com

Currently, both forms could be active, but, currently, I am the only one in the db. Once I get all of the bugs worked out, I will split the db into several front ends where they will only have one form. I'm just looking for a way to keep from having to go into each report and edit that field, actually there are two of them, when i do that.
Plus, it would help if the fields work while I am doing demonstrations on Friday. I'm keeping it all together for that.

If I can pass a parameter or something using the button, that will work. The way it is currently set up, the control for that field is not in the query that the report is based on, it is just the control for that field.

Well, you are going to have to write code somewhere, unless you use the button wizard and tell it to link the report to the combo box.

Remember the maxim: "I write code so you don't have to." (See www.thinkgeek.com for other fun coding t-shirts)

The way you would do this with the button is:

'===============================
Dim sLink As String
Dim dtStart As Date
Dim sDoc As String

sDoc = "rptMyReport"

If IsNull(Me.StartDate) Or Me.StartDate = 0 Then
   MsgBox "Please select a start date.", vbInformation
   Exit Sub
Else
   dtStart = Me.StartDate
End If

sLink = "[Date] = #" & dtStart & "#"

DoCmd.OpenReport sDoc, , , sLink
'===============================

So you see... either this code, or add code to the report.

Don't forget to code the On No Data event on the reports.

mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks, I'm not very good at code yet, but I'm learning. How do I fit that in with the following scenario. This is the code for one of the buttons.

You didn't mention an end date. That is the code you need already complete. It looks like you can remove the code from your report since the button already passes the parameter for a start and end date from your TWO combo boxes.

It looks like you are using the code on your report to only DISPLAY the start date, not pass parameters. In that case, you are just going to have to recode the reports for the different forms.

HTH

mmcdonal

Look it up at: http://wrox.books24x7.com

Crap.

Oh well, I should only have to do it once for each report, for each front end.
Thank you for your help.

I'll get this code stuff eventually. I guess I should read a book. :)

No worries. Keep at it. Keep posting.

mmcdonal

Look it up at: http://wrox.books24x7.com