How to stop duplicate entries on a form

[Fish521]

Hi,

I have looked through the threads and FAQ's but didn't see anything that looked like it would help me with this. What I am trying to do is make sure that only one entry is made for a certain month in a year. On the form I have these drop downs:

Team
Employee
Month
Year
Samples

Is there some way to make sure that the employee is only entered once, so another person can't change the information. If this is not clear or you need some more information please let me know.

Thanks,

 

[PHV]

To avoid duplication (even when data is entered directly in table view), the safest way is a composite unique index.
To prevent the duplicate error message when entering data in a form, a common way is to play with the DLookUp function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GooGoocluster]

a simple way to make a composite index is to take the table that has those fields that will be unique and select them then press the primary key button. this will stop any dupicates.

 

[MajP]

A composite index and a composite primary key are not the same thing and could cause some unintended consequences unless you truly want a composite primary key. Go to the lightning bolt icon next to the key. In the first column give the index a name. In the next column list your fields down the column. Click on the index name and at the bottom of the form choose unique.

 

[Fish521]

Thanks everyone for the information

 

[MajP]

You may want to add your own error message to the form if the user inputs a duplicate key to avoid the standard cryptic Access error message. Here is an example

Private Sub Form_Error(DataErr As Integer, Response As Integer)
  Const adhcErrDuplicateKey = 3022
  Dim strMsg As String
  Select Case DataErr
    Case adhcErrDuplicateKey
      strMsg = "The Study / Project name must be unique. " & _
      "You added a name that already exists."
      strMsg = strMsg & Chr(13) & "Please try a new name, or escape to undo changes."
      MsgBox strMsg, vbExclamation
      Response = acDataErrContinue
    Case Else
      Response = acDataErrDisplay
 End Select
End Sub