asked on

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

End Sub

Open in new window

Rey Obrero (Capricorn1)

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

place

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

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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

Sandra Smith

ASKER

MX, I was trying that route but I keep getting the below error.