Combo Box that uses a last name field - 2 minor problems 1

[cimoli]

Attached if you don't mind is a shorty file (not all my real fields).
In the past, i had problems with a simple combo box for input because we have 240,000 customers.
So a consultant who is long gone, devised a method which worked. But i need to improve it.

It revolves around a Reservation file. The sfrmResPeople has a form field called txtNameLook.
I type in the person's lastname. then i tab over to the cusID field and a list appears of choices.
I pick one.

I have 2 problems.
1st When i am adding in a person to the sfrmResPeople, the person's name temporarily appears
on every previously entered row of txtNameLook.

2nd, i cannot control the sort of the input.
I didn't want a sort. I wanted the screen to always show the order entered in. but it presently sorts by CusID.
Don't want that. Want no sort in sfrmResPeople.

You can ignore the right side subform unless you see an error in my work.
Thank you.

The following is in the After Update of the form field named txtNameLook.

Private Sub txtNameLook_AfterUpdate()

'2011 Ray Jablonski
'used in the subform on frm-reservation to get a person's name

Dim sName As String, sSQL As String, varNames As Variant
On Error Resume Next

' Do nothing if txtNameLook is empty
If Len(Trim(Me.txtNameLook)) = 0 Then Exit Sub

' See if the user typed a blank (last name - blank - first name)
varNames = Split(Trim(Me.txtNameLook), " ")
' Build last name search on first part
sName = "(tblCustInfo.LastName LIKE '" & _
Replace(varNames(0), "'", "''") & "*')"
' If more than one string,
If UBound(varNames) > 0 Then
' Add a search on first
sName = sName & _
" AND (tblCustInfo.FirstName LIKE '" & _
Replace(varNames(1), "'", "''") & "*')"
End If

sSQL = "SELECT tblCustInfo.CusID, [LastName] & "", "" & [FirstName] & "", "" & [MiddleName] AS LFI, " & _
"tblCustInfo.DOB FROM tblCustInfo WHERE tblCustInfo.CUSTTYPE = 'CUS' " & _
"AND " & sName & _
"ORDER BY [LastName], [FirstName], [MiddleName], DOB"

Me.cboNameSearch.RowSource = sSQL

End Sub

 

[MajP]

1st When i am adding in a person to the sfrmResPeople, the person's name temporarily appears
on every previously entered row of txtNameLook

This is normal behavior of an unbound control in continous form view. There is nothing that relates that control to a row. The only solution is to redesign the interface. What exactly is that supposed to do?

I didn't want a sort. I wanted the screen to always show the order entered in.

Consider a table like a bag of marbles. You can pull them back out for display in any order you want (color, size, make...) but it is always your responsibility to pull them out the way you want. If you just dump out the bag do not expect them to come out in a certain order. If you want records to come out in order then either add an autonumber or timestamp to the table and sort the query by that field.

 

[cimoli]

MajP - as usual, you gave me some good tips. I will fix and amend my question after i fix.
You've got me thinking. Thanks so much. Will be back on this topic somewhat.