Forum Discussion

kiefertrey's avatar
kiefertrey
Copper Contributor
Jun 16, 2021

Use verification rule to stop input of duplicate values in a field of another form?

Hello all,   I am new here and new to Access. I have created a database for my employer to track job records. Each job has a unique job number. I tried to use unique indexes to stop duplicate value...
access
Karl_Donaubauer's avatar
Karl_Donaubauer
MVP
Jun 16, 2021

Hi,

 

Why do you want to check for the duplicate in another form instead of in the table?

If they write to the same table you can use VBA code like this in the Before Update event of a JobNo textbox:


If Not IsNull(Dlookup("JobNo", "JOB_TABLE", _
    "JobNo = " & Me!JobNo)) _
    And Me!JobNo <> nz(Me!JobNo.OldValue) Then

    MsgBox Me!JobNo & " already exists.", vbOKOnly, "Duplicate!"
    Cancel = True

End If

 

Servus
Karl
*********
http://AccessDevCon.com
https://www.donkarl.com

  • kiefertrey's avatar
    kiefertrey
    Copper Contributor
    Jun 16, 2021

    Karl_Donaubauer 

     

    Karl,

     

    Thank you very much for your input. I tried what you suggested. However, I keep getting an error where Access is telling me it cannot find the respective field "JOB". As the actual field name in the table is "JOB#", I am wondering if it does not recognize the "#" character. I could try changing the field name in the Sharepoint table to what you have in your code as "JobNo". However, I fear this may break other relationships within the database after update. Here is the code I entered in the BeforeUpdate area of the textbox control.

     

    If Not IsNull(Dlookup("JOB#", "JOB_TABLE", _
        "JOB# = " & Me!JOB#)) _
        And Me!JOB# <> nz(Me!JOB#.OldValue) Then

        MsgBox Me!JOB# & " already exists.", vbOKOnly, "Duplicate!"
        Cancel = True

    End If

     

    Thoughts? 

    • Karl_Donaubauer's avatar
      Karl_Donaubauer
      MVP
      Jun 16, 2021

      Hi,

       

      Special characters like # in object or field names very often cause problems and should be avoided. If you are able to change that name then do it ASAP. Meanwhile you have to always enclose the name in square brackets:

       

      If Not IsNull(Dlookup("[JOB#]", "JOB_TABLE", _
      "[JOB#] = " & Me![JOB#])) _
      And Me![JOB#] <> nz(Me![JOB#].OldValue) Then

      MsgBox Me![JOB#] & " already exists.", vbOKOnly, "Duplicate!"
      Cancel = True

      End If

       

      Servus
      Karl
      *********
      http://AccessDevCon.com
      https://www.donkarl.com

Resources