Solved Prevent Duplicate entry in access table via form (Upper and lower case sensitive) (1 Viewer)

Ihk

Member
Local time
Today, 06:25
Joined
Apr 7, 2020
Messages
280
I am new to this, and facing a problem I am looking for correct vba code for access to prevent duplicate entry, with message alert. Follwoing code works very good. but problem with this is "if Lower case to upper case or vice versa" is changed, this code does not recognise and accepts the entry.

In my table field name is "Cat_No" , table name is "ArticlesDetails"

Code:
Private Sub TextCat_No_BeforeUpdate(Cancel As Integer)
If Me.TextCat_No.Value = DLookup("[Cat_No]", "ArticlesDetails", "[Cat_No] = '" & Me.TextCat_No.Value & "'") Then
Cancel = True
MsgBox "This Catalogue number already exists, Duplicate entry not allowed", vbCritical, "Duplicate Entry"
End If
End Sub

For Example if Cat_No "T330" already exists, but if user changes to "t330" it is accepted by the system.

I can change the table field index "Duplicate no" but I want to present a message to users as above.
 

plog

Banishment Pending
Local time
Today, 00:25
Joined
May 11, 2011
Messages
11,613
Upper case both sides of your comparison:

 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:25
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF! You could also try StrComp(). For example:
Rich (BB code):
Private Sub TextCat_No_BeforeUpdate(Cancel As Integer)
If StrComp(Me.TextCat_No.Value, DLookup("[Cat_No]", "ArticlesDetails", "[Cat_No] = '" & Me.TextCat_No.Value & "'"), vbBinaryCompare) > 0 Then
    Cancel = True
    MsgBox "This Catalogue number already exists, Duplicate entry not allowed", vbCritical, "Duplicate Entry"
End If
End Sub
(untested)
Hope that helps...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:25
Joined
May 7, 2009
Messages
19,169
I think it would be best to consider using Recordset. Why?
you are saving Exact text.
You may have "Cat", "cAt", "caT", "CAt" on your table (these are different words when using Exact match):
Code:
Private Sub TextCat_No_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
If Trim(Me.TextCat_No & "") = "" Then
    Cancel = True
    Msgbox "No Catalogue number was inputted", vbInformation + vbOkOnly, "TextCat"
    Me.TextCat_No.SetFocus
    Exit Sub
End If
Set db = Currentdb
With db.OpenRecordset("SELECT Cat_No From ArticlesDetails Where Cat_No = " & Chr(34) & Me.TextCat_No & Chr(34), dbOpenSnapShot)
    ' why Recordset?
    ' for what we know, you are using Exact match.
     ' therefore you may have records like "CAT", "cat", "Cat", "caT", which
    ' are different when you save it and search for exact match.
    If Not (.BOF And .EOF) Then
       .MoveFirst
       Do While Not .EOF
           Cancel = (StrComp(Me.TextCat_No, .Fields(0) & "") <> 0)
           If Cancel Then Exit Do
           .MoveNext
       Loop
    End If
End With
If Cancel Then
    MsgBox "This Catalogue number already exists, Duplicate entry not allowed", vbCritical, "Duplicate Entry"
End If
End Sub
 

Ihk

Member
Local time
Today, 06:25
Joined
Apr 7, 2020
Messages
280
I am very thank full for every body for your effort and input.
I tried above codes, but did not solve my problem.

Information for any one else, looking for answer of same question like me. Below is the solution.
I used Comparison in DLOOKUP in my above formula. Now it worked like Magic, This is not case sensitive now. This formula will prevent entry, whether entry is Upper Case or Lower Case (For example Either "T300" or "t300") - All were prevented.
Code:
If Not IsNull(DLookup("[Cat_No]", "ArticlesDetails", "[Cat_No] = '" & Me.TextCat_No.Value & "'")) Then
 

missinglinq

AWF VIP
Local time
Today, 01:25
Joined
Jun 20, 2003
Messages
6,423
At the top of the code module for the Form...you could simply replace

Option Compare Database

with

Option Compare Binary

and all comparisons, for that Form, will automatically be case sensitive.

Linq ;0)>
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:25
Joined
May 7, 2009
Messages
19,169
hmm, I thought the topic is Case Sensitive, why the shift.
comparison operator will respect the Option Compare Binary, yes.
but not Dlookup(), it will fetch the first occurrence (not case sensitive).
 

Users who are viewing this thread

Top Bottom