Results 1 to 2 of 2
Thread: Form Prompt for Report (2000)
2005-11-13, 04:02 #1
- Join Date
- Oct 2005
- Kansas City, Missouri
- Thanked 0 Times in 0 Posts
Form Prompt for Report (2000)
Hello all, I am having an issue with a form prompt that I created to open a report. On my form prompt I have a drop down combo for the user to select a employee, then a start and end date text fields. I want to make it where they can leave the employee field blank to include all Employees or if they select an employee then it will only pull over information for the employee they selected. I have the code down for the start and end dates but can figure out the code for the emplyee part. My click event procedure on the OK button has the following code:
Private Sub OK_Click()
On Error GoTo Err_OK_Click
Dim strReport As String 'Name of report to open.
Dim strfield As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "#mm/dd/yyyy#"
strReport = "R_SalesReportByDateRange"
strfield = "CallDate"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strfield & " < " & Format(Me.txtEndDate, conDateFormat)
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strfield & " > " & Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strfield & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
DoCmd.Close acForm, Me.Name
DoCmd.Close acForm, "F_Sales & Marketing", acSaveNo
DoCmd.Close acForm, "F_Sales & Marketing2", acSaveNo
Any suggestions would be most appreciated! The field names for the combo box on my prompt form is ESearch and the field name on my report is EName.
2005-11-13, 07:09 #2
- Join Date
- Jun 2002
- Mt Macedon, Victoria, Australia
- Thanked 45 Times in 44 Posts
Re: Form Prompt for Report (2000)
You just need to add this after the current lines that set strwhere according to the dates.
If not isnull(me![ComboEmployee]) then
Strwhere = strwhere & " and ( [EmplyeeID]=" & me![ComboEmployee] & ")"
This assumes that emplyees are identified by a numerical employeeID, which is in the combo box (but probably hidden in first column)
It also assumes that employeeID is in the query behind the report.Regards