Prompt for duplicate entries

[Ernie Sersen]

I received the following reply to a post and have been advised that I should
repeat my question in the Forms Coding group for specifics. Can someone
please expand upon Steve's answer to my questions? My table is PARTS and my
field is SERIALNUMBER.

Yes. In the AfterUpdate, query the table for an existing SSN. If True,
then MsgBox, else don't.

Inquire within the FormsCoding newsgroup for specific help on the coding of
forms.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Al Camp]

Ernie,
Use the BeforeUpdate event of the SerNo field. (use your own object names)
If Not IsNull(Dlookup("[SerialNumber]","tblYourTable","SerialNumber =
Forms!frmYourForm!SerialNumber")) Then
'message box here... Duplicate SerNo
Cancel = True
SerialNumber.Undo
End If

 

[Keith Wilby]

I received the following reply to a post and have been advised that I
should
repeat my question in the Forms Coding group for specifics. Can someone
please expand upon Steve's answer to my questions? My table is PARTS and
my
field is SERIALNUMBER.

Can't you just set the field properties such that duplicates aren't allowed?
No coding required.

Keith.
www.keithwilby.com

 

[Ernie Sersen]

Thanks, Al. I tried your suggestion and get two errors:

Private Sub Text58_BeforeUpdate(Cancel As Integer)
If Not IsNull(Dlookup("[SerialNumber]","TAinvxMAIN","SerialNumber =
Forms!FMinvxMAIN!SerialNumber")) Then
MsgBox = "Duplicate SerNo"
Cancel = True
SerialNumber.Undo
End If
End Sub

Error 1: Expecting = (when creating the code)
Error 2: Syntax error (when trying to run the code)

Can you see what might be causing the error?

Ernie,
Use the BeforeUpdate event of the SerNo field. (use your own object names)
If Not IsNull(Dlookup("[SerialNumber]","tblYourTable","SerialNumber =
Forms!frmYourForm!SerialNumber")) Then
'message box here... Duplicate SerNo
Cancel = True
SerialNumber.Undo
End If
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I received the following reply to a post and have been advised that I should
repeat my question in the Forms Coding group for specifics. Can someone
please expand upon Steve's answer to my questions? My table is PARTS and my
field is SERIALNUMBER.

Yes. In the AfterUpdate, query the table for an existing SSN. If True,
then MsgBox, else don't.

Inquire within the FormsCoding newsgroup for specific help on the coding of
forms.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Al Camp]

Ernie,
What line generates the Error 1: Expecting = (when creating the code)?

The Dlookup must be all on one line...

The MsgBox syntax is wrong...
MsgBox "Duplicate SerNo"
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Thanks, Al. I tried your suggestion and get two errors:

Private Sub Text58_BeforeUpdate(Cancel As Integer)
If Not IsNull(Dlookup("[SerialNumber]","TAinvxMAIN","SerialNumber =
Forms!FMinvxMAIN!SerialNumber")) Then
MsgBox = "Duplicate SerNo"
Cancel = True
SerialNumber.Undo
End If
End Sub

Error 1: Expecting = (when creating the code)
Error 2: Syntax error (when trying to run the code)

Can you see what might be causing the error?

Ernie,
Use the BeforeUpdate event of the SerNo field. (use your own object names)
If Not IsNull(Dlookup("[SerialNumber]","tblYourTable","SerialNumber =
Forms!frmYourForm!SerialNumber")) Then
'message box here... Duplicate SerNo
Cancel = True
SerialNumber.Undo
End If
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I received the following reply to a post and have been advised that I should
repeat my question in the Forms Coding group for specifics. Can someone
please expand upon Steve's answer to my questions? My table is PARTS and my
field is SERIALNUMBER.

Yes. In the AfterUpdate, query the table for an existing SSN. If True,
then MsgBox, else don't.

Inquire within the FormsCoding newsgroup for specific help on the coding of
forms.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I am using Access Data Project with SQL backend and have a table of parts
with serial numbers. In the parts entry form, if someone enters a part
with
a serial number that already exists in the table, can the user be prompted
'Serial Number already exists'?

Thanks.