Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form won't work (2000)

    I have a form and on this form are two combo boxes. The first combo box is(CboConsultant). When you choose a consultant, then the next combo box (CBOcm) is limited to just the Client Mgrs for that consultant. It used to work until I changed the query behind the form to a table. I created a table from the query that was behind the form to help with performance issues. I also changed the query (row source) for the CBOConsultant combo box to a table. It works fine until you select a client mgr and it gives you all the client mgrs not just the ones for that consultant. The code is as follows:

    Option Compare Database

    Private Sub cbocm_AfterUpdate()
    FilterMe
    End Sub

    Private Sub cboconsultant_AfterUpdate()
    FilterMe
    Me.cbocm.Requery
    End Sub

    Private Sub cmdClearFilter_GotFocus()
    Me.cboconsultant = Null
    Me.cbocm = Null
    Me.cbocm.Requery
    FilterMe
    End Sub

    Private Sub FilterMe()
    Dim strFilter As String
    strFilter = MakeFilter
    Me.Filter = strFilter
    Me.FilterOn = Not (strFilter = "")
    End Sub

    Function MakeFilter() As String
    If Not IsNull(Me.cboconsultant) Then
    strFilter = " AND [Mclname]=" & Chr(34) & Me.cboconsultant & Chr(34)
    End If
    If Not IsNull(Me.cbocm) Then
    strFilter = strFilter & " AND [Rep_Last]=" & Chr(34) & Me.cbocm & Chr(34)
    End If
    If Not strFilter = "" Then
    ' Omit first " AND "
    strFilter = Mid(strFilter, 6)
    End If
    MakeFilter = strFilter
    End Function


    What would make it stop working. I even went back to the way it used to be and it still doesn't work. Help!! I would like to change to the tables if that is still an option. Performance is much faster. I have a macro that deletes records from the table and then runs an append query to refresh the table. Any thoughts on this?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Form won't work (2000)

    The only query that matters is the rowsource for cboCM; it must reference cboConsultant. Then you have to remember to requery cboCM each time you make a selection in cboConsultant, and each time you move to a new record on the main form (put cboCM.Requery in Current event).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form won't work (2000)

    I added what you said to the on current event of the form but it still won't filter? (See attached). It should just give me what is listed on the screen but it gives me everything. I am puzzeled.

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form won't work (2000)

    Can you give us the SQL for the row source of cboCM?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form won't work (2000)

    SELECT qryforREP.Rep_Last
    FROM qryforREP
    WHERE ((([forms]![frmSearchsic]![cboconsultant])=[mclname] Or ([forms]![frmSearchsic]![cboconsultant]) Is Null));

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form won't work (2000)

    shouldn't it be WHERE (((qryforREP.mclname)=forms!frmsearchsic!cboconsul tant) or (forms!frmsearchsic!cboconsultant) Is Null); ?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

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

    Re: Form won't work (2000)

    Why?

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form won't work (2000)

    The way I read it, it looked to me as though she's trying to filter cboCM from cboConsultant. If mclname is the manager's name, wouldn't that filter the combo box?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

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

    Re: Form won't work (2000)

    Your and Linda's WHERE clauses are equivalent, as far as I can see.

  10. #10
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form won't work (2000)

    Hmm....so it doesn't matter which order they're in? I'll just assume from your post that's a yes, which is interesting. Learned something new today. If that's the case, Linda, could you post an example? I'm assuming your table looks something like the example below, and your desired result is to select "Bob" from cboConsultant and have cboCM only show Joe; Linda; Sharon....correct?


    <table border=2><td>Consultant</td><td>Manager</td><td>Bob</td><td>Joe</td><td>Bob</td><td>Linda</td><td>Bob</td><td>Sharon</td><td>Tim</td><td>Joe</td></table>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  11. #11
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Form won't work (2000)

    Hi Linda

    Sorry for stepping in so late in this thread. I have attached a demo database to this thread for you to look up. I have created three tables:

    tblConsultant
    tblManager
    tblJob

    1)there is a one to many relationship between tblConsultant and tblManager
    2) I have created a query from these two tables
    3) The criteria for the consultant is the combo on the form
    4) the manager combo is unbound but linked to the query
    5) After update of the consultant combo I refresh the form.

    have a look and see if this is what you want... it seems much simpler, but just as affective
    Jerry

Posting Permissions

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