troubleshooting Question

Preventing Duplicates in Ms Access Tables

Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScFlag for Zambia asked on
Microsoft AccessSales
4 Comments1 Solution111 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros