Search last name and first name combo box

S

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
 
S

smk23

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

Song Su

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

Now how to modify AfterUpdate event?

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


Song Su said:
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
 
S

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

Rick Brandt said:
Song Su said:
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) & "'"
 
R

Rick Brandt

Song Su said:
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.
 
S

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 Brandt said:
Song Su said:
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.
 
R

Rick Brandt

Song Su said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top