Avoid Creating A Duplicate Record

[Mark]

When my form goes to a new record, I have a procedure that copies the last
record added to the form's underlying table into the form. The intent is that a
series of new records may have the same data in many of the fields so I paste in
the same values of the previous record and then edit what needs edited in the
new record saving much retyping of the same data. Doing this however creates the
definite possibility of creating a duplicate record. If after copying the
previous record, no fields are edited, a duplicate record of the previous record
is created. I'm looking for a way to detect if the new record has been edited
after a copy of the previous record has been added to the form. I checked the
Dirty property and the form is dirt after adding the copy of the previous record
so using the dirty property seems to be out. Does anyone have any idea on what I
can do?

Thanks!

Mark

[rkc]

Re: Avoid Creating A Duplicate Record


"Mark" <mmorrow@earthl ink.net> wrote in message
news:QbCnc.4535 $KE6.506@newsre ad3.news.atl.ea rthlink.net...[color=blue]
> When my form goes to a new record, I have a procedure that copies the last
> record added to the form's underlying table into the form. The intent is[/color]
that a[color=blue]
> series of new records may have the same data in many of the fields so I[/color]
paste in[color=blue]
> the same values of the previous record and then edit what needs edited in[/color]
the[color=blue]
> new record saving much retyping of the same data. Doing this however[/color]
creates the[color=blue]
> definite possibility of creating a duplicate record. If after copying the
> previous record, no fields are edited, a duplicate record of the previous[/color]
record[color=blue]
> is created. I'm looking for a way to detect if the new record has been[/color]
edited[color=blue]
> after a copy of the previous record has been added to the form. I checked[/color]
the[color=blue]
> Dirty property and the form is dirt after adding the copy of the previous[/color]
record[color=blue]
> so using the dirty property seems to be out. Does anyone have any idea on[/color]
what I[color=blue]
> can do?[/color]


Why are duplicate records allowed in your table? A table should be designed
so that duplicates aren't allowed by the database engine not by the user
interface.

[Graham Mandeno]

Re: Avoid Creating A Duplicate Record

Hi Mark

If you set the values in the new record by setting the DefaultValue for each
control, instead of the Value, then the new record will not become dirty
until the user types in some changes.

The one catch is that the DefaultValue property is always a string
expression, so strings and dates must be enclosed in quote marks.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Mark" <mmorrow@earthl ink.net> wrote in message
news:QbCnc.4535 $KE6.506@newsre ad3.news.atl.ea rthlink.net...[color=blue]
> When my form goes to a new record, I have a procedure that copies the last
> record added to the form's underlying table into the form. The intent is[/color]
that a[color=blue]
> series of new records may have the same data in many of the fields so I[/color]
paste in[color=blue]
> the same values of the previous record and then edit what needs edited in[/color]
the[color=blue]
> new record saving much retyping of the same data. Doing this however[/color]
creates the[color=blue]
> definite possibility of creating a duplicate record. If after copying the
> previous record, no fields are edited, a duplicate record of the previous[/color]
record[color=blue]
> is created. I'm looking for a way to detect if the new record has been[/color]
edited[color=blue]
> after a copy of the previous record has been added to the form. I checked[/color]
the[color=blue]
> Dirty property and the form is dirt after adding the copy of the previous[/color]
record[color=blue]
> so using the dirty property seems to be out. Does anyone have any idea on[/color]
what I[color=blue]
> can do?
>
> Thanks!
>
> Mark
>
>[/color]

[Mark]

Re: Avoid Creating A Duplicate Record

Graham,

Thank you for the response!

Would you explain further your last sentence about the DefaultValue property is
always a string expression?

If I wanted the following default values in different fields, what would I set
the DefaultValue to:
4.5
$8.75
5/10/04
Miles Shipping Company

Thanks,

Mark


