Cycling through alphabet list

[Stu_J]

Hi, I am new to this forum. I have been using Access for some time, but mostly I am self-taught.

A colleague of mine created a phone book database, and I refined the user form for this using VBA code.

The form orders the recordset into alphabetical order (of the surnames) every time it opens.

The user can type in the surname they want, and the form will automatically update with the information for that person. Because of problems that occured after this, I have also replaced the record navigation buttons with my own VB code.


Private Sub Next_Item_Click()

DoCmd.GoToRecord , , acNext

Combo25.SetFocus
If Combo25.Text = Surname Then GoTo Skip_Command
Combo25.Text = Surname

End Sub



Private Sub Prev_Item_Click()

DoCmd.GoToRecord , , acPrevious

Combo25.SetFocus
If Combo25.Text = Surname Then GoTo Skip_Command
Combo25.Text = Surname

End Sub


Unfortunately, the system has one small bug in it. There are two people with the same surname in the phone list, and when the user cycles backwards through the list, it skips one of those two people.

Can anyone suggest anything? Or will we just have to fire one these awkward people?

(ps I am using Access97 for the record)

 

[BLeslie88]

Well one possible solution may be to reference 2 columns but only display one... Look up values by a query... I suppose these values are stored in a table? If so each person should have a assigned ID? If this is the case you can set-up the combo box as follows:

Column Count: 2
Column widths : 0";1"

Bound Column : 1
Row Source : SELECT [tblName].[NameID] AS NameID, [tblName].[Name] AS Name FROM tblName;

Hope this helps...

 

[Stu_J]

Unfortunately, the table does not have an index field for each record. I don't think that this would help, as I need to cycle through the recordset in alphabetical order of the last name.

I did try to adapt the properties to use the first name of the individual, like so:

Column Count: 2
Column Widths: 2.54cm;0cm

Row Source: SELECT [Phone List].[Surname] AS Surname, [Phone List].[Name] AS Name FROM [Phone List];


But it still skips the second "Brown" when it cycles backwards.

 

[cogent1]

If you don't have some unique identifier such as a key field, problems are inevitable. You have to create a unique record somehow. Try concatenating First and Last names in your query and using that to search. If two people have the same complete name, you're still stymied though. Can't you add an index field to your table? That would solve the problem.