Search last name and first name combo box

[Song Su]

My continiuse form has 2 separate fields: Last Name and First Name. I setup
a search combo box and I want user to type last name, (comma and space) and
continue to type first name to search correct record. My code only search
Last Name. How to do that? Thanks.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![cboFind] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

 

[smk23]

You need to define a field in your underlying query definition like
[LastName] & ", " & [FirstName] AS CompleteName and search on that field.

 

[Song Su]

Now I did [Last Name] & ", " & [First Name] in cboFind.

Now how to modify AfterUpdate event?

You need to define a field in your underlying query definition like
[LastName] & ", " & [FirstName] AS CompleteName and search on that field.
--
sam

My continiuse form has 2 separate fields: Last Name and First Name. I
setup
a search combo box and I want user to type last name, (comma and space)
and
continue to type first name to search correct record. My code only search
Last Name. How to do that? Thanks.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![cboFind] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

 

[Song Su]

Rick,

I tried your method.
column count 3, column width 0;0;1 (hide first 2 columns)
rowsource SELECT qryName.[Last Name], qryName.[First Name], [Last Name] & ",
" & [First Name] AS Expr1
FROM qryName;

The problem is rs.FindFirst does not find. no move and no error message

If I use column count 1, column width 1
rowsource SELECT [Last Name] & [First Name] AS Expr1
FROM qryName;

rs.FindFirst "[Last Name]&[First Name] = '" & Me![cboFind] & "'"
works fine but user see last name and first name combined in the find box.

I want to know if I use SELECT [Last Name] & ", " & [First Name] AS Expr1
FROM qryName
and how to modify rsFindFirst before = part

- Song

Now I did [Last Name] & ", " & [First Name] in cboFind.

Now how to modify AfterUpdate event?

Add two more hidden columns to the ComboBox that each have [Last Name] and
[First Name] separately (concatenated is fine for the user, not so much
for your code). Then use...

rs.FindFirst "[Last Name] = '" & Me![cboFind].Column(1) & "' AND [First
Name] = '" & Me![cboFind].Column(2) & "'"

 

[Rick Brandt]

Rick,

I tried your method.
column count 3, column width 0;0;1 (hide first 2 columns)
rowsource SELECT qryName.[Last Name], qryName.[First Name], [Last Name] & ", "
& [First Name] AS Expr1
FROM qryName;

The problem is rs.FindFirst does not find. no move and no error message

Then your syntax is incorrect.

If I use column count 1, column width 1
rowsource SELECT [Last Name] & [First Name] AS Expr1
FROM qryName;

rs.FindFirst "[Last Name]&[First Name] = '" & Me![cboFind] & "'"
works fine but user see last name and first name combined in the find box.

I want to know if I use SELECT [Last Name] & ", " & [First Name] AS Expr1
FROM qryName
and how to modify rsFindFirst before = part

rs.FindFirst "[Last Name] & ', ' & [First Name] = '" & Me![cboFind] & "'"

BUT...you are now searching on an expression rather than on raw field data which
is MUCH less efficient as no index can be used. You force the search to perform
a full scan of the RecordSet performing the expression evaluation on each row as
it goes.

This might not be a big deal depending on the size of your table and other
factors, but as a general practice you don't want to filter or search on
expressions when the same results can be achieved using actual field data.

 

[Song Su]

Hi Rick,

Thank you for fast reply. My table has about 1000 records. I really like to
use your method. You said my syntax is incorrect. which part? can you help
me to correct my syntax to be used in your method?

Thanks.

Rick,

I tried your method.
column count 3, column width 0;0;1 (hide first 2 columns)
rowsource SELECT qryName.[Last Name], qryName.[First Name], [Last Name] &
", " & [First Name] AS Expr1
FROM qryName;

The problem is rs.FindFirst does not find. no move and no error message

Then your syntax is incorrect.

If I use column count 1, column width 1
rowsource SELECT [Last Name] & [First Name] AS Expr1
FROM qryName;

rs.FindFirst "[Last Name]&[First Name] = '" & Me![cboFind] & "'"
works fine but user see last name and first name combined in the find
box.

I want to know if I use SELECT [Last Name] & ", " & [First Name] AS Expr1
FROM qryName
and how to modify rsFindFirst before = part

rs.FindFirst "[Last Name] & ', ' & [First Name] = '" & Me![cboFind] & "'"

BUT...you are now searching on an expression rather than on raw field data
which is MUCH less efficient as no index can be used. You force the
search to perform a full scan of the RecordSet performing the expression
evaluation on each row as it goes.

This might not be a big deal depending on the size of your table and other
factors, but as a general practice you don't want to filter or search on
expressions when the same results can be achieved using actual field data.

 

[Rick Brandt]

Hi Rick,

Thank you for fast reply. My table has about 1000 records. I really like to
use your method. You said my syntax is incorrect. which part? can you help me
to correct my syntax to be used in your method?

Try that same column set up, but just search on [Last Name] by itself. Once
that is working try adding the [First Name] part. There is no reason why you
shouldn't be able to get it to work.