Actively Find Duplicates On Form

  • Thread starter Thread starter DaveAP
  • Start date Start date
D

DaveAP

Access 2003, Windows XP

I am trying to actively stop duplicates from being entered on a form but
cannot seem to get the code to work correctly (or I'm going about it all
wrong).

The GroupTable holds all of the active records and I would like the form to
show that a duplicate is being entered. The trick is there are seven
different forms and some can have this field be a duplicate, others cannot.

Example:
Form A222 cannot have the Claims_Tracking_Number duplicated on another form
while form MA61 can.

I've written a query to search for the duplicates on the forms that cannot
have dups:

SELECT GroupTable.Claims_Tracking_Number, GroupTable.[Name of Form]
FROM GroupTable
WHERE (((GroupTable.Claims_Tracking_Number) In (SELECT
[Claims_Tracking_Number] FROM [GroupTable] As Tmp GROUP BY
[Claims_Tracking_Number] HAVING Count(*)>1 )) AND ((GroupTable.[Name of
Form])=[Forms]![Startup_Split]![FormDataEntry]))
ORDER BY GroupTable.Claims_Tracking_Number;

The Name of Form is entered by the rep on a startup page from a combo box
and remains open in the background.

How do I get the query to run as an AfterUpdate and alert the rep once
he/she enters a Claims Tracking Number on the data entry form?
 
Dave

First, Access forms are not data. The data is stored in the tables, so you
are really checking the tables to see if the data is already there.

Next, "duplicate" can mean whatever you want it to mean ... what do YOU want
it to mean? One field the same...? All fields identical...?

Finally, rather than forcing your users to enter some data and then checking
to see if they are using something that's already in the table, what about
the idea of giving them a way to look up existing values (a combobox) and
add a new value if they don't find it on the list (using the NotInList
event)?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thank you for replying Jeff!

Yes, I am trying to check the tables if the data is already there. I think
my query works but I cannot get it to run on the AfterUpdate event on the
text box where the rep enters the Claims Tracking Number.

The combo box list for the Claims Tracking Number may get too long (right
now there's 40,000+ unique tracking numbers). This probably is my best bet
however, could show me how the NotInList event works?

The definition of a duplicate Claims Tracking Number in this database is
tricky as mentioned, some forms (A222) cannot use the Claims Tracking Number
twice, where as other forms (MA61) can because it was entered previously
under a different valid form (AP20).

I apologize for my ignorance as I'm still fairly new to coding and databases.


Jeff Boyce said:
Dave

First, Access forms are not data. The data is stored in the tables, so you
are really checking the tables to see if the data is already there.

Next, "duplicate" can mean whatever you want it to mean ... what do YOU want
it to mean? One field the same...? All fields identical...?

Finally, rather than forcing your users to enter some data and then checking
to see if they are using something that's already in the table, what about
the idea of giving them a way to look up existing values (a combobox) and
add a new value if they don't find it on the list (using the NotInList
event)?

Regards

Jeff Boyce
Microsoft Office/Access MVP


DaveAP said:
Access 2003, Windows XP

I am trying to actively stop duplicates from being entered on a form but
cannot seem to get the code to work correctly (or I'm going about it all
wrong).

The GroupTable holds all of the active records and I would like the form
to
show that a duplicate is being entered. The trick is there are seven
different forms and some can have this field be a duplicate, others
cannot.

Example:
Form A222 cannot have the Claims_Tracking_Number duplicated on another
form
while form MA61 can.

I've written a query to search for the duplicates on the forms that cannot
have dups:

SELECT GroupTable.Claims_Tracking_Number, GroupTable.[Name of Form]
FROM GroupTable
WHERE (((GroupTable.Claims_Tracking_Number) In (SELECT
[Claims_Tracking_Number] FROM [GroupTable] As Tmp GROUP BY
[Claims_Tracking_Number] HAVING Count(*)>1 )) AND ((GroupTable.[Name of
Form])=[Forms]![Startup_Split]![FormDataEntry]))
ORDER BY GroupTable.Claims_Tracking_Number;

The Name of Form is entered by the rep on a startup page from a combo box
and remains open in the background.

How do I get the query to run as an AfterUpdate and alert the rep once
he/she enters a Claims Tracking Number on the data entry form?
Click to expand...
Click to expand...
 
Dave

Check Access HELP for "NotInList" ... the syntax is listed and there's an
example you can copy/adapt.

I still don't have a clear picture how "duplicate" is decided, so I'm
guessing you are not yet able to tell Access how to decide. Can you provide
more specific details on how to determine this? A couple examples might
help.

Regards

Jeff Boyce
Microsoft Office/Access MVP


DaveAP said:
Thank you for replying Jeff!

Yes, I am trying to check the tables if the data is already there. I
think
my query works but I cannot get it to run on the AfterUpdate event on the
text box where the rep enters the Claims Tracking Number.

