Set Report control to date from opening form

Posted on 2011-10-11
Medium Priority
Last Modified: 2013-11-27
I have a form that the user can enter a date and I want this data to appear on the report, but the text control keeps coming up blank.
Private Sub lblCreateReport_Click()
'Weekly project update report primiarily used by management to
'track what is going on
Dim strUserID   As String
Dim dteStartDate    As Date
Dim strType As String
'Need to check is user is a manager as they are the only ones allowed to run report

strUserID = VBUserName()
strType = DLookup("Type", "tblBOTHTeamMEmbers", "UserID = '" & strUserID & "' ")
dteStartDate = Me.txtStartDate

If strType = "MGR" Then
    Call CreateProjectQuery("Yes")
    DoCmd.OpenReport "rptProjects_Updates", acPreview
    Reports!rptProjects_Updates.txtStartDate = dteStartDate
    DoCmd.Close acForm, Me.name
    MsgBox "You do not have permissions to run this report", vbOKOnly, "PERMISSION DENIED"
End If

End Sub

Open in new window

Question by:ssmith94015
  • 5
  • 2
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36950762
remove this declaration

Dim dteStartDate    As Date

from the click event and place in a regular module like this

Public dteStartDate    As Date

in the Reports Format event or Print event of the section where the textbox  "txtStartDate" is


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Reports!rptProjects_Updates.txtStartDate = dteStartDate
End Sub

LVL 75
ID: 36950770
Try this:
   Call CreateProjectQuery("Yes")
    DoCmd.OpenReport "rptProjects_Updates", acPreview,,,,dteStartDate ' OpenArgs
    DoCmd.Close acForm, Me.name

In the Report Open Event

Private Sub Report_Open (Cancel As Integer)
    Me.txtStartDate = Me.OpenArgs
End Sub

Author Comment

ID: 36950920
MX, I was trying that route but I keep getting the below error. Error Message Error Message
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Author Comment

ID: 36950924
Sorry, I embeded the image twice.

Author Comment

ID: 36950925
I get this mesage on the Report in the open event.

Author Comment

ID: 36950935
Do I need the pound sign around the date?
LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 36950940
Sorry ... do this instead.  Since you have a text box on the Form with that date (txtStartDate) ...just  put a text box on the report, and set the Control Source to this:



Author Closing Comment

ID: 36950949
Simplest and worked, thank you both.


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question