Notification-Cancellation of duplicate entry

[indyaries]

Greetings,

I am using Access 97 at work, Access 2000 at home. This is a work project.

I have a database that tracks employee awards. One type of award is the Peer Award. An employee can receive more than one award during the reporting period (01 May 05 through 30 April 06), but an employee can nominate someone only ONCE during this period. The query also filters all award for the word PEER in the Award_Type field.

I need a method in my subform (where all of the award info is entered) to display a message if the NOMINATED_BY field contains the name of a nominator more than once. I am using a combobox (cboNominated_By) to select the nominator from a query based on the Employee_Name table.

I had thought of making the field NO DUPLICATES, but cannot do that, as I also keep these records as multi-year archives.

In my query I have as the criteria to limit records for the reporting period above.

In my subform, is there a method to check the combobox that I'm using for a duplicate nominator, then notify the user of this duplicate?

I've seen something similar to what I want in the below thread, but FoFa didn't offer an example of how this would be accomplished;
Notification of Duplicate Records
http://www.access-programmers.co.uk/forums/showpost.php?p=158082&postcount=2

Thanks all for any assistance you might offer!

 

[indyaries]

Hmmm....no responses yet. Well, below are my attempts to make this work.

Here, I wanted the combo box for the Nominator to remain invisible unless the Award Type combo box has the word Peer in it. It works (sort of), but only if I leave the record, and then return. I'd like the combo box cboNominated_By to be visible immediately after the focus leaves the Award_Type field.

Private Sub cboAward_Type_AfterUpdate()
If cboAward_Type.Value = "*Peer*" Then
Me!cboNominated_By.Visible = True
Else
Me!cboNominated_By.Visible = False
End If
End Sub
****************************

The below code does NOT work;

Private Sub cboNominated_By_BeforeUpdate(Cancel As Integer)
If DCount("[cboNominated_By]", _
"[t_Peer_Award_CHECK]", _
"[Nominated_By] = '" & Me.cboNominated_By & "'") Then
MsgBox "This has already nominated this peer-year (01May05-30Apt05)!!"
Cancel = True
Me.Undo
Else 'Do nothing
End If
End Sub
****************************

Does anyone have any suggestions to get this working?

Thanks again.

 

[Bat17]

when you say it doesn't work, what does happen?
Fail to put up when there is a duplicate? flash up when there is no duplicate?

Peter

 

[indyaries]

Hello Bat17,

Absolutely nothing happens. The record is allowed to save even though a duplicate exists.

 

[indyaries]

Hello Bat17,

Absolutely nothing happens. The record is allowed to save even though a duplicate exists. The message box does not appear.

 

[Bat17]

Which means the DCount is returning 0 so you need to look to the where clause,
I would check the rowsource for Me.cboNominated_By, normaly i would expect this would return an id number but show the name of the person.
I would test the Dcount from the immediate window (Ctr-G) with real values

?DCount("[cboNominated_By]", "[t_Peer_Award_CHECK]", "[Nominated_By] = 'Joe Smith'")
or somesuch that should return a count greater than 0 .

HTH

Peter