Preventing  Duplicates in Ms Access Tables

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
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

Thank You Gustav it has worked!

Regards

Chris
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial