Need help with Index

[Smecker]

I'm working on a database to store user information for an application at work. It contains the user's name, email, etc and login information. Login IDs are 4 numbers and 2 letters (like 1234AB). The way this particular application works is when an account is disabled the login ID is deleted from the system. This means that sometime in the future it would be possible for the same number/letter combo to be created later attached to a different user account. I have a field in the DB for loginID and another for Status (ACTIVE or TERMED are the options). I'd like to be able to setup an index so that active accounts can't be duplicated, but doesn't care about termed accounts being duplicated. Is this possible in Access?

[NeoPa]

You could set up a unique index consisting of both the [LogonID] and the [Status] fields. This would allow the same [LogonID] value in an ACTIVE record as another in a TERMED record. Notice the an though. It would not allow multiple TERMED records with the same [LogonID]. Even though you didn't specify this as a requirement, I suspect it is.

An alternative (not a good one I don't believe) is to copy the TERMED records into a separate table. Nasty idea. Don't do it.

Otherwise you can manage it yourself without specifying the index be unique.

[Smecker]

Thanks, NeoPa. That's what I was thinking, and you're right about the options not being very good. I was wondering, is it possible to make a validation rule on the [LoginID] field to check for duplicates based on status? This would remove the index issue.

[NeoPa]

I'm pretty sure the validation rule has no access to other data in the dataset, so I'm guessing that won't work.

[ADezii]

Not sure if the following would be appropriate in your case, but if you wished to Drop the Index, you could Validate User Input in the BeforeUpdate() Event of your Form. The following Code will NOT allow Duplication on ACTIVE Accounts for a specific Login ID, but will for TERMED:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
 
If IsNull(Me![txtLoginID]) Or IsNull(Me![txtStatus]) Then
  MsgBox "Both the Login ID and Status Fields are required!"
    Cancel = True
Else
  'Do not allow Duplication on 'ACTIVE' Accounts for the same Login ID, but
  'allow Duplication on the ID for 'TERMED Accounts
  If Me![txtStatus] = "ACTIVE" Then
    strWhere = "[LoginID] = '" & Me![txtLoginID] & "' AND [Status] = '" & _
                Me![txtStatus] & "'"
      If DCount("*", "tblUserInfo", strWhere) > 0 Then
        MsgBox "You cannot have the same ACTIVE, Login IDs"
          Cancel = True
      End If
  End If
End If
End Sub