rs.FindFirst to find last and first name

[Song Su]

My table has 2 separate field: Last, First
I want a combo to find name and allow people to type Last SPACE First. So my
recordsourse for the combo box is:
SELECT [Last] & " " & [First] AS name FROM tblMaster ORDER BY [Last] & " " &
[First];

How to connect LastSPACEFirst in rs.FindFirst in AfterUpdate event?

Private Sub cboFind_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last] = '" & Me![cboFind] & "'"
Me.Bookmark = rs.Bookmark
Last.SetFocus

End Sub

 

[Ken Sheridan]

Its not a good idea to rely on names to identify people. Names can be
duplicated. Instead use a unique numeric column, e.g. a ContactID autonumber
column. Set up the combo box like this:

RowSource: SELECT ContactID, First & " " & Last FROM tblMaster ORDER BY
Last, First;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Then search on the ContactID column:

rs.FindFirst "ContactID = " & Me!cboFind

Ken Sheridan
Stafford, England

 

[Song Su]

Got it! Thanks.

Why not base your form on a query that has the extra concantenated field
you
want and then do the FindFirst on that FullName field?

My table has 2 separate field: Last, First
I want a combo to find name and allow people to type Last SPACE First. So
my
recordsourse for the combo box is:
SELECT [Last] & " " & [First] AS name FROM tblMaster ORDER BY [Last] & " "
&
[First];

How to connect LastSPACEFirst in rs.FindFirst in AfterUpdate event?

Private Sub cboFind_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last] = '" & Me![cboFind] & "'"
Me.Bookmark = rs.Bookmark
Last.SetFocus

End Sub

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com