"Graham Mandeno" <Graham.Mandeno @nomail.please> wrote in message
news:ei8OZDkNEH A.3312@tk2msftn gp13.phx.gbl...[color=blue]
> Hi Mark
>
> If you set the values in the new record by setting the DefaultValue for each
> control, instead of the Value, then the new record will not become dirty
> until the user types in some changes.
>
> The one catch is that the DefaultValue property is always a string
> expression, so strings and dates must be enclosed in quote marks.
> --
> Good Luck!
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
> "Mark" <mmorrow@earthl ink.net> wrote in message
> news:QbCnc.4535 $KE6.506@newsre ad3.news.atl.ea rthlink.net...[color=green]
> > When my form goes to a new record, I have a procedure that copies the last
> > record added to the form's underlying table into the form. The intent is[/color]
> that a[color=green]
> > series of new records may have the same data in many of the fields so I[/color]
> paste in[color=green]
> > the same values of the previous record and then edit what needs edited in[/color]
> the[color=green]
> > new record saving much retyping of the same data. Doing this however[/color]
> creates the[color=green]
> > definite possibility of creating a duplicate record. If after copying the
> > previous record, no fields are edited, a duplicate record of the previous[/color]
> record[color=green]
> > is created. I'm looking for a way to detect if the new record has been[/color]
> edited[color=green]
> > after a copy of the previous record has been added to the form. I checked[/color]
> the[color=green]
> > Dirty property and the form is dirt after adding the copy of the previous[/color]
> record[color=green]
> > so using the dirty property seems to be out. Does anyone have any idea on[/color]
> what I[color=green]
> > can do?
> >
> > Thanks!
> >
> > Mark
> >
> >[/color]
>
>[/color]

[Mark]

Re: Avoid Creating A Duplicate Record

Good point!

The table has 27 fields. Should I set each field to No Duplicates? How do I trap
the error if the record I am trying to enter is a duplicate?

Thanks,

Mark


"rkc" <rkc@yabba.dabb a.do.rochester. rr.bomb> wrote in message
news:TACnc.2310 70$e17.24839@tw ister.nyroc.rr. com...[color=blue]
>
> "Mark" <mmorrow@earthl ink.net> wrote in message
> news:QbCnc.4535 $KE6.506@newsre ad3.news.atl.ea rthlink.net...[color=green]
> > When my form goes to a new record, I have a procedure that copies the last
> > record added to the form's underlying table into the form. The intent is[/color]
> that a[color=green]
> > series of new records may have the same data in many of the fields so I[/color]
> paste in[color=green]
> > the same values of the previous record and then edit what needs edited in[/color]
> the[color=green]
> > new record saving much retyping of the same data. Doing this however[/color]
> creates the[color=green]
> > definite possibility of creating a duplicate record. If after copying the
> > previous record, no fields are edited, a duplicate record of the previous[/color]
> record[color=green]
> > is created. I'm looking for a way to detect if the new record has been[/color]
> edited[color=green]
> > after a copy of the previous record has been added to the form. I checked[/color]
> the[color=green]
> > Dirty property and the form is dirt after adding the copy of the previous[/color]
> record[color=green]
> > so using the dirty property seems to be out. Does anyone have any idea on[/color]
> what I[color=green]
> > can do?[/color]
>
>
> Why are duplicate records allowed in your table? A table should be designed
> so that duplicates aren't allowed by the database engine not by the user
> interface.
>
>
>[/color]

[Larry Daugherty]

Re: Avoid Creating A Duplicate Record

Hi Mark,

Wanting to carry current values forward to the next record is fairly common.
Someone else posted a similar question in microsoft.publi c.access My reply
follows

=============== =============== =============== =
Hi Maggie,

Put the following in the AfterUpdate event of your form

