Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Option Groups & Queries (2003)

    I would like to create a user form with check boxes that feed into a query that produces a report. The field in the underlying table is DateCompleted. I would like 3 items in the option group: DateCompleted "Open"; DateCompleted Is Null; DateCompleted Is Not Null.

    The idea is that the user selects - using a check box - whether they want information on all plant batches, current batches or batches that have been completed. This then feeds into a query which governs a report.

    I have produced the report (and its underlying query) and could produce three reports based on three queries. However, I feel there must be a much neater solution.

    As ever any and all assistance much appreciated.

    Many thanks
    Jill

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Option Groups & Queries (2003)

    From you descripion, I would guess that the choices are mutually exclusive. If that is correct, it would be better to use option buttons (aka radio buttons) instead of check boxes. The standard for Windows applications is to use check boxes if the user can select any combination of choices, and option buttons if the user can select only one choice.

    In an option group, each option button has an option value. If you use the Option Group Wizard, the default values are 1, 2, 3. The Value property of the group box is the option value of the selected option button. Say that you have a command button cmdReport on the form to open the report. You can use the value of the group box in the WhereCondition argument of DoCmd.OpenReport as follows - substitute the appropriate names:
    <code>
    Private Sub cmdReport_Click()
    Dim strWhere As String

    On Error GoTo ErrHandler

    Select Case Me.GroupBoxName.Value
    Case 1 ' Open
    strWhere = "DateCompleted = 'Open'"
    Case 2 ' Null
    strWhere = "DateCompleted Is Null"
    Case 3 ' Not Null
    strWhere = "DateCompleted Is Not Null"
    End Select

    DoCmd.OpenReport "rptMyReport", acViewPreview, , strWhere
    Exit Sub

    ErrHandler:
    If Err = 2501 Then
    ' Report canceled - ignore
    Else
    ' Display error message
    MsgBox Err.Description, vbExclamation
    End If
    End Sub
    </code>
    Note: I have assumed that DateCompleted is a text field. Otherwise, I don't understand what DateCompleted "Open" means.

  3. #3
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option Groups & Queries (2003)

    Hi

    How I wish I could explain things properly!

    Your code works beautifully.... except for Case 1....

    I would like Case 1 to bring back all records regardless of what is in "DateCompleted"

    I have fiddled with the Case 1 strWhere statement but can't seem to hit on the right syntax.

    Sorry to be a pain
    Jill

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Option Groups & Queries (2003)

    Try the following:
    <pre>Private Sub cmdReport_Click()
    Dim strWhere As String

    On Error GoTo ErrHandler

    Select Case Me.GroupBoxName.Value
    Case 1 ' Open
    DoCmd.OpenReport "rptMyReport", acViewPreview
    Case 2 ' Null
    strWhere = "DateCompleted Is Null"
    DoCmd.OpenReport "rptMyReport", acViewPreview, , strWhere
    Case 3 ' Not Null
    strWhere = "DateCompleted Is Not Null"
    DoCmd.OpenReport "rptMyReport", acViewPreview, , strWhere
    End Select

    Exit Sub

    ErrHandler:
    If Err = 2501 Then
    ' Report canceled - ignore
    Else
    ' Display error message
    MsgBox Err.Description, vbExclamation
    End If
    End Sub
    </pre>


  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Option Groups & Queries (2003)

    Patt has already given you the solution (thanks, Patrick). Here is a variation:
    <code>
    Private Sub cmdReport_Click()
    Dim strWhere As String

    On Error GoTo ErrHandler

    Select Case Me.GroupBoxName.Value
    Case 1 ' All - no filter
    strWhere = ""
    Case 2 ' Null
    strWhere = "DateCompleted Is Null"
    Case 3 ' Not Null
    strWhere = "DateCompleted Is Not Null"
    End Select

    DoCmd.OpenReport "rptMyReport", acViewPreview, , strWhere
    Exit Sub

    ErrHandler:
    If Err = 2501 Then
    ' Report canceled - ignore
    Else
    ' Display error message
    MsgBox Err.Description, vbExclamation
    End If
    End Sub</code>

  6. #6
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option Groups & Queries (2003)

    Hi Pat

    Thanks very much for this. I have stuck with Hans suggestion because it only meant alterning one line. Your code is however useful to me as it helps me to understand how things work. I am saving everything people are kind enough to help me with so that I can use it for future reference.

  7. #7
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option Groups & Queries (2003)

    Hi Hans

    Thanks yet again. Code works perfectly now.

    Jill

Posting Permissions

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