Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DoCmd.OpenQuery? (A2K)

    OK, every village has one and I don
    Cheers,
    Andy

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

    Re: DoCmd.OpenQuery? (A2K)

    What you want to do is to limit the records displayed in the form. There are basically two approaches:
    1) Apply a filter to the form, or
    2) Change the record source of the form on the fly.
    There is no need to use DoCmd.RunSQL here - that is used to execute an action query (such as an update query or a delete query).

    Let's say (for illustration purposes) that you have a combo box cboSelect from which the user can select a value.

    For approach 1), you could use code like this.

    Private Sub cmdFilter_Click()
    Me.Filter = "F00_Function = " & Me.cboSelect
    Me.FilterOn = True
    End Sub

    For approach 2), you could use

    Private Sub cmdFilter_Click()
    Me.RecordSource = "SELECT * FROM [queryname] WHERE F00_Function = " & Me.cboSelect
    End Sub

    If you want to be able to select multiple values, you could use a multi-select list box lstSelect instead of a combo box. Code for approach 1) could look like this:

    Private Sub cmdFilter_Click()
    Dim varItem As Variant
    Dim strWhere As String
    For Each varItem In Me.lstSelect.ItemsSelected
    strWhere = strWhere & ", " & Me.lstSelect.ItemData(varItem)
    Next
    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 3)
    strWhere = "F00_Function In (" & strWhere & ")"
    Me.Filter = strWhere
    Me.FilterOn = True
    End If
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DoCmd.OpenQuery? (A2K)

    Hans,

    Thanks again for the quick reply. I guess even the simplest of questions can evoke an elaborate answer. I'm sure that I'm not the only one that will benefit from your answer. I'll be working on this later tonight and will get back to you if I have any problems or further questions. Further questions will probably be in the area of how to apply all of this to selecting reports. But, no questions until I go searching first.

    Thanks again,
    Cheers,
    Andy

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DoCmd.OpenQuery? (A2K)

    Hans,

    First, thanks again for the help with my problem yesterday.

    Brief background about the form that I
    Cheers,
    Andy

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

    Re: DoCmd.OpenQuery? (A2K)

    A list box has a property Multi Select (in the Other tab of the Properties window). The default setting is None, meaning that you cannot select multiple items. To enable selecting more than one item, set Multi Select to one of the other two options:

    Simple means that clicking on an item selects or deselects it.

    Extended means that you can select items the same way you select files in Windows Explorer:
    - Click and drag to select a range of items.
    - Click on one item, then Shift+click on another one to select the entire range in between.
    - Ctrl+click selects or deselects individual items.

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DoCmd.OpenQuery? (A2K)

    Hans, like I said at the front of this Post, "OK, every village has one and I don
    Cheers,
    Andy

Posting Permissions

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