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
skull52Asked:
Who is Participating?
 
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
 
skull52Author 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
skull52Author 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
 
skull52Author 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
 
skull52Author 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
 
skull52Author Commented:
OK, i created the index, do i add it to the table
0
 
skull52Author 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
 
skull52Author 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
 
skull52Author Commented:
Thanks for all the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.