Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 22nd, 2008, 03:39 PM
Authorized User
 
Join Date: Mar 2008
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Date on report called from multiple forms

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]??

 
Old April 23rd, 2008, 06:17 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old April 23rd, 2008, 08:15 AM
Authorized User
 
Join Date: Mar 2008
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old April 23rd, 2008, 08:40 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old April 23rd, 2008, 09:13 AM
Authorized User
 
Join Date: Mar 2008
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Code:
Private Sub PVCProdRptBtnA_Click()
If IsNull(Me.StartDate) Or IsNull(Me.EndDate) Then
        MsgBox "Please enter dates to include in report"
    Else
Dim sLink As String
sLink = "[PDate] Between #" & Format(Me.StartDate, "mm\/dd\/yyyy") & "# And #" & Format(Me.EndDate, "mm\/dd\/yyyy") & "# And [Shift]='A'"
DoCmd.OpenReport "rptPVCProdA", acViewReport, , sLink
End If
End Sub
 
Old April 23rd, 2008, 09:17 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old April 23rd, 2008, 09:21 AM
Authorized User
 
Join Date: Mar 2008
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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. :)
 
Old April 23rd, 2008, 09:24 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

No worries. Keep at it. Keep posting.

mmcdonal

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
crystal report called from vbscript Seeker55 Crystal Reports 2 September 18th, 2006 03:02 PM
Button Handler Being called multiple times ExDb VB Databases Basics 1 January 22nd, 2006 07:32 PM
Multiple forms? Thurston VB.NET 2002/2003 Basics 5 November 18th, 2005 05:14 PM
multiple forms johanyu VB.NET 2002/2003 Basics 2 October 10th, 2004 12:35 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.