Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #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


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!

Reply With Quote
  #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

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:

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:

="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:

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"
        If [txtBeginningDate] > [txtEndingDate] Then
            MsgBox "Ending date must be greater than Beginning date."
            DoCmd.GoToControl "txtBeginningDate"
            Me.Visible = False
        End If
    End If
    Exit Sub

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

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:

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...



Reply With Quote
  #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

...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...


Reply With Quote
  #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

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:

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

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.


Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 04:31 AM.

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