upon submit, run duplicate query and display a pop up listing of d

[Jeff @ CI]

In my Access 2000 database, I have a form for entering data on people who
register for an event. Key fields are "First Name", "Last Name", "Event
Code". I am tracking multiple events.

When I enter a registrant's information, I want to click on a "Submit"
button that will run a find duplicates query for only that one event and then
display a pop up form displaying the duplicates - if any are found - and
allow me to modify or delete or keep the records in question. If no
duplicates are found, I do not want anything to occur.

All fields are in one table called contacts. I intend to use the wizard to
create the button with the "Save Record" process. Then insert the coding
needed to perform the above process.

Any help provided would be greatly appreciated.

Thanks in advance, ...

Jeff

 

[Jeff @ CI]

In my Access 2000 database, I have a form for entering data on people who
register for an event. Key fields are "First Name", "Last Name", "Event
Code". I am tracking multiple events.

When I enter a registrant's information, I want to click on a "Submit"
button that will run a find duplicates query for only that one event and then
display a pop up form displaying the duplicates - if any are found - and
allow me to modify or delete or keep the records in question. If no
duplicates are found, I do not want anything to occur.

All fields are in one table called contacts. I intend to use the wizard to
create the button with the "Save Record" process. Then insert the coding
needed to perform the above process.

Any help provided would be greatly appreciated.

Thanks in advance, ...

Jeff

Just to clarify, when the submit button is clicked, the duplicates search
would look first select all records in the current event code, then list
duplicates for those records with the same first and last names.

 

[Michel Walsh]

You can check if a DCount(...) returns 0 or something else (in that case,
there is already a record with the given criteria.

Dim strCriteria AS string
strCriteria="FirstName=FORMS!formNameHere.FirstName "
strCriteria = strCriteria + " AND LastName =
FORMS!FormNameHere.LastName "
strCriteria = strCriteria + "AND Event=FORMS!FormNameHere.Event"

if 0 <> DCount("*", "tableNameHere", strCriteria) Then
' Asking first if the user want to open a form to edit
existing records
' if so ...
DoCmd.OpenForm ... , where:= strCriteria ' note the :=
syntax
'
when using a named argument


if 0 <> DCount("*", "tableNameHere", strCriteria) Then
'there is still dup, what to do? eliminate this
proposed row not entered yet?

Cancel = true
end if

else

'do nothing

end if


Note that DCount counts the rows already committed in the database, not THIS
(assumed NEW) ONE. There are still a couple of things to do, such as asking
if the end user want to edit previous records. If the end user does not what
that, or if, after having edited it, there is still a dup, what we do? I
assume this code is somewhere where you can 'CANCEL' the process to insert
this new record in the database, but clearly, something else can be the
proper way to do it, in your case.



Hoping it may help,
Vanderghast, Access MVP