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

    Adapt Code (2000)

    I have some code (that I got here) and I am trying to adapt it to other fields. There are 2 combo boxes on a form. According to what you select in one combo box, determines what is in the other combo box. The code I think that needs adjusted is as follows:
    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

    The first combo box used to contain SIC code and Description (now I want to chage it to Consultant). The other combo box had zip code and now I want to change it to Client Managers for Consultants. I can select a Consultant and I get the records for them but when I select the Client Manager it doesn't just show the Client Managers for that consultant. I am not sure about what the code ' Omit first " AND "
    strFilter = Mid(strFilter, 6) means. All the fields are strings but what does Mid(strFilter, 6) do?

    I hope this isn't too confusing. Thanks for your help.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adapt Code (2000)

    After the two first If conditions, the strFilter variable will contain :
    " AND [Mclname] = "XXX" AND [Rep_Last]="ZZZ""
    The purose of
    strFilter = Mid(strFilter, 6)
    is to remove the leading " AND " so strFilter will contain :
    "[Mclname] = "XXX" AND [Rep_Last]="ZZZ""
    Have you checked your field names and control names ?
    Do the combo's contain the strings and not an ID ?
    Put a breakpoint on the line
    MakeFilter = strFilter
    and go to the immediate window and type
    ? strFilter
    Did the result look like what you are searching ? (something like : [Mclname] = "XXX" AND [Rep_Last]="ZZZ")
    Francois

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

    Re: Adapt Code (2000)

    Before the instruction strFilter = Mid(strFilter, 6), strFilter will look like

    <code>" AND [Mclname]='Johnson' AND [Rep_Last]='Evans'"</code>

    You don't want the first " AND " in the end result, so you omit that by taking everything from the 6th character.

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

    Re: Adapt Code (2000)

    What could I be missing? When I choose a name from the first combo box, the form filters records for that consultant. Then when I open up the other combo box, instead of just the client managers for that consultant, I get all the client managers. In the query for the Client Manager, I have [forms]![NameofFrm]![CboConsultant] and in the criteria I have [McLname] or is Null. I am doing exactly what the form does that I am trying to adapt to.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adapt Code (2000)

    In the after update event of the Consultant combo, do you requery the Client Manager combo ?
    Francois

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

    Re: Adapt Code (2000)

    Yes -
    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

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adapt Code (2000)

    In the FilterMe sub, are you doing anything else than setting the filter to the form ?
    Francois

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

    Re: Adapt Code (2000)

    Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>

    The form I am trying to adapt is in <post#=478608>post 478608</post#>. This is what the specifications are that I was given:
    each user (Consultant) will be assigned to multiple client managers. The function will allow each user to view a clients records by filtering their respective client managers. The user will select a Client Manager from a drop down list. Also, the user will select a Status from a drop down list that includes "All" as a selection. Furthermore, as an additional filter, the user will input a date or date range into ePlatform Effective Date.

    I am stumped as to how to do this. I was thinking a parameter query but they want dropdown lists. That is when I thought of adapting the database (478608). Any help would be very much appreciated. I need specifics. Thanks

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

    Re: Adapt Code (2000)

    I figured out how to make the form work. The reason the second dropdown list wasn't working was because of the way I set up my query. I fixed that and that works. The way I handled the next 2 filters was to make the report that is part of the filter form be a parameter query asking for status and then a date range. That works sort of. After I choose a consultant, then choose a Client manager, I hit the print button. It asks for status. If I type a status in, it fills in the status, If I hit enter at Status, the report comes up with the Status field blank. When I run the query by itself and I hit enter at status , it shows in the query. Why wouldn't it show in the report that uses that query? Any ideas?

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adapt Code (2000)

    Hard to say without seeing the db. Can you post a stripped version ?
    Francois

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

    Re: Adapt Code (2000)

    I figured it out. The field is a dropdown list. I am not sure it that had anything to do with it not showing on the report. In the query I just made another column and called it Status2:[status]. I put Status2 in the report and it shows the field when I hit enter at status. It may not be the best approach but it works.

Posting Permissions

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