Prevent Duplicate Data Over 2 Fields

[thmsjlmnt3953]

Hi,

I currently assign multiple 'skills' to departments using a listbox and a combo using the following code

Private Sub cmdSaveReq_Click()
    

  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant

  On Error GoTo ErrorHandler

  Set db = CurrentDb()
  Set rs = db.OpenRecordset("tblRequirements", dbOpenDynaset, dbAppendOnly)

  'make sure a selection has been made
  If Me.SkillsList.ItemsSelected.Count = 0 Then
    MsgBox "No skills selected"
    Exit Sub
  End If

  If IsNull(cmbDept.Value) Then
    MsgBox "No department selected"
    Exit Sub
  End If

  'add selected value(s) to table
  Set ctl = Me.SkillsList
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!required_skill = ctl.ItemData(varItem)
    rs!deptid = Me.cmbDept
    rs.Update
  Next varItem

ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select

End Sub

However as time goes on we may want to add new skills to the depts - ive used dcount to check if the data exists in one field before however how would it be possible to check to see if the data combination exists over deptid and skill

any help would be fab

 

[RainLover]

I am not clear on exactly what you are trying to achieve.

I would like to think that the Code could be little more efficient.

Could you explain in simple terms (English) what it is you are trying to do.

 

[thmsjlmnt3953]

hi,

ill try to explain a little better, i input into a table deptid and required skill,

i want to find a way on entering data that the same combination of deptid and skill cant be added twice?

the code above is how i enter the data, a combo box for dept and a listbox (multi selection)

 

[RainLover]

deptid is I would assume is not only the Primary Key but is also of Type Auto Number. This being the case it can never be duplicated.

If you have a second field of any value even one that has been used must be unique.

One unique field, namely the PK, combined with another field must also be unique.

One Unique Field plus a second field that is either duplicated or is itself unique will always produce a unique combination.

You code is not required from my point of view. What you want can all be accomplished through the properties box of the Auto Numbered Table.

Go to Access Help and read up on INDEXING