Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Filtering a form by Combo Box

Discussion in 'Information Technology' started by SCHNYDES, Jul 28, 2006.

  1. SCHNYDES

    SCHNYDES
    Expand Collapse
    Guest

    Hi all,

    I'm looking to filter a form by an option in a drop down. The access wizard
    for this does little to help my needs. I'm using access 2000.

    The drop down contains 2 values, Contract and Date.

    The combo list is bound to qryselectcontract. The form is bound to
    qrycontract.

    I need my form to filter via the 2 values(contract and date) chosen from the
    drop down. How do I make this happen? Thanks.
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    A combo box can only have 1 bound field, so that when you refer to the combo
    box, the most you can get is a single value. However, you can refer to other
    fields in the combo box using the Column collection.

    In the combo box's AfterUpdate event, try code like:

    Private Sub MyCombo_AfterUpdate()

    Dim strFilter As String

    strFilter = "Contract = " & Me.MyCombo & " And " & _
    "[Date] = " & Format(Me.MyCombo.Column(1), "\#mm\/dd\/yyyy\#")

    Me.Filter = strFilter
    Me.FilterOn = True

    End If

    This assumes that the Bound Field property for the combo box is set to 1,
    and that Contract is a numeric field. If Contract is a text field, change to

    strFilter = "Contract = " & Chr$(34) & Me.MyCombo & Chr$(34) & " And " &
    _
    "[Date] = " & Format(Me.MyCombo.Column(1), "\#mm\/dd\/yyyy\#")

    Note that the Column collection starts numbering at 0: Column(1) refers to
    the second column in the combo box.


    BTW, I'd strongly recommend renaming your table field from Date. That's a
    reserved word, and should never be used for your own purposes. If you cannot
    (or will not) rename it, at least enclose it in square brackets as in my
    sample above.


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "SCHNYDES" <SCHNYDES@discussions.microsoft.com> wrote in message
    news:AA08A9EA-AA92-44DD-85D2-0D1134AAA925@microsoft.com...
    > Hi all,
    >
    > I'm looking to filter a form by an option in a drop down. The access

    wizard
    > for this does little to help my needs. I'm using access 2000.
    >
    > The drop down contains 2 values, Contract and Date.
    >
    > The combo list is bound to qryselectcontract. The form is bound to
    > qrycontract.
    >
    > I need my form to filter via the 2 values(contract and date) chosen from

    the
    > drop down. How do I make this happen? Thanks.
     
  4. strive4peace

    strive4peace
    Expand Collapse
    Guest

    on the AfterUpdate event of your combo

    if isnull(me.activecontrol) then
    me.filteron = false
    else
    me.filter = _
    "Contract_fieldname='" _
    & me.combo_controlname _
    & "' AND Date_fieldname =#" _
    & me.combo_controlname.column(1) _
    & "#"
    me.filteron = true
    endif
    me.requery

    I am assuming:
    1. date is in the second column of your combo
    2. Contract is a text field

    Warm Regards,
    Crystal
    MVP Microsoft Access

    remote programming and training
    strive4peace2006 at yahoo.com
    *
    Have an awesome day ;)


    SCHNYDES wrote:
    > Hi all,
    >
    > I'm looking to filter a form by an option in a drop down. The access wizard
    > for this does little to help my needs. I'm using access 2000.
    >
    > The drop down contains 2 values, Contract and Date.
    >
    > The combo list is bound to qryselectcontract. The form is bound to
    > qrycontract.
    >
    > I need my form to filter via the 2 values(contract and date) chosen from the
    > drop down. How do I make this happen? Thanks.
     

Share This Page