Coding for "Duplicate error on update" needed.

[Guest]

Hi,

I have a form that has a few fields that cannot accept a duplicate value.
Can anyone tell me what coding or command I can put in the "After Update"
property for each field that will let me know the value exists already in the
connected table?

Current problem is we don't get the error message until the entire form is
completed and we don't know which field triggered the error message.

Thanks
DD

 

[Guest]

Look at this link on "Preventing Duplicates from being entered"

http://www.databasedev.co.uk/duplicates.html

 

[Guest]

Thank you - I'm going to try this. It seems like exactly what I need.

 

[Guest]

Quick question on this coding. I tried and I keep getting the following
error on the line that reads Dim rsc As DAO.Recordset "Compile Error: User
defined Type not defined".

What does this mean? What needs to be changed?

 

[John W. Vinson]

Quick question on this coding. I tried and I keep getting the following
error on the line that reads Dim rsc As DAO.Recordset "Compile Error: User
defined Type not defined".

What does this mean? What needs to be changed?

Open the VBA editor; select Tools... References. Scroll down to find Microsoft
DAO 3.6 Object Library (or the highest version if there are more than one).
Check it, close the window, and select Debug... Compile <project>.

John W. Vinson [MVP]

 

[Guest]

Thanks John - now I got a new error: Compile Error. Duplicate declaration in
current scope.

Can you please help with this one? Sorry - not really familiar with this
coding stuff, your help is greatly appreciated.
DD

 

[Guest]

Thanks John. Now I have another issue:Can you please help with this one? Sorry - not really familiar with this coding stuff, your help is greatly appreciated.

Debug is bringing me to this line: rsc.FindFirst stLinkCriteria

with this error: Run-time error '3070': The Microsoft Jet database engine
does not recognize 'SUPPLIER_ID' as a valid field name or expression.

I replaced srtStudentNumber in the sample code with SUPPLIER_ID (my field
name). However my field name doesn't have the underscore in it. Would this
make a difference?

Thanks,
DD

 

[Guest]

Disreguard everything else - I got it to work.......!!!!!!!! THANK YOU!

 

[John W. Vinson]

Thanks John - now I got a new error: Compile Error. Duplicate declaration in
current scope.

A line should be highlighted. I'm guessing that it's complaining about a
duplicate declaration because... ummm... because you have a duplicate
declaration. Something (Dim db As DAO.Database maybe??) is in the code twice.
See which line is highlighted, and then use the binoculars tool to find
another instance of declaring the same variable; get rid of one of them.

John W. Vinson [MVP]

 

[John W. Vinson]

I replaced srtStudentNumber in the sample code with SUPPLIER_ID (my field
name). However my field name doesn't have the underscore in it. Would this
make a difference?

Ummm... yes. Of course it would make a difference. Computers are very literal
minded; a human might recognize SUPPLIER ID and SUPPLIER_ID as being "close
enough" but to the computer, they're as different as Kalamazoo and Timbuktu.

Try [Supplier ID] in square brackets.

John W. Vinson [MVP]