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
> >
> >
> >
> >
> >
> >
------------------------ Yahoo! Groups Sponsor --------------------~-->
Fair play? Video games influencing politics. Click and talk back!
http://us.click.yahoo.com/T8sf5C/tzNLAA/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/
