Find Record

[hobbit2612 via AccessMonster.com]

Hi, I wonder whether someone can help me please.

I have a form with the fields Name, Job, Length of Time In Job.

What I would like to do is to have a command button that searches only the
'Name' field but one that doesn't require the user to know how the full name
has been keyed in i.e for the name John Smith, the user can type John, Smith
or even J Smith.

I've tried using the built in find and replace functionality, but this only
allows the search to be within the cursor happens to be in at the time which
is something I don't want.

Can anyone help please?

Thanks and regards

Chris

 

[tina]

if you open the Find dialog, you should have the option to change the
default setting of Match to Any Part of Field. have you tried that?

and btw, if you really have a field in a table, and/or a control in a form,
called "Name", recommend you change it, since that is a Reserved word in
Access. for more information, see
http://home.att.net/~california.db/tips.html#aTip5.

hth

 

[hobbit2612 via AccessMonster.com]

Tina,

Many thanks for replying. I will certainly change the field name.

With regards to the 'Find' dialog, I had found the option for 'Match Any part
of the Field' but this is not really what I would like.

Unless I'm incorrect, the user has to have the cursor within the box they
wish to perform the find on, whereas, if possible I would like for the user,
no matter whether cursor is on the form, to only be able to search within the
Name field.

Could you tell me please of this is possible?

Thanks and regards

Chris

if you open the Find dialog, you should have the option to change the default setting of Match to Any Part of Field. have you tried that?

and btw, if you really have a field in a table, and/or a control in a form,
called "Name", recommend you change it, since that is a Reserved word in
Access. for more information, see
http://home.att.net/~california.db/tips.html#aTip5.

hth

Hi, I wonder whether someone can help me please.

[quoted text clipped - 14 lines]

Chris

 

[tina]

ah, now i understand what your goal is. you can write VBA code to search for
a record that matches specified text. you'll need a place for the user to
enter the search text; an unbound textbox control in the form's Header
section, or an Inputbox in the code itself, will do. you'd add the code to
the command button's Click event procedure, as

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "EmpName Like '*" & Me!txtFind & "*'"
If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
rst.Close
Set rst = Nothing

i haven't done this in quite awhile (i'm usually looking to filter, not
find, records), but i think the above code will work. you'll need to replace
EmplName with whatever you've renamed your "Name" field to, and replace
txtFind with the name of the textbox control in the form's Header section.
and btw, if you want to use an Inputbox instead of the textbox control, try

Dim rst As DAO.Recordset, str As String

str = Inputbox("Enter the name you want to find.")
Set rst = Me.RecordsetClone
rst.FindFirst "EmpName Like '*" & str & "*'"
If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
rst.Close
Set rst = Nothing

also, we weren't discussing normalization issues...but you might want to
read up/more on relational design principles, and then consider whether you
should break out the "name" field into separate fields, as

FirstName
MiddleInitial
LastName

you can always concatenate those values together for display/search in a
form, or for display in a report, as needed. for more information on
relational design, see http://home.att.net/~california.db/tips.html#aTip1.

hth

Tina,

Many thanks for replying. I will certainly change the field name.

With regards to the 'Find' dialog, I had found the option for 'Match Any part
of the Field' but this is not really what I would like.

Unless I'm incorrect, the user has to have the cursor within the box they
wish to perform the find on, whereas, if possible I would like for the user,
no matter whether cursor is on the form, to only be able to search within the
Name field.

Could you tell me please of this is possible?

Thanks and regards

Chris

if you open the Find dialog, you should have the option to change the default setting of Match to Any Part of Field. have you tried that?

and btw, if you really have a field in a table, and/or a control in a form,
called "Name", recommend you change it, since that is a Reserved word in
Access. for more information, see
http://home.att.net/~california.db/tips.html#aTip5.

hth

Hi, I wonder whether someone can help me please.

[quoted text clipped - 14 lines]

Chris

 

[hobbit2612 via AccessMonster.com]

Tina,

This is absolutely brilliant! I really appreciate your help.

I've gone for the 'Input Box' version and it works a treat.

Many thanks and regards

Chris

ah, now i understand what your goal is. you can write VBA code to search for
a record that matches specified text. you'll need a place for the user to
enter the search text; an unbound textbox control in the form's Header
section, or an Inputbox in the code itself, will do. you'd add the code to
the command button's Click event procedure, as

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "EmpName Like '*" & Me!txtFind & "*'"
If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
rst.Close
Set rst = Nothing

