Preventing Duplicating Fields

[shaz123]

Hi,
Just a quick question i have a table "tbl_TrnasactionMaster", with the follwing fields

Employee ID, User Name and Time, Time of Transaction,

What i need to do is to prevent the same record form being entered more then once, for instance the folwiing should not be allowed.

Employee ID, User Name and Time, Time of Transaction
200 GA0200 20/11/2006 9.13
200 GA0200 20/11/2006 9.13

But the following can occur, there is a diffrence between the two times the transaction occured.

Employee ID, User Name and Time, Time of Transaction
200 GA0200 20/11/2006 9.13
200 GA0200 20/11/2006 9.23

Would this be made possible by setting the index criteria for all three fields to no duplicates?.

 

[Remou]

Yes, it is. In table design view you can select all three fields and mark them as the primary key, or you can add and index name that refers to the three field (View->Indexes).

 

[shaz123]

Thankyou for ur quick repsponse ill try it

 

[ragu111]

Try to place below code on the DateTime fields BeforeUpdate Event

Private Sub Date_BeforeUpdate(Cancel As Integer)
Dim MyDb As Database
Dim MySet As Recordset
Dim SQLStg As String

Set MyDb = CurrentDb
SQLStg = "SELECT Count(EmployeeID) AS CountOfempID, UserName, Date"
SQLStg = SQLStg & "FROM tbl_TrnasactionMaster"
SQLStg = SQLStg & "GROUP BY UserName, Date;"
SQLStg = SQLStg & "HAVING dATE = " & Me.frmDateField & ";"

Set MySet = MyDb.OpenRecordset(SQLStg)
If MySet!CountOfempID >= 2 Then
MsgBox "You're trying to create duplicate record", vbCritical
Cancel = True
End If
MySet.Close
Set MySet = Nothing

End Sub

Ragu

 

[IGPCS]

romou's suggestion is the right and simple way

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[shaz123]

oh ok thanxs for the advice, i ahve tried romous way and it has worked.

Thank for everyones help