Const CQuote = """" 'that's two quotes, 4 quote marks

Me!txtExamDate. DefaultValue = CQuote & Me!txtExamDate. Value & CQuote

If you're going to use this code on more than one form I'd make CQuote a
global constant. Otherwise it can go in the declarations area of the
subroutine

As you can see, if you have several TextBoxes that you want to hold the
current value as the default just keep copying the Me!txtExamDate ... line
and always change the name appropriately.

Shame on me, I've lost the thread of attribution but I believe Allen Browne
posted the code.

=============== =============== =============== ===========
HTH
--
-Larry-
--

"Mark" <mmorrow@earthl ink.net> wrote in message
news:ZEGnc.577$ zO3.138@newsrea d2.news.atl.ear thlink.net...[color=blue]
> Graham,
>
> Thank you for the response!
>
> Would you explain further your last sentence about the DefaultValue[/color]
property is[color=blue]
> always a string expression?
>
> If I wanted the following default values in different fields, what would I[/color]
set[color=blue]
> the DefaultValue to:
> 4.5
> $8.75
> 5/10/04
> Miles Shipping Company
>
> Thanks,
>
> Mark
>
>
> "Graham Mandeno" <Graham.Mandeno @nomail.please> wrote in message
> news:ei8OZDkNEH A.3312@tk2msftn gp13.phx.gbl...[color=green]
> > Hi Mark
> >
> > If you set the values in the new record by setting the DefaultValue for[/color][/color]
each[color=blue][color=green]
> > control, instead of the Value, then the new record will not become dirty
> > until the user types in some changes.
> >
> > The one catch is that the DefaultValue property is always a string
> > expression, so strings and dates must be enclosed in quote marks.
> > --
> > Good Luck!
> >
> > Graham Mandeno [Access MVP]
> > Auckland, New Zealand
> >
> > "Mark" <mmorrow@earthl ink.net> wrote in message
> > news:QbCnc.4535 $KE6.506@newsre ad3.news.atl.ea rthlink.net...[color=darkred]
> > > When my form goes to a new record, I have a procedure that copies the[/color][/color][/color]
last[color=blue][color=green][color=darkred]
> > > record added to the form's underlying table into the form. The intent[/color][/color][/color]
is[color=blue][color=green]
> > that a[color=darkred]
> > > series of new records may have the same data in many of the fields so[/color][/color][/color]
I[color=blue][color=green]
> > paste in[color=darkred]
> > > the same values of the previous record and then edit what needs edited[/color][/color][/color]
in[color=blue][color=green]
> > the[color=darkred]
> > > new record saving much retyping of the same data. Doing this however[/color]
> > creates the[color=darkred]
> > > definite possibility of creating a duplicate record. If after copying[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > previous record, no fields are edited, a duplicate record of the[/color][/color][/color]
previous[color=blue][color=green]
> > record[color=darkred]
> > > is created. I'm looking for a way to detect if the new record has been[/color]
> > edited[color=darkred]
> > > after a copy of the previous record has been added to the form. I[/color][/color][/color]
checked[color=blue][color=green]
> > the[color=darkred]
> > > Dirty property and the form is dirt after adding the copy of the[/color][/color][/color]
previous[color=blue][color=green]
> > record[color=darkred]
> > > so using the dirty property seems to be out. Does anyone have any idea[/color][/color][/color]
on[color=blue][color=green]
> > what I[color=darkred]
> > > can do?
> > >
> > > Thanks!
> > >
> > > Mark
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[rkc]

Re: Avoid Creating A Duplicate Record


"Mark" <mmorrow@earthl ink.net> wrote in message
news:IHGnc.579$ zO3.475@newsrea d2.news.atl.ear thlink.net...[color=blue]
> Good point!
>
> The table has 27 fields. Should I set each field to No Duplicates? How do[/color]
I trap[color=blue]
> the error if the record I am trying to enter is a duplicate?[/color]

In general you would identify the field or combination of fields that make
a record unique and create a unique index on the field(s). That way the
Jet engine won't allow a duplicate record to be entered. If a duplicate
record is submitted for some reason there will be a popup message
informing you of it, but I wouldn't call it an error since it is not fatal
to
the application.

[Mark]

Re: Avoid Creating A Duplicate Record

In this case, a duplicate record is one where all 27 fields match an existing
record. A new record could have 26 fields match an existing record and one field
not match. That is not a duplicate. The one field could be any of the 27. For
this reason, I can't set any field to No Duplicates.

Mark


"rkc" <rkc@yabba.dabb a.do.rochester. rr.bomb> wrote in message
news:kjJnc.2312 79$e17.7101@twi ster.nyroc.rr.c om...[color=blue]
>
> "Mark" <mmorrow@earthl ink.net> wrote in message
> news:IHGnc.579$ zO3.475@newsrea d2.news.atl.ear thlink.net...[color=green]
> > Good point!
> >
> > The table has 27 fields. Should I set each field to No Duplicates? How do[/color]
> I trap[color=green]
> > the error if the record I am trying to enter is a duplicate?[/color]
>
> In general you would identify the field or combination of fields that make
> a record unique and create a unique index on the field(s). That way the
> Jet engine won't allow a duplicate record to be entered. If a duplicate
> record is submitted for some reason there will be a popup message
> informing you of it, but I wouldn't call it an error since it is not fatal
> to
> the application.
>
>
>[/color]

[Graham Mandeno]

Re: Avoid Creating A Duplicate Record

Hi Mark

I hope Larry's response has answered your question.

To further elaborate, the DefaultValue property is seen as a string which is
then evaluated as an expression. For example:
MyControl.Defau ltValue = "Date()"
would cause the Date function to be called, which would return the current
date.

Similarly,
MyControl.Defau ltValue = "8/1/04"
would cause the string to be evaluated as the expression "8 divided by 1
divided by 4", which would return the result 2.

Presumably this would NOT be what was intended. To get the intended result,
put the string in quotes:
MyControl.Defau ltValue = """8/1/04"""

Or, to make this more readable, use Larry's suggestion and declare a
constant for the quote character:
Const cQuote = """"
MyControl.Defau ltValue = cQuote & "8/1/04" & cQuote

