1

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. Following 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"

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.

June7
  • 19,874
  • 8
  • 24
  • 34
Ihs
  • 11
  • 2
  • If you have successfully prevented *t300* from being in the database you can change the search criterium for DLOOKUP to upper case and only T300 will be found because t300 neither exists nor is it looked for. If this way isn't available to you try another way of looking for the duplicate. https://stackoverflow.com/questions/10046627/how-to-write-case-sensitive-query-for-ms-access – Variatus Apr 25 '20 at 01:55
  • If you want only upper case saved, then use code in control's AfterUpdate event to modify user input so regardless of case entered, only upper case is saved. `Me!Cat_No = UCase(Me.Cat_No)`. Access is not case sensitive by default so it really doesn't matter if user types upper or lower case, Access will consider `t=T` and prevent duplicate entry. – June7 Apr 25 '20 at 04:08

1 Answers1

0

Comparison in DLookup is not case sensitive, so this should work:

If Not IsNull(DLookup("[Cat_No]", "ArticlesDetails", "[Cat_No] = '" & Me.TextCat_No.Value & "'")) Then
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • 1
    I am really very thankful for your help. This code really worded like a magic. Now it is perfectly preventing duplicate entry, irrespective of whether word is upper case or lower case. – Ihs Apr 25 '20 at 10:22