Link to home
Start Free TrialLog in
Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScFlag for Zambia

asked on

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
Avatar of als315
als315
Flag of Russian Federation image

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:
User generated imageSecond 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
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

Thank You Gustav it has worked!

Regards

Chris
You are welcome!