Coding for "Duplicate error on update" needed.

D

DMarieD

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
 
D

DMarieD

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

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?
Click to expand...

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]
 
D

DMarieD

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
 
D

DMarieD

DMarieD said:
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.
Click to expand...

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
 
J

John W. Vinson

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

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]
 
J

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?
Click to expand...

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]
 
Top