Set Report control to date from opening form

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,
    MsgBox "You do not have permissions to run this report", vbOKOnly, "PERMISSION DENIED"
End If

End Sub

Open in new window

Sandra SmithRetiredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
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

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try this:
   Call CreateProjectQuery("Yes")
    DoCmd.OpenReport "rptProjects_Updates", acPreview,,,,dteStartDate ' OpenArgs
    DoCmd.Close acForm,

In the Report Open Event

Private Sub Report_Open (Cancel As Integer)
    Me.txtStartDate = Me.OpenArgs
End Sub
Sandra SmithRetiredAuthor Commented:
MX, I was trying that route but I keep getting the below error. Error Message Error Message
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Sandra SmithRetiredAuthor Commented:
Sorry, I embeded the image twice.
Sandra SmithRetiredAuthor Commented:
I get this mesage on the Report in the open event.
Sandra SmithRetiredAuthor Commented:
Do I need the pound sign around the date?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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:



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sandra SmithRetiredAuthor Commented:
Simplest and worked, thank you both.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.