Duplicate Record Warnings

[Guest]

I have a field in my Access database that I do not want duplicated, so I set
it in the table properties not to allow duplicates. It works great for not
allowing duplicate records.

The problem is that the form people access this table through does not warn
them when they have duplicated the project number. It is a additions-only
form designed for the entry of a single record of entry, and then the user
presses 'done'. If the field in question is a duplicate, the form just
closes like normal, but doesn't insert the record. No warning, no nothing.
It's as if the OnError event isn't firing.

I have been using the docmd.setwarnings(false) command in VBA in certain
places, but I put docmd.setwarnings(true) directly above the exit command,
and still no warning. Why are no warning messages showing up?

Any ideas?

Dustin Ventin

 

[Michel Walsh]

HI,


You can have a

On Error Resume Next


and never check

If err.Number <> 0 then


or check at another level and disregard the error. That is just one
possibility. You can check the duplicated value in the AfterUpdate of the
Control, with something like:


If 0 <> DCoun("*", "mytable: , "myfield=""" & Me.ThisControl & """" )
Then

MsgBox " [" & Me.ThisControl & "] is already entered in the
table."

End If




Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


Note that technically, you should test, first, if the control value is
null.


Vanderghast, Access MVP

 

[Guest]

Hello,
I have tried the code from Michel and got an error message: Expected list
separator or ).
If 0 <> Dcount("*","tblFINSPECTcustspec:,ITEM=""& Me.ITEM&"""")
Any ideas what I have wrong?
Thanks,

 

[Michel Walsh]

Hi,


Should be


Dcount("*","tblFINSPECTcustspec", "ITEM=""" & Me.ITEM & """" )



be sure to add a space after Item and before &.

At then end of the table name, use " not : (an original typo in my
reply).



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Works----Thank you very much for the help! I am still learning alot about VB.
RAO