Validation rule to avoid duplicate values

[Gregory23]

Hi ,

I would like to check if there is a way setup a validation rule to avoid typing the same value as an already existing record in the Table.

Tried the below but it does not seem to work

dlookup("Field name","Table name,"Field name")



Any advice would be appreciated

Thanks

[ranman256]

close:
dlookup("Field name","Table name",where clause)

vRet = dlookup("[lastname]","tClients,"[LastName]='" & txtBox & "'")

if the text box has SMITH, the command will return SMITH if it exists,
NULL if it does not.

If you are using a lookup table , keyed values will never allow duplicates.
people are the problem, since you can have duplicates.
What data are you trying to validate?

[Gregory23]

Thank you for the suggestion

This is a number field and it stores a document reference number

is there a way to have a msgbox popup if there is a value which already exist in the table but still able to proceed ?

[ranman256]

vRet = dlookup("[DocNum]","tDocs,"DocNum=" & txtBox )
if not IsNull(vRet) then msgbox "This already exists"

[Gregory23]

Thank you but unfortunately it does not work , I get Invalid Syntax

Maybe it is easier if I specify the names
Table : Redressing complaint log
Field name : RV no
Data Type : Number

[June7]

Advise not to use spaces in names. If you do, they must be enclosed in []. Post your exact attempted code.

[Gregory23]

Please see below

vRet=dlookup("[RV no]","Redressing complaint log,"RV no="&txtBox)
if not IsNull(vRet) then msgbox "This already exists"

[orange]

An attempt to add a duplicate value on the key field will be given a message with error number 3022.

If [RV no] is the PK or a unique index you should get a message/error. You can test this to confirm.

If [RV no] is not the key field or an index, I would suggest

If DCount("*","Redressing complaint log,"[RV no]" = & Me.txtbox) > 0 Then 'you have a duplicate so do something here

I would adjust your field names to remove all spaces.

[ArviLaanemets]

Can this value be Null or not?

When it can't be Null, define unique index based on this field - you aren't allowed to save a record when having duplicate in this field then.

[June7]

As already stated, use []s or remove spaces from names. Also missing a quote mark. Spaces on each side of &.

DLookup("[RV no]", "[Redressing complaint log]", "[RV no]=" & Me.txtBox")

[Gregory23]

Hi June,

I get the below error msg "invalid string"

[June7]

Where are you using this expression? If it is VBA, post the procedure.

Is [RV no] a text type field? If it is then use apostrophe delimiters as shown in post 2 example. And sorry, my example had an extra quote typo.

If number type field:

DLookup("[RV no]", "[Redressing complaint log]", "[RV no]=" & Me.txtBox)

If text type field:

DLookup("[RV no]", "[Redressing complaint log]", "[RV no]='" & Me.txtBox & "'")


If DLookup doesn't find a match it will return Null.

[Gregory23]

This would be a Validation Rule for a table , no VBA

It is a Number type field

[June7]

DLookup expression just won't work in Validation Rule property.

Users should not work directly with tables so why bother with setting Validation Rule in table? I never set features like Validation Rule and Lookup in table. Put code behind form.

If you really must not use VBA, set the field as Index Yes No Duplicates. Let Access harass users about duplicate input.