Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
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
  #1 (permalink)  
Old October 3rd, 2006, 07:57 PM
Registered User
Points: 27, Level: 1
Points: 27, Level: 1 Points: 27, Level: 1 Points: 27, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2006
Location: , , Australia.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Putting user defined Dates into Reports

HI,

I have a query [CustomerOrdersByDate] that requests user input for [CustomerID], a 'Start Date' and an 'End Date' for the [OrderDate] field.

the Report works fine, however I'd like it to be able to take the Start & End dates the user has inputted when running the report, and make it appear in the Report header
e.g. Orders Received from [CustomerID], Between {user defined}[Enter Start Date] And {user defined}[Enter End Date].

I have added a text box to display the results and played around with a few different expressions, etc... unsuccessfully so far, and as I'm not up to working with VBA code, I would appreciate some help!


  #2 (permalink)  
Old October 4th, 2006, 09:06 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Use the reports Open event to invoke your Date Range selection dialog (complete with CustomerID, Beginning Date, and Ending Date text boxes) with something like:

Code:
Private Sub Report_Open(Cancel As Integer)
    DoCmd.OpenForm "frmReportDateRange", , , , , acDialog, "Set Date Range"
    If Not IsLoaded("frmReportDateRange") Then
        Cancel = True
    End If
End Sub
Then in the Control Source property of your textbox in your report header, place something like:

Code:
="Orders Recieverd from " & [Forms]![frmReportDateRange]![txtCustomerID] & “, “ & “Between “ &   [Forms]![frmReportDateRange]![txtBeginningDate] & " And " & [Forms]![frmReportDateRange]![txtEndingDate]
Have your Date Range selection form open as a modal form, place a report preview command button on it, then hide the form (while keeping it loaded) when the the preview button is clicked with something like:

Code:
Private Sub cmdPreview_Click()

On Error GoTo HandleErr

    If IsNull([txtBeginningDate]) Or IsNull([txtEndingDate]) Then
        MsgBox "You must enter both beginning and ending dates."
        DoCmd.GoToControl "txtBeginning Date"
    Else
        If [txtBeginningDate] > [txtEndingDate] Then
            MsgBox "Ending date must be greater than Beginning date."
            DoCmd.GoToControl "txtBeginningDate"
        Else
            Me.Visible = False
        End If
    End If
ExitHere:
    Exit Sub

HandleErr:
    Select Case Err
        Case Else
            MsgBox Err & ": " & Err.Description, vbCritical, _
             "Error in Form_frmReportDateRange.Preview_Click"
    End Select
    Resume ExitHere
    Resume
End Sub
Might also want to add a NoData event to the report in case no data is available for the selected date range:

Code:
Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There is no data for this report. Canceling report..."
    Cancel = -1
End Sub

Private Sub Report_Close()
    DoCmd.Close acForm, "frmReportDateRange"
End Sub
And here's a generic IsLoaded routine called by the reports Open event:

Code:
Function IsLoaded(ByVal strFormName As String) As Integer
 ' Returns True if the specified form is open in Form view or Datasheet view.

    Const conObjStateClosed = 0
    Const conDesignView = 0

    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
        If Forms(strFormName).CurrentView <> conDesignView Then
            IsLoaded = True
        End If
    End If

End Function
Somethin' like that...

HTH,

Bob

  #3 (permalink)  
Old October 4th, 2006, 09:12 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Quote:
quote:
...and as I'm not up to working with VBA code
Just noticed that bit. The heart of the matter is the expression:

="Orders Recieverd from " & [Forms]![frmReportDateRange]![txtCustomerID] & “, “ & “Between “ & [Forms]![frmReportDateRange]![txtBeginningDate] & " And " & [Forms]![frmReportDateRange]![txtEndingDate]

Place it in a text box's Control Source property, then just be sure you have the form with txtCustomerID, txtBeginningDate, txtEndingDate loaded so the expression can reference the text property of those controls. May the force be with you...

Bob




  #4 (permalink)  
Old October 4th, 2006, 09:35 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Also just dawned on me that you are trying to do this from a query. (Its late, long day). So have your query pick up its parameter values from the date range selection form also. Enter the following as criteria for your OrderDate field:

Code:
>=[Forms]![frmReportDateRange]![txtBeginningDate] And <=[Forms]![frmReportDateRange]![txtEndingDate]
and the following for the criteria for your CustomerID field:

Code:
[Forms]![frmReportDateRange]![txtCustomerID]
Then use the query as the record source of your form.

'Course the approach I'm driving at entails adding a date range selection dialog form to your app, and thats going to take a little VBA.

Bob



Similar Threads
Thread Thread Starter Forum Replies Last Post
ADODB.Connection user-defined type not defined Wall st Guru Excel VBA 2 March 26th, 2014 03:44 PM
User Control Not Defined? Ron Howerton Visual Studio 2005 1 March 29th, 2006 11:15 AM
User-defined type not defined (Icecream.mdb) dloren01 BOOK: Beginning Access VBA 0 June 22nd, 2005 10:36 PM
user defined templates clandestine XML 2 June 15th, 2005 08:27 AM
User Defined Function niravp SQL Server 2000 7 November 29th, 2004 02:18 PM





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