The combo box list for the Claims Tracking Number may get too long (right
now there's 40,000+ unique tracking numbers). This probably is my best
bet
however, could show me how the NotInList event works?

The definition of a duplicate Claims Tracking Number in this database is
tricky as mentioned, some forms (A222) cannot use the Claims Tracking
Number
twice, where as other forms (MA61) can because it was entered previously
under a different valid form (AP20).

I apologize for my ignorance as I'm still fairly new to coding and
databases.


Jeff Boyce said:
Dave

First, Access forms are not data. The data is stored in the tables, so
you
are really checking the tables to see if the data is already there.

Next, "duplicate" can mean whatever you want it to mean ... what do YOU
want
it to mean? One field the same...? All fields identical...?

Finally, rather than forcing your users to enter some data and then
checking
to see if they are using something that's already in the table, what
about
the idea of giving them a way to look up existing values (a combobox) and
add a new value if they don't find it on the list (using the NotInList
event)?

Regards

Jeff Boyce
Microsoft Office/Access MVP


DaveAP said:
Access 2003, Windows XP

I am trying to actively stop duplicates from being entered on a form
but
cannot seem to get the code to work correctly (or I'm going about it
all
wrong).

The GroupTable holds all of the active records and I would like the
form
to
show that a duplicate is being entered. The trick is there are seven
different forms and some can have this field be a duplicate, others
cannot.

Example:
Form A222 cannot have the Claims_Tracking_Number duplicated on another
form
while form MA61 can.

I've written a query to search for the duplicates on the forms that
cannot
have dups:

SELECT GroupTable.Claims_Tracking_Number, GroupTable.[Name of Form]
FROM GroupTable
WHERE (((GroupTable.Claims_Tracking_Number) In (SELECT
[Claims_Tracking_Number] FROM [GroupTable] As Tmp GROUP BY
[Claims_Tracking_Number] HAVING Count(*)>1 )) AND ((GroupTable.[Name of
Form])=[Forms]![Startup_Split]![FormDataEntry]))
ORDER BY GroupTable.Claims_Tracking_Number;

The Name of Form is entered by the rep on a startup page from a combo
box
and remains open in the background.

How do I get the query to run as an AfterUpdate and alert the rep once
he/she enters a Claims Tracking Number on the data entry form?
Click to expand...
Click to expand...
Click to expand...
 
Hopefully this will help solve the duplicate problem. Below are two fields
in the GroupTable and shown is an example of how I would want Access to
verify if the entry is a duplicate or not.

Name of Form ClaimsTrackingNumber
A222 QA0000000000000001
A222 QA0000000000000001 (Duplicate)
MA61 QA0000000000000003
MA61 QA0000000000000003 (Not a duplicate)
MatchRef QA0000000000000003 (Not a duplicate)
VAR Track QA0000000000000001 (Duplicate)

Some forms are allowed to have the same Claims Tracking Number, others are
not. Does "Dlookup" work with an If/then function?


Jeff Boyce said:
Dave

Check Access HELP for "NotInList" ... the syntax is listed and there's an
example you can copy/adapt.

I still don't have a clear picture how "duplicate" is decided, so I'm
guessing you are not yet able to tell Access how to decide. Can you provide
more specific details on how to determine this? A couple examples might
help.

Regards

Jeff Boyce
Microsoft Office/Access MVP


DaveAP said:
Thank you for replying Jeff!

Yes, I am trying to check the tables if the data is already there. I
think
my query works but I cannot get it to run on the AfterUpdate event on the
text box where the rep enters the Claims Tracking Number.

The combo box list for the Claims Tracking Number may get too long (right
now there's 40,000+ unique tracking numbers). This probably is my best
bet
however, could show me how the NotInList event works?

The definition of a duplicate Claims Tracking Number in this database is
tricky as mentioned, some forms (A222) cannot use the Claims Tracking
Number
twice, where as other forms (MA61) can because it was entered previously
under a different valid form (AP20).

I apologize for my ignorance as I'm still fairly new to coding and
databases.


Jeff Boyce said:
Dave

First, Access forms are not data. The data is stored in the tables, so
you
are really checking the tables to see if the data is already there.

Next, "duplicate" can mean whatever you want it to mean ... what do YOU
want
it to mean? One field the same...? All fields identical...?

Finally, rather than forcing your users to enter some data and then
checking
to see if they are using something that's already in the table, what
about
the idea of giving them a way to look up existing values (a combobox) and
add a new value if they don't find it on the list (using the NotInList
event)?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Access 2003, Windows XP

I am trying to actively stop duplicates from being entered on a form
but
cannot seem to get the code to work correctly (or I'm going about it
all
wrong).

