It seems to me there are about 7 different ways to do this, and it mainly is
up to programmer preference. If it works and there isn't any negatives
(like slower performance), fine.
1. You could use a combo box and Not In List, although I assume you would
have to do this with multiple controls, since you would have to do at least
FirstName and LastName (it seemed he wanted all fields checked).
2. You could put something like this in the AfterUpdate of FirstName and
LastName (or more controls)
If Not IsNull(LastName) Then
'Check for duplicates and handle appropriately
End If
If all the fields that you are basically using as an index are filled in,
then go ahead and check for duplicates
3. You could set up a multi-field Index with the combination of fields that
you wanted to be unique and prevent duplicates from happening, although you
might get an error message that you would want to handle appropriately.
4. You could test for duplicates in the BeforeUpdate of the form (assuming a
bound form). That way you're not deleting a record right after you create
it. I see no point in putting any code in the AfterUpdate event of the
form.
When checking for duplicates, you could use...
5. DLookup("ID", "TableName", "FirstName = '" & txtFirstName & "' And
LastName = '" & txtLastName & "' ...
6. Or a query checking for an record with identical fields (at least the
field you are cross-checking). Then either dump the results into a
recordset and check for EOF (meaning the query returned 0 records meaning
there isn't a duplicate)
7. Or a DCount query (same query as in 6 except for the DCount)
Like I said, there are tons of ways to attack this problem.
HTH,
Toby
----- Original Message -----
From: "Robin" <[EMAIL PROTECTED]>
To: <AccessDevelopers@yahoogroups.com>
Sent: Saturday, October 01, 2005 5:20 AM
Subject: [AccessDevelopers] Re: Getting Duplicate Records
> Hi Tom,
>
> No, I don't like deleting records right in the middle of making
> them. I leave the text boxes in question unbound (or even the whole
> form) and then use an update sql if the record doesn't exist.
>
> Like I said, a coupla lines of code (maybe three or four).
>
> Regards
> Robin.
>
>
> --- In AccessDevelopers@yahoogroups.com, "Tom Oakes" <[EMAIL PROTECTED]>
> wrote:
> > Yes - more work, but more elegant in my opinion. The AfterUpdate
> occurs
> > after the problem (the duplicate entry) has already occurred.
> > Then you give them the option to delete it if it's a duplicate,
> I'd assume -
> > which seems a little silly to me.
> >
> > Tom Oakes
> > Personal PC Consultants, Inc.
> > [EMAIL PROTECTED]
> > 503.230.0911 (O)
> > 402.968.6946 (C)
> > 734.264.0911 (F)
> >
> >
> >
> > _____
> >
> > From: AccessDevelopers@yahoogroups.com
> > [mailto:[EMAIL PROTECTED] On Behalf Of Robin
> > Sent: Friday, September 30, 2005 4:57 PM
> > To: AccessDevelopers@yahoogroups.com
> > Subject: [AccessDevelopers] Re: Getting Duplicate Records
> >
> >
> > Hi Guys,
> >
> > Scuse me for butting in, but this seems like a lot of trouble to
> go
> > to to avoid using DLookup.
> >
> > If you do a lookup in the after update event on FirstName and
> > LastName and a message or other aproprate action if found (like
> > loading the record) it's only a coupla lines of code.
> >
> > Regards
> > Robin.
> >
> >
> >
> > --- In AccessDevelopers@yahoogroups.com, "Crosier, Dawn"
> > <[EMAIL PROTECTED]> wrote:
> > > Thanks Tom - This will be easier to implement than I thought it
> > would,
> > > basically I will force the search first and then allow them to
> add
> > if no
> > > records are found. I already had a search form / process in
> > place, I'll
> > > just reverse when things are done.
> > >
> > > Sometimes you just need someone else's "eyes" on a problem!
> > Especially
> > > when you are a one person development "team".
> > >
> > > Have a good weekend!
> > >
> > > Dawn Crosier
> > > Application Specialist
> > > "Education Lasts a Lifetime"
> > >
> > > This message was posted to a newsgroup. Please post replies and
> > > questions to the group so that others can learn as well.
> > >
> > >
> > > ________________________________
> > >
> > > From: AccessDevelopers@yahoogroups.com
> > > [mailto:[EMAIL PROTECTED] On Behalf Of Tom Oakes
> > > Sent: Friday, September 30, 2005 4:21 PM
> > > To: AccessDevelopers@yahoogroups.com
> > > Subject: RE: [AccessDevelopers] Getting Duplicate Records
> > >
> > >
> > > You can't reasonably expect to be able to index the first & last
> > names,
> > > so...
> > >
> > > I would create a unbound, pop-up form that is the first step in
> > entering
> > > a new client. Change the "Allow Additions" property on your
> main
> > client
> > > form to disallow the "native" addition of a new record.
> > >
> > > Your pop-up would have textboxes for first/last/title, at
> least.
> > Add a
> > > "Save" button and a "Cancel" button. The "Save" button does
> this:
> > >
> > > 1) Searches the client table for matches on these fields.
> > > 2) Warns if found - maybe have a listbox on this form that shows
> > > duplicates with a bit more info (address, phone, etc) that
> enables
> > them
> > > to determine whether the client actually exists in the db
> already,
> > or
> > > whether the match is coincidence.
> > > 3) Give the user the choice to add the record, or jump to the
> > existing
> > > record.
> > >
> > > I do this sort of thing quite a bit to enforce relational
> integrity
> > > that's a little to complicated to do with indices, etc.
> > >
> > > Hope that helps.
> > >
> > > Tom Oakes
> > > Personal PC Consultants, Inc.
> > > [EMAIL PROTECTED]
> > > 503.230.0911 (O)
> > > 402.968.6946 (C)
> > > 734.264.0911 (F)
> >
> >
> >
> >
> >
> > Please zip all files prior to uploading to Files section.
> >
> >
> >
> >
> > SPONSORED LINKS
> > Microsoft
> > <http://groups.yahoo.com/gads?
> t=ms&k=Microsoft+access+developer&w1=Microsoft
> >
> +access+developer&w2=Microsoft+access+help&w3=Microsoft+access+databa
> se&w4=M
> >
> icrosoft+access+training&w5=Microsoft+access+training+course&w6=Micro
> soft+ac
> > cess+programming&c=6&s=193&.sig=d-CjBIrYOH9NCKHYFeGZJA> access
> developer
> > Microsoft
> > <http://groups.yahoo.com/gads?
> t=ms&k=Microsoft+access+help&w1=Microsoft+acce
> >
> ss+developer&w2=Microsoft+access+help&w3=Microsoft+access+database&w4
> =Micros
> >
> oft+access+training&w5=Microsoft+access+training+course&w6=Microsoft+
> access+
> > programming&c=6&s=193&.sig=crx-d4AAhdklv_VozGVAUw> access help
> Microsoft
> > <http://groups.yahoo.com/gads?
> t=ms&k=Microsoft+access+database&w1=Microsoft+
> >
> access+developer&w2=Microsoft+access+help&w3=Microsoft+access+databas
> e&w4=Mi
> >
> crosoft+access+training&w5=Microsoft+access+training+course&w6=Micros
> oft+acc
> > ess+programming&c=6&s=193&.sig=qg2hDuQNweByMCX0NU7cEA> access
> database
> > Microsoft
> > <http://groups.yahoo.com/gads?
> t=ms&k=Microsoft+access+training&w1=Microsoft+
> >
> access+developer&w2=Microsoft+access+help&w3=Microsoft+access+databas
> e&w4=Mi
> >
> crosoft+access+training&w5=Microsoft+access+training+course&w6=Micros
> oft+acc
> > ess+programming&c=6&s=193&.sig=bLZHqTqWUQny609X1OkmNA> access
> training
> > Microsoft
> > <http://groups.yahoo.com/gads?
> t=ms&k=Microsoft+access+training+course&w1=Mic
> >
> rosoft+access+developer&w2=Microsoft+access+help&w3=Microsoft+access+
> databas
> >
> e&w4=Microsoft+access+training&w5=Microsoft+access+training+course&w6
> =Micros
> > oft+access+programming&c=6&s=193&.sig=d8GQXfQW3RZ64rOfzIMo8A>
> access
> > training course Microsoft
> > <http://groups.yahoo.com/gads?
> t=ms&k=Microsoft+access+programming&w1=Microso
> >
> ft+access+developer&w2=Microsoft+access+help&w3=Microsoft+access+data
> base&w4
> >
> =Microsoft+access+training&w5=Microsoft+access+training+course&w6=Mic
> rosoft+
> > access+programming&c=6&s=193&.sig=iXDlL79-kkgjv6fLyFu3Sg> access
> programming
> >
> >
> > _____
> >
> > YAHOO! GROUPS LINKS
> >
> >
> >
> > * Visit your group "AccessDevelopers
> > <http://groups.yahoo.com/group/AccessDevelopers> " on the web.
> >
> >
> > * To unsubscribe from this group, send an email to:
> > [EMAIL PROTECTED]
> > <mailto:[EMAIL PROTECTED]
> subject=Unsubscribe>
> >
> >
> > * Your use of Yahoo! Groups is subject to the Yahoo! Terms
> of Service
> > <http://docs.yahoo.com/info/terms/> .
> >
> >
> > _____
>
>
>
>
>
>
>
> Please zip all files prior to uploading to Files section.
> Yahoo! Groups Links
>
>
>
>
>
>
------------------------ Yahoo! Groups Sponsor --------------------~-->
Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life.
http://us.click.yahoo.com/A77XvD/vlQLAA/TtwFAA/q7folB/TM
--------------------------------------------------------------------~->
Please zip all files prior to uploading to Files section.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessDevelopers/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
