Prevent Duplicate ID Values

I have a form with 2 unbound combo boxes called from 2 tables Category and Subcategory they each contain 2 columns CAT_ID, Category and SUBCAT_ID, Subcategory.  those ID Values are then inserted into the CAT_SUBCAT table. I want to prevent duplicate values. in that table the CAT_ID can have duplicate values and the SUBCAT_ID can also but a CAT_ID and a SUBCAT_ID together are unique. So I need a method to check if the same 2 ID values have already been entered into the CAT_SUBCAT table. I tried this code but it errors.
Private Sub cboSubCat_BeforeUpdate(Cancel As Integer)
Dim NewCat, NewSubcat As String
Dim stLinkCriteria As String
'Dim custNo As Integer

'Assign the entered Category and Subcategory to a variable
NewCat = Me.cboCat.Column(0)
NewSubcat = cboSubCat.Column(0)
stLinkCriteria = "[cboCat.Column(0)] = " & "'" & NewCat & "' and [cboSubCat.Column(0)  = " & "'" & NewSubcat & "'"
If cboCat.Column(0) = DLookup("[CAT_ID]", "CAT_SUBCAT", stLinkCriteria) Then

   MsgBox "This Value, " & NewCat & ", has already been entered in database." _
              & vbCr & vbCr & "with addresss " & NewSubcat & "" _
              & vbCr & vbCr & "Please check Entery.", vbInformation, "Duplicate information"
     Me.Undo   'undo the process and clear all fields
    'show the record of matched customer name and address from the customer table
     'custNo = DLookup("[customer_id]", "tbl_customer", stLinkCriteria)
'     Me.DataEntry = False
'     DoCmd.FindRecord custNo, , , , , acCurrent
End If
End Sub

Open in new window

ErrorDebugCAT_SUBCAT_TABLE
skull52IT director Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

ste5anSenior DeveloperCommented:
The only way to do this is a unique constraint over both columns. Also just try to insert to values and handle the error. Any prior check can lead to false positives.
1

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
skull52IT director Author Commented:
That won't work, as I said Each ID column can have the same value multiple times, but a CAT_ID can only have 1 SUBCAT_ID and a SUBCAT_ID can only have 1 CAT_ID

CATSUBCAT_ID      CAT_ID      SUBCAT_ID                      
1                                  19      163
2                                  19      202
3                                  19      261
4                                  19      273
5                                  19      196
6                                  19      209

CATSUBCAT_ID      CAT_ID      SUBCAT_ID
45                                  25      170
93                                  28      170
122                                  33      170
0
Martin LissOlder than dirtCommented:
You can create a collection to which you add items and a key for the item, something like this. If need be the Itemx and/or key values can be a concatenation of two (or more) things separated by the pipe (|) or other character and then split up later. The 457 error indicates that the key is already in the collection.


Dim col As New Collection

On Error Resume Next

col.Add "item1", "Key1"
col.Add "item2", "Key1"
If Err.Number - 457 Then
    'You've added it already
Else
    ' Add it
End If

On Error GoTo 0

Open in new window

0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

skull52IT director Author Commented:
Martin,
I have never worked with collections before. Can you give me more detail as to how i would apply it to my particular situation?
0
Martin LissOlder than dirtCommented:
Try this. BTW, the way you have NewCat defined, it'sa Variant and not a String.

Private Sub cboSubCat_BeforeUpdate(Cancel As Integer)
Dim NewCat, NewSubcat As String
Dim stLinkCriteria As String
Dim col As New Collection

'Dim custNo As Integer

'Assign the entered Category and Subcategory to a variable
NewCat = Me.cboCat.Column(0)
NewSubcat = cboSubCat.Column(0)

On Error Resume Next
col.Add NewCat & "|" & NewSubcat, CStr(NewCat & "|" & NewSubcat)
If Err.Number - 457 Then
        MsgBox "This Value, " & NewCat & ", has already been entered in database." _
               & vbCr & vbCr & "with addresss " & NewSubcat & "" _
               & vbCr & vbCr & "Please check Entery.", vbInformation, "Duplicate information"
Else
    stLinkCriteria = "[cboCat.Column(0)] = " & "'" & NewCat & "' and [cboSubCat.Column(0)  = " & "'" & NewSubcat & "'"
    If cboCat.Column(0) = DLookup("[CAT_ID]", "CAT_SUBCAT", stLinkCriteria) Then
    
         Me.Undo   'undo the process and clear all fields
        'show the record of matched customer name and address from the customer table
         'custNo = DLookup("[customer_id]", "tbl_customer", stLinkCriteria)
    '     Me.DataEntry = False
    '     DoCmd.FindRecord custNo, , , , , acCurrent
    End If
End If
On Error GoTo 0

End Sub

Open in new window

0
skull52IT director Author Commented:
Martin,
Ok... the code fires even if the values are different and if there is a duplicate value it still saves the record.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That won't work, as I said Each ID column can have the same value multiple times, but a CAT_ID can only have 1 SUBCAT_ID and a SUBCAT_ID can only have 1 CAT_ID
Sounds like stefan's suggestion would work. If you make a composite key/index on those two columns and define that composite key as Unique, then the data would enforce unique combinations of  CAT_ID and SUBCAT_ID - which sounds like what you need.
1
skull52IT director Author Commented:
Scott,
How would I go about that
0
ste5anSenior DeveloperCommented:
Indeed. Like this:

Capture9.PNG
1
Fabrice LambertFabrice LambertCommented:
That won't work, as I said Each ID column can have the same value multiple times, but a CAT_ID can only have 1 SUBCAT_ID and a SUBCAT_ID can only have 1 CAT_ID
So, the combo CAT_ID / SUBCAT_ID must be unique ?
That's exactly what a unique index (or primary key) on both columns at the same time is for.
There is no reason for it to not work (or explain why).
0
Martin LissOlder than dirtCommented:
Instead of this in the sub

Dim col As New Collection

put this at the top of the module

Private col As New Collection
0
skull52IT director Author Commented:
OK, i created the index, do i add it to the table
0
skull52IT director Author Commented:
OK creating a unique index prevented the record from being entered but is there anyway to display a message that it was attempted?
0
skull52IT director Author Commented:
Martin,
still fires on unique entery
0
Martin LissOlder than dirtCommented:
Can you attach your workbook?

Try moving the code from the BedforeUpdate event to the Click event.
0
ste5anSenior DeveloperCommented:
Just use error handling in your code. But this depends on your actual implementation. Thus post a sample database.

btw, code..

CAVEAT: VBA uses different variable declaration syntax than VB as Martin already pointed out.

In VBA you need to declare each data type explicitly, otherwise it is Variant. Thus your

Dim NewCat, NewSubcat As String

Open in new window

is equivalent to

Dim NewCat As Variant, NewSubcat As String

Open in new window

0
skull52IT director Author Commented:
Thanks for all the help.
0
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.