Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Modify a Search Combo box! (Access 2000 >)

    Calling all Access Experts!

    I have a form containing course info such as : CourseDate, Subject, Company, etc.
    On the same form I created a combo box that contains the course date. When a user selects a date from the dropdown, it searches for and navigates to the course based on the date selected in the dropdown.
    I then noticed that if I have 2 or more courses running on the same date it only navigates to the 1st record that has the date.
    I then modified the combo to contain 3 columns. When the user clicks the dropdown arrow, it now shows:
    |CourseDate|Subject|Company|
    This will create uniqueness for each row in the combo,
    ie <pre>|20 May 2004|Excel|CompanyABC|
    |20 May 2004|Excel|CompanyBCD|
    |20 May 2004|Word|CompanyCDE|
    </pre>



    Based on the VBA code driving the combo, it still only sees and navigates to the 1st date that meets the selected value. The VBA code knows nothing about my modifications.
    The current code looks like this:
    <pre>Sub cboSearch_AfterUpdate()
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    Rs.FindFirst
    Regards,
    Rudi

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Modify a Search Combo box! (Access 2000 >)

    Try this

    Sub cboSearch_AfterUpdate()
    Dim strCriteria as string

    StrCriteria =
    Regards
    John



  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Modify a Search Combo box! (Access 2000 >)

    Hi John,
    I have this DB on my home PC. I will copy your suggestion and see if it works when I get home 2night. Thanx for your input.
    Just one thing. I will change the [company] to [course], as the 2nd column of the combo is the course. It is also a text field. I'm sure this is OK based on the code you gave?
    Thanx
    Regards,
    Rudi

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Modify a Search Combo box! (Access 2000 >)

    The counting of columns actually starts at zero , so column(2) is company, (column(1) is course, and column(0) is date)

    so if you want to find by course use:


    StrCriteria =
    Regards
    John



  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Modify a Search Combo box! (Access 2000 >)

    Noted.
    Looking forward to see if it will work. Will let u know!
    Thanx
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Modify a Search Combo box! (Access 2000 >)

    You're fighting the way the combobox matching actually works. Since the first column doesn't contain a unique value, there is no way for Access to know which specific item you are trying to use. You need unique values in the first column to get the combobox to work the way you expect it to or you need to add the values in the other fields to the criteria.
    Charlotte

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Modify a Search Combo box! (Access 2000 >)

    Hi Charlotte,
    I understand what you mean, and do agree with you, but it would be hard for a user to navigate to a certain course by the course ID!
    I still need to check Johns suggestion at home, but do you feel his suggestion of StrCriteria =
    Regards,
    Rudi

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Modify a Search Combo box! (Access 2000 >)

    If there are multiple courses for Word on the same date, then that won't work either. The first column of a combobox doesn't have to be visible. You can set it's width to zero so it doesn't show, and if you do it that way, you can select the specific course using the first column and save yourself a lot of effort.
    Charlotte

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Modify a Search Combo box! (Access 2000 >)

    If Johns method does not work, I will re-evaluate the combo box structure and attempt to work it the way you suggest Charlotte. Thanx 4 your input!
    Regards,
    Rudi

Posting Permissions

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