By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,379 Members | 3,035 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,379 IT Pros & Developers. It's quick & easy.

Need help with Index

P: 11
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?
Feb 3 '12 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 15k+
P: 31,494
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.
Feb 3 '12 #2

P: 11
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.
Feb 3 '12 #3

Expert Mod 15k+
P: 31,494
I'm pretty sure the validation rule has no access to other data in the dataset, so I'm guessing that won't work.
Feb 4 '12 #4

Expert 5K+
P: 8,638
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. Dim strWhere As String
  4. If IsNull(Me![txtLoginID]) Or IsNull(Me![txtStatus]) Then
  5.   MsgBox "Both the Login ID and Status Fields are required!"
  6.     Cancel = True
  7. Else
  8.   'Do not allow Duplication on 'ACTIVE' Accounts for the same Login ID, but
  9.   'allow Duplication on the ID for 'TERMED Accounts
  10.   If Me![txtStatus] = "ACTIVE" Then
  11.     strWhere = "[LoginID] = '" & Me![txtLoginID] & "' AND [Status] = '" & _
  12.                 Me![txtStatus] & "'"
  13.       If DCount("*", "tblUserInfo", strWhere) > 0 Then
  14.         MsgBox "You cannot have the same ACTIVE, Login IDs"
  15.           Cancel = True
  16.       End If
  17.   End If
  18. End If
  19. End Sub
Feb 5 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.