Combo box quick form search to skip through form records

[Soundhouse]

I have a combo box on a form that I use to allow for a quick search facility. The field that is being searched does not contain unique values, therefore I would like the user to be prompted to be able to find the next record and the next and the next.... etc... after the first record is found. This is the current code:

Private Sub cmbFindDOJobNo_AfterUpdate()


' Used to find the record that matches the control.
DoCmd.ShowAllRecords
Me!DOJobNo.SetFocus
DoCmd.FindRecord Me!CmbFindDoJobNo
Me!CmbFindDoJobNo.SetFocus
Me!CmbFindDoJobNo = ""
Me!CmbFindDoJobNo.Requery
End Sub

[isladogs]

One way of doing this is shown in the attached screenshot
Users enter a last name or first name (or just part of the name) as shown




A list of all matching records appears for users to select the record required.

However, in my example the search form is then closed.
So it does mean returning to the search form to get other records

[Soundhouse]

Thanks for your speedy reply ridders52. What I have at the moment is a similar thing in that the combo box is fully populated with all of the field entries, the user can type part or all of the value, or choose from the list. The problem is even when the user chooses the second choice of the same value, the user is sent to the 1st occurrence every time. See my screen shot: I need a way of trapping where I am in the combo box and then return the chosen record and not return the record of the first occurrence of the chosen value.

[orange]

Also, review this search article by Allen Browne for ideas.

[Soundhouse]

Thank you Orange, I shall review the article.

[isladogs]

One obvious problem is the fact that you have repeated values in your combo
Change the row source SQL to begin with SELECT DISTINCT instead of SELECT to ensure you have unique values

[Soundhouse]

Hi Ridders, the problem with using SELECT DISTINCT is that I do want the duplicates in the list because they are actually different records with different data associated to them. Although the screen shot doesn't show, I have included additional columns to the list so that the user can identify the differences between each record. I just wanted a very quick search tool based on a single key field so that the user didn't have to necessarily go to the Access find tool. For the most part the combo tool idea works well and is quick but it falls down where the key field has duplicates. I think I may have to rethink the idea.

[isladogs]

Suggest you do rethink it to avoid confusion .... or at the very least show more than one column in your combo dropdown

[Soundhouse]

Thanks for your input Ridders, much appreciated

[Missinglinq]

...The problem is even when the user chooses the second choice of the same value, the user is sent to the 1st occurrence every time...

I didn't wade thru Allen's article, which is no doubt accurate (but can be daunting to newbies) but the standard approach for this kind of search is to use FindFirst...and that's exactly what it is doing. With two occurrences of 11771, it will always take you to the first Record where 11771 appears!

You have to have a unique Field to search for. The usual hack is to concatenate, in this case, the CER No and another Field that would make the Record unique, and search on that Field.

A basic example would be to concatenate a last name and Social Security Number. With six Smiths in the database, Access will always retrieve the first Smith it encounters, regardless of which Smith you select...but searching on Smith plus their SSN, which will create a unique Field, it'll retrieve the one selected.

Linq ;0)>

[Soundhouse]

Hi Linq, an excellent shout. Thinking about it I do have a field built up of 3 key fields that I use as a sort field. I think I'll substitute my current search field for that one and that will solve my problem. Don't know why I didn't think of that earlier!...

Thanks for your time and the prompt

[Missinglinq]

Glad we could help!

Linq ;0)>

[Soundhouse]

Yep, huge thanks to all that helped.

Problem solved by substituting search field