Check dublications immediatelly from table

[janfolmer]

Hi There,

I use a form to add data to a table. The first field in my form (PolicyNumber) must check the entered value in the form directly from the table. *(Now it notifies me when I completed all the other fields in the form and when I ready to add this as a new record in the table).

I tried this with After Update/Before Update/Enter/Dirty events:
If IsNull(DLookup("PolicyNumber", "tblPolicies", "PolicyNumber = " & Me.PolicyNumber)) Then
'...Not found process onwards
Else
'...Opps found a duplicate number.. do some other actions..
End If

tblPolicies is the name of the table, PolicyNumber is both the fieldname in the table and the controlname in the form.

Often Im prompted with Error 2001 'You cancelled the previous operation', any ideas how I can get this working??

Thanks

 

[KenHigg]

If you set the policy number as the primary key or simply set the field's 'Indexed' property to 'Yes -No duplicate values', this should prevent duplicate policy numbers...

 

[janfolmer]

Hi Ken,

I did that, I'm saying that I only get notified (in case of a duplication) when I completed my whole form with 9 fields. I want to get a message as soon as I entered the first field, the PolicyNumber field.

The table already does not accept dublications.

 

[KenHigg]

Maybe you should check in the text box's 'After Update' event...?

 

[janfolmer]

I emptied the whole database, and named it test. You can see in my userface what my struggle is.

You can have a look around,

..

 

[kstampo]

Hi!

I'm quite new at Access and I had a similar problem which looks like it has been solved according to the instructions at:

http://support.microsoft.com/?kbid=209479

Hope this helps!

 

[KeithG]

Here is an example

Is this what you want?

 

[janfolmer]

Hi Keith,

Great, that was the solution, thanks