The GroupTable holds all of the active records and I would like the
form
to
show that a duplicate is being entered. The trick is there are seven
different forms and some can have this field be a duplicate, others
cannot.

Example:
Form A222 cannot have the Claims_Tracking_Number duplicated on another
form
while form MA61 can.

I've written a query to search for the duplicates on the forms that
cannot
have dups:

SELECT GroupTable.Claims_Tracking_Number, GroupTable.[Name of Form]
FROM GroupTable
WHERE (((GroupTable.Claims_Tracking_Number) In (SELECT
[Claims_Tracking_Number] FROM [GroupTable] As Tmp GROUP BY
[Claims_Tracking_Number] HAVING Count(*)>1 )) AND ((GroupTable.[Name of
Form])=[Forms]![Startup_Split]![FormDataEntry]))
ORDER BY GroupTable.Claims_Tracking_Number;

The Name of Form is entered by the rep on a startup page from a combo
box
and remains open in the background.

How do I get the query to run as an AfterUpdate and alert the rep once
he/she enters a Claims Tracking Number on the data entry form?
Click to expand...
Click to expand...
Click to expand...
 
Dave

If there are (potentially) a number of different ways to determine whether a
[NameOfForm] is allowed "duplicate" [ClaimsTrackingNumber] records, maybe
you need to consider adding a field in the table that holds your
[NameOfForm] records. A Yes/No field [AllowsDuplicates] might be enough.

Or am I still not understanding...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

DaveAP said:
Hopefully this will help solve the duplicate problem. Below are two
fields
in the GroupTable and shown is an example of how I would want Access to
verify if the entry is a duplicate or not.

Name of Form ClaimsTrackingNumber
A222 QA0000000000000001
A222 QA0000000000000001 (Duplicate)
MA61 QA0000000000000003
MA61 QA0000000000000003 (Not a duplicate)
MatchRef QA0000000000000003 (Not a duplicate)
VAR Track QA0000000000000001 (Duplicate)

Some forms are allowed to have the same Claims Tracking Number, others are
not. Does "Dlookup" work with an If/then function?


Jeff Boyce said:
Dave

Check Access HELP for "NotInList" ... the syntax is listed and there's an
example you can copy/adapt.

I still don't have a clear picture how "duplicate" is decided, so I'm
guessing you are not yet able to tell Access how to decide. Can you
provide
more specific details on how to determine this? A couple examples might
help.

Regards

Jeff Boyce
Microsoft Office/Access MVP


DaveAP said:
Thank you for replying Jeff!

Yes, I am trying to check the tables if the data is already there. I
think
my query works but I cannot get it to run on the AfterUpdate event on
the
text box where the rep enters the Claims Tracking Number.

The combo box list for the Claims Tracking Number may get too long
(right
now there's 40,000+ unique tracking numbers). This probably is my best
bet
however, could show me how the NotInList event works?

The definition of a duplicate Claims Tracking Number in this database
is
tricky as mentioned, some forms (A222) cannot use the Claims Tracking
Number
twice, where as other forms (MA61) can because it was entered
previously
under a different valid form (AP20).

I apologize for my ignorance as I'm still fairly new to coding and
databases.


:

Dave

First, Access forms are not data. The data is stored in the tables,
so
you
are really checking the tables to see if the data is already there.

Next, "duplicate" can mean whatever you want it to mean ... what do
YOU
want
it to mean? One field the same...? All fields identical...?

Finally, rather than forcing your users to enter some data and then
checking
to see if they are using something that's already in the table, what
about
the idea of giving them a way to look up existing values (a combobox)
and
add a new value if they don't find it on the list (using the NotInList
event)?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Access 2003, Windows XP

I am trying to actively stop duplicates from being entered on a form
but
cannot seem to get the code to work correctly (or I'm going about it
all
wrong).

The GroupTable holds all of the active records and I would like the
form
to
show that a duplicate is being entered. The trick is there are
seven
different forms and some can have this field be a duplicate, others
cannot.

Example:
Form A222 cannot have the Claims_Tracking_Number duplicated on
another
form
while form MA61 can.

I've written a query to search for the duplicates on the forms that
cannot
have dups:

SELECT GroupTable.Claims_Tracking_Number, GroupTable.[Name of Form]
FROM GroupTable
WHERE (((GroupTable.Claims_Tracking_Number) In (SELECT
[Claims_Tracking_Number] FROM [GroupTable] As Tmp GROUP BY
[Claims_Tracking_Number] HAVING Count(*)>1 )) AND ((GroupTable.[Name
of
Form])=[Forms]![Startup_Split]![FormDataEntry]))
ORDER BY GroupTable.Claims_Tracking_Number;

The Name of Form is entered by the rep on a startup page from a
combo
box
and remains open in the background.

How do I get the query to run as an AfterUpdate and alert the rep
once
he/she enters a Claims Tracking Number on the data entry form?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top