Consulting

Results 1 to 5 of 5

Thread: Saved Queries vs ComboBox

  1. #1
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location

    Saved Queries vs ComboBox

    Greetings..

    I have a form which populates through a ComboBox, except that the ComboBox is located on a different form and is closed during operation of the form. I have a cycle button so the user can click to get the next person in the list. I have used this kind of operation successfully in other situations. I have seen other codes for it on this forum as well. The idea goes like this...

    If Combo76.ListIndex <> Combo76.ListCount - 1 Then
    Combo76.ListIndex = Combo76.ListIndex + 1

    Of course you have to watch for the possibilities of reaching the end of the list box.

    But in my situation, my .ListIndex is calculating to -1 in the middle of the list. I am not sure why. But it may have to do with a clumsy workaround I used (and would rather not) since the combobox is not on the form. So I want to use a saved query instead of a combobox. So the simple question is this? Do saved queries have a similar internal listindex? What is the quick way to identify my current record in that query so that I can populate my form with the next or previous record?

    I can build a code and search this way: (only using a couple lines to provide idea)
    Set rst = db.OpenRecordset("SELECT * FROM MyCurrentList;")
    Do Until rst.EOF
    If rst.ID = myID Then...

    But is there a quicker way?

    Thanks
    GaryJ

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Gary, I have to ask this question, Why?
    Why have a separate search form?
    Why close it?
    Why not take advantage of a subform?
    Why do you need to increment the records either up or down?
    Why not have the search combo on the display form etc

  3. #3
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location
    Good questions...

    The form already has a subform. Both the form and the subform are continuous forms. So the need for space is important.
    The search form is larger than just the combo, as it also has six options to select from. i.e.:
    Search by First Name
    Search by Last Name
    Find all Incomplete Records
    etc..

    Selecting the option allows the user to locate a list of records. The incomplete records one is where a cycle through records effect becomes helpful, though they could also go back to the search and get the next one.

    Since it is on a different form, closing it after populating the record just makes sense - gets it out of the way.

    Thanks
    GaryJ


    Quote Originally Posted by OBP View Post
    Gary, I have to ask this question, Why?
    Why have a separate search form?
    Why close it?
    Why not take advantage of a subform?
    Why do you need to increment the records either up or down?
    Why not have the search combo on the display form etc
    Last edited by garyj; 05-28-2019 at 04:08 PM.

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, but have you considered using the search form itself to display the data using the Form's filter, this is very useful for displaying more than one record and using more than one search criteria.
    Have you seen Allen Browne's 2000 Search Form?

  5. #5
    VBAX Contributor
    Joined
    Apr 2014
    Posts
    109
    Location
    If by 'Allen Browne's Search Form' you mean this form: http://allenbrowne.com/ser-62.html it is a good idea, but it is specific to finding details within certain fields on the form. I am searching for all the donations made by a certain individual (form) and the subform is the donor's wishes / details of those donations. The fields of the form or subform are not searched in the criteria. Rather I search names from Contacts table to populate one form with all of John Doe's donations and donation details. I might want all the Doe's so as to select the correct John, by another contact field criteria. I suppose I do place that name in the header, so as to remember which donor the form now references, and I could use Allen's idea there. But that is only really for first name, last name, and city. One of my main reasons for preferring not to use Allen's idea is that it adds several more controls to the form, and I have seen so many forms done by the experts that cause my users to spend hours in being trained how to use the form. My forms are simple, require about 5 minutes of training. One of the methods utilized for that simplicity is to get rid of duplicate or unnecessary controls. The problem with this approach is that it can remove control from the user, and I try not to go that far.

    As to filters, it is a good idea and I should have thought of it myself -- [shakes my head now at self]. I haven't used form filters in a while, but this is also my volunteer way of helping an organization do their office work. Filters do answer the issues with "cycle to next". Perhaps I shall pull the items to cycle out of the current search list, and use an option group to display those right on the form.

    Thanks for your help.

Posting Permissions

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