Welcome to SPN

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

Sign Up Now!

combo box help!!

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

Tags:
  1. JG

    JG
    Expand Collapse
    Guest

    I have a form with 5 combo boxes which combined represents the employeeID
    and by using a parameter query im able to extract any data from the
    employeeID. Now what i want to do is to do the same function and but
    instead of typing a parameter i want to use the combo box and display the
    data in a subform or subreport. Heres the schema to the very best of my
    description.
    cmb1 - e
    - c
    - f
    cmb2 - CO
    - JS
    -AM
    cmb3 - 000
    - 001
    - 002
    cmb4 - XX
    - PF
    cmb5 - NO
    - AA

    an example employeeID would be cco000pfaa. now yesterday im able to query
    this by just using the Like feature in criterea (exp: "Like "*" & [Enter
    EmpID] & "*" ) and by typing CO and data would be extracted by employeeID
    with the value CO and would output it on a datasheet. I would like to do
    this same function using combo boxes and the output would display in my
    subform/subreport in my form. I hope for people who read this can
    understand what im talkin about. Any help would be great.
     
  2. Loading...


  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Sub ApplyFilterBasedOnCombos()
    ' Can Be assigned to a Button or to AfterUpdate Events (The Latter will
    result in a lot of requeries)
    ' This assumes Cmb1.. 5 But can easily be rewritten using the Tag Property
    or Multiple If's or an Array, or....

    Dim Flt As string
    Dim i As Long
    Dim cb AS Access.ComboBox
    Dim Sf AS Access.Form

    Set Sf = Me.SubForm.Form

    Flt = VBA.vbNullString
    For i = 1 To 5
    If Len(Me("cmb" & i).Value & VBA.vbNullString) =0 Then
    Flt = Flt & "*"
    Else
    Flt = Flt & Me("cmb" & i).Value
    End If
    Next ' i
    If VBA.Instr(Flt,"*") > 0 Then
    Flt = "EmployeeID Like '" & Flt & "'"
    Else
    Flt = "EmployeeID = '" & Flt & "'"
    End If

    Sf.Filter = Flt
    Sf.FilterOn = True
    Sf.Requery
    End Sub

    HTH

    Pieter





    "JG" <jgustilo@gmail.com> wrote in message
    news:OIYvL8imGHA.3880@TK2MSFTNGP02.phx.gbl...
    >I have a form with 5 combo boxes which combined represents the employeeID
    >and by using a parameter query im able to extract any data from the
    >employeeID. Now what i want to do is to do the same function and but
    >instead of typing a parameter i want to use the combo box and display the
    >data in a subform or subreport. Heres the schema to the very best of my
    >description.
    > cmb1 - e
    > - c
    > - f
    > cmb2 - CO
    > - JS
    > -AM
    > cmb3 - 000
    > - 001
    > - 002
    > cmb4 - XX
    > - PF
    > cmb5 - NO
    > - AA
    >
    > an example employeeID would be cco000pfaa. now yesterday im able to query
    > this by just using the Like feature in criterea (exp: "Like "*" & [Enter
    > EmpID] & "*" ) and by typing CO and data would be extracted by employeeID
    > with the value CO and would output it on a datasheet. I would like to do
    > this same function using combo boxes and the output would display in my
    > subform/subreport in my form. I hope for people who read this can
    > understand what im talkin about. Any help would be great.
    >
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Sub ApplyFilterBasedOnCombos()
    ' Can Be assigned to a Button or to AfterUpdate Events (The Latter will
    result in a lot of requeries)
    ' This assumes Cmb1.. 5 But can easily be rewritten using the Tag Property
    or Multiple If's or an Array, or....

    Dim Flt As string
    Dim i As Long
    Dim cb AS Access.ComboBox
    Dim Sf AS Access.Form

    Set Sf = Me.SubForm.Form

    Flt = VBA.vbNullString
    For i = 1 To 5
    If Len(Me("cmb" & i).Value & VBA.vbNullString) =0 Then
    Flt = Flt & "*"
    Else
    Flt = Flt & Me("cmb" & i).Value
    End If
    Next ' i
    If VBA.Instr(Flt,"*") > 0 Then
    Flt = "EmployeeID Like '" & Flt & "'"
    Else
    Flt = "EmployeeID = '" & Flt & "'"
    End If

    Sf.Filter = Flt
    Sf.FilterOn = True
    Sf.Requery
    End Sub

    HTH

    Pieter





    "JG" <jgustilo@gmail.com> wrote in message
    news:OIYvL8imGHA.3880@TK2MSFTNGP02.phx.gbl...
    >I have a form with 5 combo boxes which combined represents the employeeID
    >and by using a parameter query im able to extract any data from the
    >employeeID. Now what i want to do is to do the same function and but
    >instead of typing a parameter i want to use the combo box and display the
    >data in a subform or subreport. Heres the schema to the very best of my
    >description.
    > cmb1 - e
    > - c
    > - f
    > cmb2 - CO
    > - JS
    > -AM
    > cmb3 - 000
    > - 001
    > - 002
    > cmb4 - XX
    > - PF
    > cmb5 - NO
    > - AA
    >
    > an example employeeID would be cco000pfaa. now yesterday im able to query
    > this by just using the Like feature in criterea (exp: "Like "*" & [Enter
    > EmpID] & "*" ) and by typing CO and data would be extracted by employeeID
    > with the value CO and would output it on a datasheet. I would like to do
    > this same function using combo boxes and the output would display in my
    > subform/subreport in my form. I hope for people who read this can
    > understand what im talkin about. Any help would be great.
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4079 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     

Share This Page