i haven't done this in quite awhile (i'm usually looking to filter, not
find, records), but i think the above code will work. you'll need to replace
EmplName with whatever you've renamed your "Name" field to, and replace
txtFind with the name of the textbox control in the form's Header section.
and btw, if you want to use an Inputbox instead of the textbox control, try

Dim rst As DAO.Recordset, str As String

str = Inputbox("Enter the name you want to find.")
Set rst = Me.RecordsetClone
rst.FindFirst "EmpName Like '*" & str & "*'"
If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
rst.Close
Set rst = Nothing

also, we weren't discussing normalization issues...but you might want to
read up/more on relational design principles, and then consider whether you
should break out the "name" field into separate fields, as

FirstName
MiddleInitial
LastName

you can always concatenate those values together for display/search in a
form, or for display in a report, as needed. for more information on
relational design, see http://home.att.net/~california.db/tips.html#aTip1.

hth

Tina,

[quoted text clipped - 28 lines]

 

[tina]

you're welcome, hon, but it's common code so i can't lay claim to it. and
not brilliant by a long shot; test it to see, but i don't think it'll handle
names with apostrophes correctly, such as O'Sullivan. if that's a concern,
you can probably google to find a post that addresses the issue, and a code
snippet that shows correct syntax to replace the current expression

"EmpName Like '*" & Me!txtFind & "*'"

hth

Tina,

This is absolutely brilliant! I really appreciate your help.

I've gone for the 'Input Box' version and it works a treat.

Many thanks and regards

Chris

ah, now i understand what your goal is. you can write VBA code to search for
a record that matches specified text. you'll need a place for the user to
enter the search text; an unbound textbox control in the form's Header
section, or an Inputbox in the code itself, will do. you'd add the code to
the command button's Click event procedure, as

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "EmpName Like '*" & Me!txtFind & "*'"
If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
rst.Close
Set rst = Nothing

i haven't done this in quite awhile (i'm usually looking to filter, not
find, records), but i think the above code will work. you'll need to replace
EmplName with whatever you've renamed your "Name" field to, and replace
txtFind with the name of the textbox control in the form's Header section.
and btw, if you want to use an Inputbox instead of the textbox control, try

Dim rst As DAO.Recordset, str As String

str = Inputbox("Enter the name you want to find.")
Set rst = Me.RecordsetClone
rst.FindFirst "EmpName Like '*" & str & "*'"
If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
rst.Close
Set rst = Nothing

also, we weren't discussing normalization issues...but you might want to
read up/more on relational design principles, and then consider whether you
should break out the "name" field into separate fields, as

FirstName
MiddleInitial
LastName

you can always concatenate those values together for display/search in a
form, or for display in a report, as needed. for more information on
relational design, see http://home.att.net/~california.db/tips.html#aTip1.

hth

Tina,

[quoted text clipped - 28 lines]

Chris

 

[hobbit2612 via AccessMonster.com]

Tina,

That's great!

Many thanks Chris

you're welcome, hon, but it's common code so i can't lay claim to it. and
not brilliant by a long shot; test it to see, but i don't think it'll handle
names with apostrophes correctly, such as O'Sullivan. if that's a concern,
you can probably google to find a post that addresses the issue, and a code
snippet that shows correct syntax to replace the current expression

"EmpName Like '*" & Me!txtFind & "*'"

hth

Tina,

[quoted text clipped - 54 lines]

 

[John W. Vinson]

you're welcome, hon, but it's common code so i can't lay claim to it. and
not brilliant by a long shot; test it to see, but i don't think it'll handle
names with apostrophes correctly, such as O'Sullivan. if that's a concern,
you can probably google to find a post that addresses the issue, and a code
snippet that shows correct syntax to replace the current expression

"EmpName Like '*" & Me!txtFind & "*'"

For future reference, just use two " characters rather than ' as a delimiter:

"EmpName Like ""*" & Me!txtFind & "*"""

Two " marks in a " delimited string get translated to a single " character, so
this would become

EmpName LIKE "*O'Sullivan*"

which would work correctly.

 

[tina]

thanks John! tina

For future reference, just use two " characters rather than ' as a delimiter:

"EmpName Like ""*" & Me!txtFind & "*"""

Two " marks in a " delimited string get translated to a single " character, so
this would become

EmpName LIKE "*O'Sullivan*"

which would work correctly.