Preventing Duplicates in Ms Access Tables

Help me to refine the code below , what is required here is that an item must be selected only ONCE from the combo account ANY second selection must be reject . The reasons for that is that the people who will be capturing a sales invoice should not be involved in double entry.

The combo Account ID is populated with only four accounts , that is revenue, discounts, VAT and Excise duty linked as ( 69,73,76 and 92 as Primary Keys), since auto selection appear to be not working ( What I mean if the invoice number is generated then the four accounts in the sub form must be populated 69,73,76 and 92) the only way is to use manual selection  such that we need to prevent several selection of one item in the combo Account ID:

Private Sub AccountID_AfterUpdate()
Dim Cancel As Integer
If (Me.AccountID = "69") Then
Beep
MsgBox "Duplicate are not allowed", vbOKOnly + vbExclamation, "Internal Audit Manager"
Cancel = True
Exit Sub
ElseIf (Me.AccountID = "73") Then
Beep
MsgBox "Duplicate are not allowed", vbOKOnly + vbExclamation, "Internal Audit Manager"
Cancel = True
Exit Sub
ElseIf (Me.AccountID = "75") Then
Beep
MsgBox "Duplicate are not allowed", vbOKOnly + vbExclamation, "Internal Audit Manager"
Cancel = True
Exit Sub
ElseIf (Me.AccountID = "76") Then
Beep
MsgBox "Duplicate are not allowed", vbOKOnly + vbExclamation, "Internal Audit Manager"
Cancel = True
Exit Sub
ElseIf (Me.AccountID = "92") Then
Beep
MsgBox "Duplicate are not allowed", vbOKOnly + vbExclamation, "Internal Audit Manager"
Cancel = True
Exit Sub
End If
End Sub

See how the above code can be changed

I tried to auto populate the the four account by using the form insert event  like below knowing very well that the sub form reference the parent primary key ( For example if the parent form primary key is 10 then all the four accounts lines must have have the same reference which is 10 , the problem here is how to insert or select all the four accounts automatically):

If (Me.Invoice <> "") Then
Me.AccountID = "69"
ElseIf (Me.Invoice <> "") Then
Me.AccountID = "73"
If (Me.Invoice <> "") Then
Me.AccountID = "75"
If (Me.Invoice <> "") Then
Me.AccountID = "76"
If (Me.Invoice <> "") Then
Me.AccountID = "92"
End If

At some point the above was inserting Only the first account throughtout the combo, that is why we need to use manual selection

Regards
Chris
LVL 3
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
Usual way for preventing duplicates is complex index in your tables. You can add to index so many fields as you need and make it unique:
indexSecond way - prevent selection of already existing records in form's queries
If you want to disallow duplicates only for specific indexes, you should revise database structure, bacause something is wrong
Gustav BrockCIOCommented:
It could be like:

Private Sub AccountID_BeforeUpdate(Cancel As Integer)
    
    Select Case Me.AccountID.Value
         Case "69", "73", "75", "76", "92" 
             Cancel = True
    End Select

    If Cancel = True Then
         Beep
         MsgBox "Duplicate are not allowed", vbOKOnly + vbExclamation, "Internal Audit Manager"
    End If

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
Thank You Gustav it has worked!

Regards

Chris
Gustav BrockCIOCommented:
You are welcome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.