Custom DLookup Error Message

[Ripper]

I am attempting to use this code to DLookup a field before it is updated.
However, I cannot seem to get my syntax correct around the "[StuID]='" &
Me.StuID &'" area. StuID is set as a number field in tblStudents.

If ((DLookup("[StuID]", "[tblStudents]", "[StuID] ='" & Me.StuID & '")))
Then
MsgBox "This Student ID number is already used. Please Check ID or look
in History From.", vbOKOnly, "Student ID Already Exists"
End If

 

[Ken Sheridan]

Omit the single quotes delimiter characters. Number data types don't need
them, only text. Test for Not IsNull to find if a match already exists:

Dim strCriteria As String

strCriteria = "StuID = " & Me.StuID

If Not IsNull(DLookup("StuID", "tblStudents", strCriteria)) Then
<and so on>

Be sure to index the column uniquely in the table design, and in the above
code cancel the update operation with:

Cancel = True

if the ID already exists.

Note that if the application is in a multi-user environment the above code
will fail if two users are adding a record simultaneously and enter the same
ID number. However, the index violation will cause a data error when the
user attempts to save the record. This can be handled in the form's Error
event procedure.

For ID numbers a text data type is generally better, in which case you would
need the quotes delimiters. ID numbers are not really numbers in the true
sense, but often some form of encoding system, and sometimes structured, as
are Zip codes and ISBN numbers for instance. Unless the 'number' has some
ordinal or cardinal significance, I'd suggest changing the data type to text.

Ken Sheridan
Stafford, England

 

[John Spencer]

If DCount("[StuID]", "[tblStudents]", "[StuID] =" & Me.StuID) <> 0 Then
...
End if

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Ripper]

Once I cancel, the number is still in the StuID box. Is there a way to clear
the field and reset it for them to try again?
--
Thanks As Always
Rip

Omit the single quotes delimiter characters. Number data types don't need
them, only text. Test for Not IsNull to find if a match already exists:

Dim strCriteria As String

strCriteria = "StuID = " & Me.StuID

If Not IsNull(DLookup("StuID", "tblStudents", strCriteria)) Then
<and so on>

Be sure to index the column uniquely in the table design, and in the above
code cancel the update operation with:

Cancel = True

if the ID already exists.

Note that if the application is in a multi-user environment the above code
will fail if two users are adding a record simultaneously and enter the same
ID number. However, the index violation will cause a data error when the
user attempts to save the record. This can be handled in the form's Error
event procedure.

For ID numbers a text data type is generally better, in which case you would
need the quotes delimiters. ID numbers are not really numbers in the true
sense, but often some form of encoding system, and sometimes structured, as
are Zip codes and ISBN numbers for instance. Unless the 'number' has some
ordinal or cardinal significance, I'd suggest changing the data type to text.

Ken Sheridan
Stafford, England

I am attempting to use this code to DLookup a field before it is updated.
However, I cannot seem to get my syntax correct around the "[StuID]='" &
Me.StuID &'" area. StuID is set as a number field in tblStudents.

If ((DLookup("[StuID]", "[tblStudents]", "[StuID] ='" & Me.StuID & '")))
Then
MsgBox "This Student ID number is already used. Please Check ID or look
in History From.", vbOKOnly, "Student ID Already Exists"
End If

 

[BruceM]

With Me.ControlName
.Undo
.SetFocus
End With
Cancel = True

Use the actual control name.

Once I cancel, the number is still in the StuID box. Is there a way to
clear
the field and reset it for them to try again?
--
Thanks As Always
Rip

Omit the single quotes delimiter characters. Number data types don't
need
them, only text. Test for Not IsNull to find if a match already exists:

Dim strCriteria As String

strCriteria = "StuID = " & Me.StuID

If Not IsNull(DLookup("StuID", "tblStudents", strCriteria)) Then
<and so on>

Be sure to index the column uniquely in the table design, and in the
above
code cancel the update operation with:

Cancel = True

if the ID already exists.

Note that if the application is in a multi-user environment the above
code
will fail if two users are adding a record simultaneously and enter the
same
ID number. However, the index violation will cause a data error when the
user attempts to save the record. This can be handled in the form's
Error
event procedure.

For ID numbers a text data type is generally better, in which case you
would
need the quotes delimiters. ID numbers are not really numbers in the
true
sense, but often some form of encoding system, and sometimes structured,
as
are Zip codes and ISBN numbers for instance. Unless the 'number' has
some
ordinal or cardinal significance, I'd suggest changing the data type to
text.

Ken Sheridan
Stafford, England

I am attempting to use this code to DLookup a field before it is
updated.
However, I cannot seem to get my syntax correct around the "[StuID]='"
&
Me.StuID &'" area. StuID is set as a number field in tblStudents.

If ((DLookup("[StuID]", "[tblStudents]", "[StuID] ='" & Me.StuID &
'")))
Then
MsgBox "This Student ID number is already used. Please Check ID or
look
in History From.", vbOKOnly, "Student ID Already Exists"
End If