Results 1 to 2 of 2
  1. #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)
    End If
    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)
    End If
    End If

    ' 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
    Exit Sub

    MsgBox Err.Description
    Resume Exit_OK_Click
    End Sub

    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.

  2. #2
    Super Moderator
    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] & ")"

    end if

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts