Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Flag 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
Microsoft AccessSales

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
als315

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
ASKER CERTIFIED SOLUTION
Gustav Brock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER
Thank You Gustav it has worked!

Regards

Chris
Gustav Brock

You are welcome!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes