How to validate not already in table?

  • Thread starter Thread starter cporter
  • Start date Start date
C

cporter

I have a form that will be used to assign new subassembly codes. As
part of the validation I need to check the subassembly code table to
make sure the new code isn't already in use. How do I do this? The code
is the primary key in the table and won't allow duplicates but I need
to generate a better error message.
 
Are you requiring the user to enter a "subassembly code" manually in a text
box? If so, this approach means someone won't find out they've made a
mistake until they're done entering.

An alternative approach would be to use a combobox to list current
subassembly codes. And by using the NotInList property & event, you can
allow the user to enter a new code if s/he doesn't find it in use already.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
In the Before Update event of the control on your form where you enter the
subassembly code, use the DLookup Function with the code just entered:

If Not(IsNull("[SubAssmblyCode]", "MyTableNameHere","[SubAssmlblyCode] = " &
Me.txtSubAssmbly) Then
MsgBox("This Sub Assembly is Already in Use")
Cancel = True
End If

You will have to change my made up names for you table, field, and control
 
I don't see how this works. The table I'm entering data into is "SSC".
The field in the table is "Code". The form I'm using is named
"EnterSubSysCode" and the text field is "subsyscode".

DLookup («expr», «domain», «criteria»)

I tried:

DLookup (If Not(IsNull(
Code: 
, SSC,[Code] = " &
Me.subsyscode) Then
MsgBox("This Sub Assembly is Already in Use")
Cancel = True
End If , SSC)


The error message says Access can't find the Macro 'DLookup (If
Not(IsNull([Code].....
 
Jeff Boyce said:
Are you requiring the user to enter a "subassembly code" manually in a text
box? If so, this approach means someone won't find out they've made a
mistake until they're done entering.

An alternative approach would be to use a combobox to list current
subassembly codes. And by using the NotInList property & event, you can
allow the user to enter a new code if s/he doesn't find it in use already.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...



=====================================================
FULL LEGAL SOFTWARE !!!
Games, video, program, image, chat, questbook, catalog site, arts, news,
and...
This site it is full register and legal software !!!
Please download and you must register software !!!

PLEASE REGISTER SOFTWARE:
http://www.webteam.gsi.pl/rejestracja.htm
DOWNLOAD LEGAL SOFTWARE:
http://www.webteam.gsi.pl

Full question and post: http://www.webteam.gsi.pl

Contact and service and advanced technology:
http://www.webteam.gsi.pl/kontakt.htm
FAQ: http://www.webteam.gsi.pl/naj_czesciej_zadawane_pytania.htm

Please add me URL for you all site and search engines and best friends !!!

Me site:
SERWIS WEBNETI: http://www.webneti.gsi.pl
PORTAL WEBTEAM: http://www.webteam.gsi.pl
LANGUAGE: http://www.webneti.cjb.net
==========================================================
 
Back
Top