Cant display some records/records aren't in order

[cnstarz]

I have two problems with my database:



1) When you first load the "Personnel Security DB" form (first button on the switchboard), and you immediately hit the next/previous record buttons, all the records are in ascending order by last name, first name, middle initial. However, once you click on a name from the combo box and load a record, the order immediately changes to ascending order by SSAN (social security #). I can't figure out how to keep it in ascending order by last name, first name, middle initial.

2) Some people in my database have the same last name. If you chose a record from the combo box that shares the same last name with someone else, the form will only display the record of the person with the lowest-value social security #. For instance, if you click on the "BAH, CAT" record, the "BAH, MEOW" record will be displayed instead because the social security number is of a smaller value (111-11-1111 vs 777-77-7777). Again, I have no idea how to remedy this.

Any help is appreciated. Thanks!

[slave138]

I can't answer #2 (because I loathe dealing with listboxes - especially when they are using multiple columns) but for #1 the easiest way to do it is to add a query which returns everything from Main and then sorts the results the way you want (see screenshot). Set the query as the record source for the form instead of the table and it will keep its sorting.

[weekend00]

Question #2:
Did you write the code or you get from other people?
I find the code for click on the listbox as below:

Private Sub SearchNameList_Click()
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("Last")
    DoCmd.FindRecord Me!SearchNameList
End Sub

you find record with only the the last name as criteria, so it returns the first record with this last name.

The solution is you should find the SSN instead of [last] (last name).

[cnstarz]

thanks slave138, i based the form off the query and kept everything alphabetized.

weekend00, I found the code at another site. you're saying i should change it to:

 
Private Sub SearchNameList_Click()
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("SSAN")
    DoCmd.FindRecord Me!SearchNameList
End Sub

edit: i tried that but it actually broke the form. when you click on a record, the form won't update to display the record's data.

edit2: fyi, the form I'm referring to is MainQueryForm.

edit3: I really like the idea of the listbox changing as you type (like Google Suggestions), but if you know of a more efficient way of implementing this, please let me know.

[weekend00]

you need to search for SSN, not lastname, so you need to change to(code in red):

Private Sub SearchNameList_Click()
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("SSAN")
    DoCmd.FindRecord Me!SearchNameList.column(3)
End Sub

[cnstarz]

no bueno. i got this error when i added ".column(3)":

"The FindRecord action requires a Find What argument."

[weekend00]

Why did I got the right thing after I added .column(3)?

[cnstarz]

i get the same error with the one you attached.

"The FindRecord action requires a Find What argument." i'm on access 2007, i wonder if that's the difference.

EDIT: GREAT SUCCESS! did some soul searching on google, and found this, http://dbaspot.com/forums/ms-access/...de-2007-a.html

so, i changed the code to:

Private Sub SearchNameList_Click()
    Dim SearchNameListColumn3 As Variant
    SearchNameListColumn3 = Me!SearchNameList.Column(3)
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("SSAN")
    DoCmd.FindRecord SearchNameListColumn3
End Sub

for some reason, Access 2007 does not like "Me!SearchNameList.Column(3)". so, i definded "Me!SearchNameList.Column(3)" as "SearchNameListColumn3" and voila, all is good. i'm sure i'll be back for other questions cuz i'm learning as i'm building this. thanks for the help everyone!