Results 1 to 9 of 9

Thread: cmbobox filter

  1. #1
    Join Date
    May 2009
    Posts
    72

    Unanswered: cmbobox filter

    Hi
    I have a problem regarding filtering of combobox

    I have a form with a sub form
    in the sub form, I have a combobox1 which shows customers
    when I select a customer from a combobox, related data is populated to 2 textboxes
    partnumber
    partname
    I have another combobox2 that shows prices for partnumbers
    what I want is..
    after I select the customer from combobox1, then when I go to the combobox2, it should show only those records related to partnumber textbox

    Hemanth

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    look up cascading combo boxes
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2009
    Posts
    72
    Hi
    any help on this
    I have a problem regarding filtering of combobox

    I have a form with a sub form
    in the sub form, I have a combobox1 which shows customers
    when I select a customer from a combobox, related data is populated to 2 textboxes
    defectpart
    defectpartname
    I have another combobox2 that shows prices for partnumbers
    what I want is..
    after I select the customer from combobox1, then when I go to the combobox2, it should show only those records related to partnumber textbox

    I have tried this on the query for combobox2 ---- [forms]![DEFECT PRODUCT REPAIR REPLACE SUB]![DEFECTPART]

    I have tried this also
    Private Sub combobox1_AfterUpdate()
    Me.combobox2.RowSource = Nz(DLookup("BOMSPRICE", "BOMSPARESPRICING", "BOMSPPARTNUMBER ='" & [DEFECTPART] & "'"), "")
    Me.combobox2.Requery
    End Sub
    no luck

    Hemanth
    Last edited by krishnanhemanth; 01-02-14 at 12:53.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    look up cascading combo boxes

    effectively you need to place some code behind the first combo box which then applies a filter or revised sql statement on the second combo box
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a rather complex but very easy to use solution.

    1. Import the class module Cls_CascadingCombos in your project.
    2. In the module of the form where you need cascading combos, create and initialize an instance of the class Cls_CascadingCombos (see attached database for the details).
    Attached Files Attached Files
    Have a nice day!

  6. #6
    Join Date
    May 2009
    Posts
    72
    hi
    this is the criteria I used in the query : [FORMS]![DEFECT PRODUCT REPAIR REPLACE SUB]![DEFECTPART]
    this is working as expected when I open the subform separately

    but when this form is opened along with the man form then its not working
    any ideas
    Hemanth

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by krishnanhemanth View Post
    hi
    this is the criteria I used in the query : [FORMS]![DEFECT PRODUCT REPAIR REPLACE SUB]![DEFECTPART]
    This is a reference to a control, not a criteria. A criteria has the following structure:
    Code:
    [Column] <Comparison operator> <Value>
    Ex. (litteral):
    Code:
    Tbl_Users.LastName Like 'S*'
    Or (reference):
    Code:
    Tbl_Users.LastName = [Forms]![Users]![LastName]
    Moreover, if [DEFECT PRODUCT REPAIR REPLACE SUB] is a subform (i.e. it is open in the SubForm/SubReport control of another form, is is not member of the Forms collection. You must use:
    Code:
    Forms!MainForm!SubFormControl!Form!Control
    see: Forms: Refer to Form and Subform properties and controls
    Have a nice day!

  8. #8
    Join Date
    May 2009
    Posts
    72
    exactly
    I used this
    [FORMS]![DEFECT PRODUCT REPAIR REPLACE].[Form]![DEFECT PRODUCT REPAIR REPLACE SUB]![DEFECTPART]
    had to refer the main form
    works like a charm
    thanks again

    another clarification
    in a combobox, can dlookup be used as a row source or shuld it be only a sql

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dlookup returns a single value, assuming a value can be identified for the specified criteria, if no row is found then dlookup retuns null.

    so I'd expect dlookup to be useless in a combo box, as its only ever going to reeturn a single value

    however you coudl alwasy do a bit of experimenting yourself and see what the results are. it will help improve your development and analytical skills
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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