Search LastName and FirstName Fields

[MentalDrow]

Hi All,

I'm sorry if this ends up being a post similar to someone else's that has
already been answered. Here is what I am trying to do.

I'm creating a database that has in it fields for first name and last name.
To prevent duplicate entries, I want the used to be able to click a New
Driver button and input the last name (variable = LastName) and first name
(FirstName) and have the macro search the DriverLast and DriverFirst fields.
If there is a match, I want to notify the user and ask them if they wish to
open the appropriate record so new deliveries can be annotated. Is there a
way to open an exsisting record in the appropriate form and have the subform
open a new record? If not I can probably place a command button on the
primary form so the subform opens a new (blank) record.

If there is no match I can already get it to open a form to input the new
driver's info.

Additional information: The delivery form (DeliveryInfoFRM) is a subform of
the driver form (DriverInfoFRM). If it would make assisting me easier, I can
easily break them away from each other. The delivery table and driver table
are separate tables from each other. If any additional information is
necessary, please let me know.

Thanks for the assist.

Chaz

 

[Joseph R. Pottschmidt]

Dear MentalDrow:

It is a lot easier to code the process that you're trying to complete
from the prospective of enter the first name and last name into a field
and have a search done and open a subform. If there is no match then
have another form open to add that person's name into the database.

MS Access has an option already built into the Database Tables that
doesn't allow for duplicates, and you can use this as Key fields in the
database.

Hope that helps.

Joe P.


-----Original Message-----
From: MentalDrow [mailto:[email protected]]
Posted At: Friday, June 09, 2006 10:49 AM
Posted To: microsoft.public.access.macros
Conversation: Search LastName and FirstName Fields
Subject: Search LastName and FirstName Fields

Hi All,

I'm sorry if this ends up being a post similar to someone else's that
has
already been answered. Here is what I am trying to do.

I'm creating a database that has in it fields for first name and last
name.
To prevent duplicate entries, I want the used to be able to click a New
Driver button and input the last name (variable = LastName) and first
name
(FirstName) and have the macro search the DriverLast and DriverFirst
fields.
If there is a match, I want to notify the user and ask them if they wish
to
open the appropriate record so new deliveries can be annotated. Is
there a
way to open an exsisting record in the appropriate form and have the
subform
open a new record? If not I can probably place a command button on the
primary form so the subform opens a new (blank) record.

If there is no match I can already get it to open a form to input the
new
driver's info.

Additional information: The delivery form (DeliveryInfoFRM) is a
subform of
the driver form (DriverInfoFRM). If it would make assisting me easier,
I can
easily break them away from each other. The delivery table and driver
table
are separate tables from each other. If any additional information is
necessary, please let me know.

Thanks for the assist.

Chaz

 

[MentalDrow]

Joe P.
I agree; however, I have over 25 different personnel who will be entering
the information. Some have military backgrounds and some don't. The way the
information gets formatted will vary from individual to individual.

That being said, I think I have come up with an alternative using what you
suggested. I recall there being a way to merge the data from two separate
fileds into one field. I can use this to create what I'll call a "search
field" or even turn THAT into the Primary Key field. Still, I appreciate the
prompt and helpful response.

Chaz

 

[Steve Schapel]

Chaz,

You need to be able to cater to the possibility that there will actually
be, at some point, more than one driver with the same names.

It is actually very easy to do what you are asking. Except there is a
problem. First the method... In the Condition column of your macro, put
like this...
DCount("*","YourDriversTable","[FirstName]='" & [FirstName] & "' And
[LastName]='" & [LastName] & "'")>0
.... which is when you use a MsgBox action, or an OpenForm action to open
a notification so the user can choose to go to the existing record or
[roceed with the creation of a new one.

But here's the poroblem: This will only work reliably if the data entry
person enters the name *exactly* as before. So if you have a driver
already entered as Charles Drow, and then the user tries to enter him
the next time as Chaz Drow, the "match" won't be found, and then you
will have the same person entered twice.

But wouldn't it be more often the case that you are trying to enter data
for an existing driver, rather than entering a new one? If so, may I
make a suggestion?... Put an unbound combobox in the Header of the
form, which shows a list of all existing drivers. When the user starts
typing the surname, it will find that portion of the list. If the
driver is found on the list, open the form at that driver's record (this
would be on the After Update event of the combobox). If the driver is
not found in the list, provide for the entry of a new driver. Based on
what you have said so far, this would seem a smoother approach from a
work flow point of view.

 

[MentalDrow]

Steve,

Actually, the information would be pulled from the person's driver's
license. As it happens though, I was considering something along the lines
of what you just mentioned. Makes less work for me in the long run.
From what I'm understanding in your reply, I may have given the wrong
impression (probably based on the fact this post in the Macros folder) that I
intend to use the Macro Builder. I don't actually use the Macro Builder. I
use VBA both because I find it more versatile and because it's such a
challenge to learn something new. I'll look over the information you've
provided though because I'm assuming I can use it in VBA with the right
syntax. Appreciate your post (along with everyone else's).

Chaz

Chaz,

You need to be able to cater to the possibility that there will actually
be, at some point, more than one driver with the same names.

It is actually very easy to do what you are asking. Except there is a
problem. First the method... In the Condition column of your macro, put
like this...
DCount("*","YourDriversTable","[FirstName]='" & [FirstName] & "' And
[LastName]='" & [LastName] & "'")>0
.... which is when you use a MsgBox action, or an OpenForm action to open
a notification so the user can choose to go to the existing record or
[roceed with the creation of a new one.

But here's the poroblem: This will only work reliably if the data entry
person enters the name *exactly* as before. So if you have a driver
already entered as Charles Drow, and then the user tries to enter him
the next time as Chaz Drow, the "match" won't be found, and then you
will have the same person entered twice.

But wouldn't it be more often the case that you are trying to enter data
for an existing driver, rather than entering a new one? If so, may I
make a suggestion?... Put an unbound combobox in the Header of the
form, which shows a list of all existing drivers. When the user starts
typing the surname, it will find that portion of the list. If the
driver is found on the list, open the form at that driver's record (this
would be on the After Update event of the combobox). If the driver is
not found in the list, provide for the entry of a new driver. Based on
what you have said so far, this would seem a smoother approach from a
work flow point of view.

--
Steve Schapel, Microsoft Access MVP

Hi All,

I'm sorry if this ends up being a post similar to someone else's that has
already been answered. Here is what I am trying to do.

I'm creating a database that has in it fields for first name and last name.
To prevent duplicate entries, I want the used to be able to click a New
Driver button and input the last name (variable = LastName) and first name
(FirstName) and have the macro search the DriverLast and DriverFirst fields.
If there is a match, I want to notify the user and ask them if they wish to
open the appropriate record so new deliveries can be annotated. Is there a
way to open an exsisting record in the appropriate form and have the subform
open a new record? If not I can probably place a command button on the
primary form so the subform opens a new (blank) record.

If there is no match I can already get it to open a form to input the new
driver's info.

Additional information: The delivery form (DeliveryInfoFRM) is a subform of
the driver form (DriverInfoFRM). If it would make assisting me easier, I can
easily break them away from each other. The delivery table and driver table
are separate tables from each other. If any additional information is
necessary, please let me know.

Thanks for the assist.

Chaz

 

[Steve Schapel]

Chaz,

That is correct, the focus of this newsgroup is on macros, so if your
question does not relate to macros, your question would be better in a
more applicable newsgroup.