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
>
>
>
>
>
>
------------------------ 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/
