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

%3CLINGO-SUB%20id%3D%22lingo-sub-2453318%22%20slang%3D%22en-US%22%3EUse%20verification%20rule%20to%20stop%20input%20of%20duplicate%20values%20in%20a%20field%20of%20another%20form%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2453318%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20new%20here%20and%20new%20to%20Access.%20I%20have%20created%20a%20database%20for%20my%20employer%20to%20track%20job%20records.%20Each%20job%20has%20a%20unique%20job%20number.%20I%20tried%20to%20use%20unique%20indexes%20to%20stop%20duplicate%20values%20for%20that%20field%20in%20the%20%22JOB_TABLE%22%2C%20however%20those%20indexes%20are%20not%20supported%20once%20the%20tables%20have%20been%20linked%20to%20sharepoint.%20I%20can%20no%20longer%20edit%20tables%20since%20they%20have%20been%20linked%20to%20sharepoint%20as%20well.%20I%20would%20like%20to%20use%20the%20verification%20rule%20for%20the%20%22JOB%23%22%20field%20on%20my%20%22JOB_FORM%22%20to%20check%20for%20equal%20values%20in%20the%20%22JOB%23%22%20field%20of%20my%20%22JOB_SHEET%22%20form%20so%20that%20when%20you%20enter%20a%20job%20number%20on%20the%20%22JOB_FORM%22%20that%20already%20exists%20in%20the%20%22JOB_SHEET%22%20the%20verification%20message%20shows%20up%20%22Cannot%20duplicate%20Job%23%22.%20I%20have%20tried%20to%20do%20this%20using%20macros%20and%20expressions%20and%20keep%20getting%20errors%20or%20it%20only%20checks%20against%20the%20last%20number%20in%20the%20%22JOB_SHEET%2C%20JOB%23%22%20form.%20I'm%20assuming%20now%20that%20it%20will%20have%20to%20be%20a%20VBA%20code%20that%20does%20the%20job.%20Is%20there%20anyone%20out%20there%20that%20can%20help%20with%20this%3F%20Any%20input%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKiefer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2453318%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2454079%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20verification%20rule%20to%20stop%20input%20of%20duplicate%20values%20in%20a%20field%20of%20another%20form%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2454079%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhy%20do%20you%20want%20to%20check%20for%20the%20duplicate%20in%20another%20form%20instead%20of%20in%20the%20table%3F%3C%2FP%3E%0A%3CP%3EIf%20they%20write%20to%20the%20same%20table%20you%20can%20use%20VBA%20code%20like%20this%20e.g.%20in%20the%20Before%20Update%20event%20of%20a%20JobNo%20textbox%3A%3CBR%20%2F%3EIf%20Not%20IsNull(Dlookup(%22JobNo%22%2C%20%22JOB_TABLE%22%2C%20_%3CBR%20%2F%3E%22JobNo%20%3D%20%22%20%26amp%3B%20Me!JobNo))%20_%3CBR%20%2F%3EAnd%20Me!JobNo%20%26lt%3B%26gt%3B%20nz(Me!JobNo.OldValue)%20Then%3C%2FP%3E%0A%3CP%3EMsgBox%20Me!JobNo%20%26amp%3B%20%22%20already%20exists.%22%2C%20vbOKOnly%2C%20%22Duplicate!%22%3CBR%20%2F%3ECancel%20%3D%20True%3C%2FP%3E%0A%3CP%3EEnd%20If%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%22-1%22%3E%3CFONT%20face%3D%22Verdana%22%3EServus%3CBR%20%2F%3EKarl%3CBR%20%2F%3E*********%3CBR%20%2F%3E%3CA%20href%3D%22http%3A%2F%2FAccessDevCon.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttp%3A%2F%2FAccessDevCon.com%3C%2FA%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.donkarl.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.donkarl.com%3C%2FA%3E%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2454322%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20verification%20rule%20to%20stop%20input%20of%20duplicate%20values%20in%20a%20field%20of%20another%20form%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2454322%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127914%22%20target%3D%22_blank%22%3E%40Karl%20Donaubauer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKarl%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20input.%20I%20tried%20what%20you%20suggested.%20However%2C%20I%20keep%20getting%20an%20error%20where%20Access%20is%20telling%20me%20it%20cannot%20find%20the%20respective%20field%20%22JOB%22.%20As%20the%20actual%20field%20name%20in%20the%20table%20is%20%22JOB%23%22%2C%20I%20am%20wondering%20if%20it%20does%20not%20recognize%20the%20%22%23%22%20character.%20I%20could%20try%20changing%20the%20field%20name%20in%20the%20Sharepoint%20table%20to%20what%20you%20have%20in%20your%20code%20as%20%22JobNo%22.%20However%2C%20I%20fear%20this%20may%20break%20other%20relationships%20within%20the%20database%20after%20update.%20Here%20is%20the%20code%20I%20entered%20in%20the%20BeforeUpdate%20area%20of%20the%20textbox%20control.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20Not%20IsNull(Dlookup(%22JOB%23%22%2C%20%22JOB_TABLE%22%2C%20_%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%22JOB%23%20%3D%20%22%20%26amp%3B%20Me!JOB%23))%20_%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20And%20Me!JOB%23%20%26lt%3B%26gt%3B%20nz(Me!JOB%23.OldValue)%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20MsgBox%20Me!JOB%23%20%26amp%3B%20%22%20already%20exists.%22%2C%20vbOKOnly%2C%20%22Duplicate!%22%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20Cancel%20%3D%20True%3C%2FP%3E%3CP%3EEnd%20If%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThoughts%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2454360%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20verification%20rule%20to%20stop%20input%20of%20duplicate%20values%20in%20a%20field%20of%20another%20form%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2454360%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESpecial%20characters%20like%20%23%20in%20object%20or%20field%20names%20very%20often%20cause%20problems%20and%20should%20be%20avoided.%20If%20you%20are%20able%20to%20change%20that%20name%20then%20do%20it%20ASAP.%20Meanwhile%20you%20have%20to%20always%20enclose%20the%20name%20in%20square%20brackets%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20Not%20IsNull(Dlookup(%22%5BJOB%23%5D%22%2C%20%22JOB_TABLE%22%2C%20_%3CBR%20%2F%3E%22%5BJOB%23%5D%20%3D%20%22%20%26amp%3B%20Me!%5BJOB%23%5D))%20_%3CBR%20%2F%3EAnd%20Me!%5BJOB%23%5D%20%26lt%3B%26gt%3B%20nz(Me!%5BJOB%23%5D.OldValue)%20Then%3C%2FP%3E%0A%3CP%3EMsgBox%20Me!%5BJOB%23%5D%20%26amp%3B%20%22%20already%20exists.%22%2C%20vbOKOnly%2C%20%22Duplicate!%22%3CBR%20%2F%3ECancel%20%3D%20True%3C%2FP%3E%0A%3CP%3EEnd%20If%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%22-1%22%3E%3CFONT%20face%3D%22Verdana%22%3EServus%3CBR%20%2F%3EKarl%3CBR%20%2F%3E*********%3CBR%20%2F%3E%3CA%20href%3D%22http%3A%2F%2FAccessDevCon.com%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttp%3A%2F%2FAccessDevCon.com%3C%2FA%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.donkarl.com%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.donkarl.com%3C%2FA%3E%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 values for that field in the "JOB_TABLE", however those indexes are not supported once the tables have been linked to sharepoint. I can no longer edit tables since they have been linked to sharepoint as well. I would like to use the verification rule for the "JOB#" field on my "JOB_FORM" to check for equal values in the "JOB#" field of my "JOB_SHEET" form so that when you enter a job number on the "JOB_FORM" that already exists in the "JOB_SHEET" the verification message shows up "Cannot duplicate Job#". I have tried to do this using macros and expressions and keep getting errors or it only checks against the last number in the "JOB_SHEET, JOB#" form. I'm assuming now that it will have to be a VBA code that does the job. Is there anyone out there that can help with this? Any input would be greatly appreciated.

 

Thanks,

 

Kiefer

6 Replies

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

@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? 

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

@kiefertrey Do you not have permissions to see the linked SharePoint lists which are tables in your Access FE?

It's a over an hour long, but Albert Kallal's recent presentation might be useful here. I believe it addresses the issue of modifying SP lists, but it's very useful anyway. Have a look when you have time.

Hi,

 

Just to add some details to George's comment on the Sharepoint design changes:

 

If you have the permissions to do changes on the Sharepoint list, you could set the field to accept only unique values. This is of course possible in the column settings in Sharepoint itself but as Albert showed in the mentioned video even in Access. You can open the linked Sharepoint list in datasheet view, choose the Fields tab in the ribbon and change the Unique setting for the column.

 

In case the Fields tab is greyed out for the Sharepoint list/table you first have to check the option to use the cache format that is compatible with Access 2010 and later in File - Option - Current Database - Caching Web Service and Sharepoint tables.

 

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

@Karl Donaubauer @George Hepworth  Guys. This sounds great. I will definitely check it out. Thanks a bunch!