look up duplicates on form

[fredk]

Hi - I am trying to eliminate duplicate records being entered in my database - We use a "group number" to identify a group - There are two parts - The sub-group number (called [subgrp]) and the main group number (called [maingrp]

There can be more than one entry for a group number (in limited situations) However, I want to give the person a message if the maingrp field finds a duplicate record - Preferably one that has been entered in the last two weeks.

Is that possible and if so, how? Also, will this lookup slow down my database significantly?

Thank you very much for your assistance!!!!

 

[jgoodman00]

Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT GroupNo, DateField FROM tblName WHERE PrimaryKey ='" & Me.PrimaryKeyField & "'&quot
If rst.RecordCount > 0 Then
If rst!GroupNo = 1 Then
If DateDiff(&quot;ww&quot;, rst!DateField, Now) <= 2 Then
MsgBox &quot;Group already exists!&quot;, vbExclamation
'Do something
Else
'Do nothing
End If
Else
'Do nothing
End If
Else
MsgBox &quot;Record not found&quot;
End If

This should be something along the lines of what you are looking for. James Goodman

 

[fredk]

Thank you very much for your assistance!!! I appreciate it!

 

[wabtrainer]

I have a problem that is vexing me, can anyone help!
I have a table called Attendee, containing standard contact details, FirstName, LastName etc...
From this table i have two forms:
AttendeeAdd and AttendeeEdit (AttendeeAdd was copied from AttendeeEdit, just renamed and set to Add mode)
When entering names into the form I wish to test to see if there are any duplicate names already in the database and rather than fill out the whole form I think it should be on the OnExit event of the LastName control.
If there are same names I would like the oportunity to open a filtered view of these records to edit or close without updating the database with the input details.
At the moment I am getting a message whether there are duplicates or not!!

 

[jgoodman00]

You dont actually need 2forms to do this. You can simply set its state on opening, either AcFormEdit or acFormAdd.

You should use the afterupdate event of the Last control (probably Surname).

Not entirely sure what you are after, but I think something like the following will work:

Dim db As DAO.Database, rst As DAO.Recordset
Dim ID As Integer 'This will be used to open a new instance of the form.

'Define the recordset & open it
Set db = CurrentDb
Set rst = db.OpenRecordset(&quot;SELECT * FROM Attendee&quot

'Check the recordset has opened successfully & it contains records.
If rst.RecordCount > 0 Then
rst.FindFirst &quot;ForeName = '&quot; & txtForeName & &quot;' AND Surname = '&quot; & txtSurname & &quot;'&quot;
'Record does not already exist
If rst.EOF = True Then
'Do nothing, let the update continue
Else
If MsgBox(&quot;A person with this name already exists. Would you like to view their details?&quot;, vbYesNo) = vbYes Then
ID = rst!PrimaryKeyField
DoCmd.OpenForm &quot;frmAttendee&quot;, , , &quot;ID = &quot; & ID, acFormEdit
Else
'Do something else
End If
End If
End If


However, I have not used the FindFirst method before. I am also not certain of your table structure, so it probably needs altering...

James Goodman

 

[wabtrainer]

Afraid this does not seem to work. The message box appears whether the name is already in or not. Also when I select yes to view I am getting a runtime error:
rst!PrimaryKeyField = <item not found in this collection>
The code has been changed a wee bit to use my field names so Im not sure if I have altered any thing. The code I am including in the event is:
Dim db As DAO.Database, rst As DAO.Recordset
Dim ID As Integer 'This will be used to open a new instance of the form.

'Define the recordset & open it
Set db = CurrentDb
Set rst = db.OpenRecordset(&quot;SELECT * FROM Attendees&quot

'Check the recordset has opened successfully & it contains records.
If rst.RecordCount > 0 Then
rst.FindFirst &quot;AttendeeFirstName = '&quot; & txtAttendeeFirstName & &quot;' AND AttendeeLastName = '&quot; & txtAttendeeLastName & &quot;'&quot;
'Record does not already exist
If rst.EOF = True Then
'Do nothing, let the update continue
Else
If MsgBox(&quot;A person with this name already exists. Would you like to view their details?&quot;, vbYesNo) = vbYes Then
ID = rst!PrimaryKeyField
DoCmd.OpenForm &quot;Attendees&quot;, , , &quot;ID = &quot; & ID, acFormEdit
Else
'Do something else
End If
End If
End If

 

[jgoodman00]

I included rst!PrimaryKeyField as a demonstration. You need to replace PrimaryKeyField with the name of the field which is the Primary Key in your table. I have assigned it as an integer variable, assuming your primary key is an integer value (autonumber).


I will have a look at the other part...
James Goodman

 

[wabtrainer]

Great! that worked, the runtime error has gone. But!!! when I click Yes to view I get a modal &quot;Enter parameter value&quot; asking for ID instead of opening the filtered form.
Thanks for the tips though.
I should have come to this site ages ago, sharing the problem has made things clearer. Cheers

 

[jgoodman00]

Try this:


Dim db As DAO.Database, rst As DAO.Recordset
Dim ID As Long 'This will be used to open a new instance of the form.

'Define the recordset & open it
Set db = CurrentDb
Set rst = db.OpenRecordset(&quot;SELECT * FROM Attendees&quot

'Check the recordset has opened successfully & it contains records.
If rst.RecordCount > 0 Then
rst.FindFirst &quot;AttendeeFirstName = '&quot; & txtAttendeeFirstName & &quot;' AND AttendeeLastName = '&quot; & txtAttendeeLastName & &quot;'&quot;
'Record does not already exist
If rst.NoMatch Then
'Do nothing, let the update continue
Else
If MsgBox(&quot;A person with this name already exists. Would you like to view their details?&quot;, vbYesNo) = vbYes Then
ID = rst!PrimaryKeyField '------This should be your primary key field
DoCmd.OpenForm &quot;Attendees&quot;, , , &quot;ID = &quot; & ID, acFormEdit
Else
'Do something else
End If
End If
End If
James Goodman

 

[jgoodman00]

You need to replace the &quot;ID ='&quot; part with the name of your primary key field.
e.g.
&quot;PrimaryKeyField - '&quot;

This should resolve your problem... James Goodman

 

[wabtrainer]

Ok here is the code I now have (with PrimaryKeyField set to primary key name for the Attendees table, which as you say is an AutoNumber data type)
I have two people in the database and when i enter the same name, nothing happens!!! When I open the form in edit mode from the switchboard, the new name has been added. i.e. a duplicate name has been entered.


Dim db As DAO.Database, rst As DAO.Recordset
Dim ID As Long 'This will be used to open a new instance of the form.

'Define the recordset & open it
Set db = CurrentDb
Set rst = db.OpenRecordset(&quot;SELECT * FROM Attendees&quot

'Check the recordset has opened successfully & it contains records.
If rst.RecordCount > 0 Then
rst.FindFirst &quot;AttendeeFirstName = '&quot; & txtAttendeeFirstName & &quot;' AND AttendeeLastName = '&quot; & txtAttendeeLastName & &quot;'&quot;
'Record does not already exist
If rst.NoMatch Then
'Do nothing, let the update continue
Else
If MsgBox(&quot;A person with this name already exists. Would you like to view their details?&quot;, vbYesNo) = vbYes Then
ID = rst!AttendeeID
DoCmd.OpenForm &quot;Attendees&quot;, , , &quot;ID = &quot; & ID, acFormEdit
Else
'Do something else
End If
End If
End If

 

[jgoodman00]

I tried this code on our own database, with no trouble. It successfully found any existing records & flagged the message box.

Are you entering the same FirstName & Surname, including any Uppercase characters??

James Goodman

 

[wabtrainer]

I am modifying a legacy system and that may be the prob.
I,ll rebuild this portion and get back after the weekend, which I hope will be a relaxing one for you!!!