"Don't know about you but my users would linch me."
I would place that in the category of "Strong arguments for checking for
duplicates early on". ;-)
Seriously though, the reason I mentioned BeforeUpdate for the form was
because Dawn sounded like he was currently cross-checking all fields in
determining duplicates. But to be sure, a user might not like you very much
if you could have programmed to check for duplicates after filling in two
fields instead of the entire form.
Good point,
Toby
~
----- Original Message -----
From: "Robin" <[EMAIL PROTECTED]>
To: <AccessDevelopers@yahoogroups.com>
Sent: Sunday, October 02, 2005 12:39 AM
Subject: [AccessDevelopers] Re: Getting Duplicate Records
> Hello Toby,
>
> Oops, that'll teach me to read your messages better than I did. I
> was assuming the before update of each text box where in fact you
> had stated the before update of the form.
>
> This means however that the user has filled in all that information
> before the condition has been tested. Don't know about you but my
> users would linch me.
>
> Bound forms are great things but I find unbound forms have more
> manipulation control for me, after all you only have to write the
> code once.
>
> Regards
> Robin.
>
>
>
> --- In AccessDevelopers@yahoogroups.com, "Toby Bierly" <[EMAIL PROTECTED]>
> wrote:
> > Sure it will. That is what BeforeUpdate is for. You can do all
> your
> > checking for duplicates in BeforeUpdate comparing what is in the
> text boxes
> > on the form against what is in the table already. The record is
> not yet
> > added to the table when the BeforeUpdate event occurs (hence its
> name). The
> > record in a bound form is only inserted or updated to the table at
> either
> > the closing of the form or changing of records, unless it is
> programmed to
> > Update somehow from code. To cancel the record from updating,
> just add the
> > line
> >
> > Cancel = True
> >
> > somewhere in the BeforeUpdate event. If the Update was triggered
> by
> > changing records, the focus will return to the form exactly where
> it was
> > before. If the Update was triggered by closing the form, then a
> message
> > will pop up saying the record can't be saved at this time and I
> think the
> > form goes ahead and closes anyway and you lose the data that you
> just typed
> > into the form (which is why I always add my own button to close
> the form and
> > disable the close button if I am doing any BeforeUpdate checking).
> >
> > So your BeforeUpdate code might look something like this:
> >
> > ' If current form is going to create a duplicate record, Then
> > If vbNo = MsgBox "This record appears to be a duplicate. Are
> you sure
> > you want to add it?", vbYesNo, "Duplicate!" Then
> > Cancel = True
> > End If
> > ' End If
> >
> > I see no reason why you can't check for duplicates in a bound
> form. In
> > general, unbound forms require a lot of additional programming and
> do not
> > utilize all of Access' built-in features. For instance, for most
> forms, you
> > have to add buttons and manually program all the Update (or Save),
> Insert,
> > Delete, and Navigations capabilities, when Access does all of that
> > automatically for you (with some quirks).
> >
> > Thanks,
> > Toby
> >
> > ~
> > ----- Original Message -----
> > From: "Robin" <[EMAIL PROTECTED]>
> > To: <AccessDevelopers@yahoogroups.com>
> > Sent: Saturday, October 01, 2005 6:03 PM
> > Subject: [AccessDevelopers] Re: Getting Duplicate Records
> >
> >
> > > Hello Toby,
> > >
> > > Sorry Toby but this one wont work on a bound form:
> > >
> > > 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.
> > >
> > > If you put the code on either before or after, because there are
> two
> > > text boxes to concider. At least those boxes must be unbound.
> > >
> > > As a personal preference, I would use:
> > >
> > > 8. Unbound form so that more checks could be run if needed.
> > >
> > > Regards
> > > Robin.
> > >
> > >
> > >
> > >
> > > --- In AccessDevelopers@yahoogroups.com, "Toby Bierly"
> <[EMAIL PROTECTED]>
> > > wrote:
> > > > 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
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > Please zip all files prior to uploading to Files section.
> > > Yahoo! Groups Links
> > >
> > >
> > >
> > >
> > >
> > >
>
>
>
>
>
>
>
> Please zip all files prior to uploading to Files section.
> Yahoo! Groups Links
>
>
>
>
>
>
------------------------ Yahoo! Groups Sponsor --------------------~-->
Most low income households are not online. Help bridge the digital divide today!
http://us.click.yahoo.com/cd_AJB/QnQLAA/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/