For what you want to do, your default values will be derived from variables
(or recordset fields), not constants, so you might do something like this:
With Me.RecordsetClo ne
.MoveLast
Me.Field1.Defau ltValue = cQuote & .Field1 & cQuote
Me.Field2.Defau ltValue = cQuote & .Field2 & cQuote
... etc
End With

You can also use the Form_AfterUpdat e procedure to set change default values
to the new data as new records are added.

(Note that some sources suggest you enclose dates in hash signs (#), but I
recommend quotes as preferable because the conversion of a quoted date will
respect your regional date format settings, while #8/1/04# will always be
interpreted as the US format of mm/dd/yy)

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


you could set the DefaultValue of a textbox to "Da
"Mark" <mmorrow@earthl ink.net> wrote in message
news:ZEGnc.577$ zO3.138@newsrea d2.news.atl.ear thlink.net...[color=blue]
> Graham,
>
> Thank you for the response!
>
> Would you explain further your last sentence about the DefaultValue[/color]
property is[color=blue]
> always a string expression?
>
> If I wanted the following default values in different fields, what would I[/color]
set[color=blue]
> the DefaultValue to:
> 4.5
> $8.75
> 5/10/04
> Miles Shipping Company
>
> Thanks,
>
> Mark
>
>
> "Graham Mandeno" <Graham.Mandeno @nomail.please> wrote in message
> news:ei8OZDkNEH A.3312@tk2msftn gp13.phx.gbl...[color=green]
> > Hi Mark
> >
> > If you set the values in the new record by setting the DefaultValue for[/color][/color]
each[color=blue][color=green]
> > control, instead of the Value, then the new record will not become dirty
> > until the user types in some changes.
> >
> > The one catch is that the DefaultValue property is always a string
> > expression, so strings and dates must be enclosed in quote marks.
> > --
> > Good Luck!
> >
> > Graham Mandeno [Access MVP]
> > Auckland, New Zealand
> >
> > "Mark" <mmorrow@earthl ink.net> wrote in message
> > news:QbCnc.4535 $KE6.506@newsre ad3.news.atl.ea rthlink.net...[color=darkred]
> > > When my form goes to a new record, I have a procedure that copies the[/color][/color][/color]
last[color=blue][color=green][color=darkred]
> > > record added to the form's underlying table into the form. The intent[/color][/color][/color]
is[color=blue][color=green]
> > that a[color=darkred]
> > > series of new records may have the same data in many of the fields so[/color][/color][/color]
I[color=blue][color=green]
> > paste in[color=darkred]
> > > the same values of the previous record and then edit what needs edited[/color][/color][/color]
in[color=blue][color=green]
> > the[color=darkred]
> > > new record saving much retyping of the same data. Doing this however[/color]
> > creates the[color=darkred]
> > > definite possibility of creating a duplicate record. If after copying[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > previous record, no fields are edited, a duplicate record of the[/color][/color][/color]
previous[color=blue][color=green]
> > record[color=darkred]
> > > is created. I'm looking for a way to detect if the new record has been[/color]
> > edited[color=darkred]
> > > after a copy of the previous record has been added to the form. I[/color][/color][/color]
checked[color=blue][color=green]
> > the[color=darkred]
> > > Dirty property and the form is dirt after adding the copy of the[/color][/color][/color]
previous[color=blue][color=green]
> > record[color=darkred]
> > > so using the dirty property seems to be out. Does anyone have any idea[/color][/color][/color]
on[color=blue][color=green]
> > what I[color=darkred]
> > > can do?
> > >
> > > Thanks!
> > >
> > > Mark
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]