Validation of a field using Access Forms

[Bobbak]

Hello All,
I am having trouble with this forum I am developing, I have this form
that allows the users to add or modify employee information, and
there's a text box called txtEmployeeLogin, and this connected to the
field EmpLogin in the database table. Now this field is not a Key
field and it is not required, and not all employees have logins, but
we can't have employees with duplicate logins. How can I go about
coding a check to see if the login exsist before adding or making
change to the employee record?

[Wayne Morgan]

You could use the DLookup function to see if the value exists in the field. Another option
would be to index the field choosing No Duplicates. If you do this, then Access won't
allow a duplicate entry in the field.

--
Wayne Morgan
"Bobbak" <bo****@ottawa.com> wrote in message
news:64**************************@posting.google.c om...

Hello All,
I am having trouble with this forum I am developing, I have this form
that allows the users to add or modify employee information, and
there's a text box called txtEmployeeLogin, and this connected to the
field EmpLogin in the database table. Now this field is not a Key
field and it is not required, and not all employees have logins, but
we can't have employees with duplicate logins. How can I go about
coding a check to see if the login exsist before adding or making
change to the employee record?

[Bobbak]

"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<oE*****************@newssvr31.news.prodigy.c om>...

You could use the DLookup function to see if the value exists in the field. Another option
would be to index the field choosing No Duplicates. If you do this, then Access won't
allow a duplicate entry in the field.

--
Wayne Morgan

OK I tried the Dlookup code but I keep getting a Datatype mismatch in
criteria error. Here is my code:

Private Sub TextPHONELOGIN_AfterUpdate()
Dim varX As Variant
If IsNull(Me![TextPHONELOGIN]) = False Then
varX = DLookup("[PHONELOGIN]", "SMS", "[PHONELOGIN] = " &
Me![TextPHONELOGIN])
MsgBox "The Phone Login " & varX & " already exsist",
vbCritical + vbOKOnly
End If
End Sub

[Douglas J. Steele]

If PHONELOGIN is a text field, you need quotes around the value:

varX = DLookup("[PHONELOGIN]", "SMS", & _
"[PHONELOGIN] = '" & Me![TextPHONELOGIN] & "'")

(where that's ' " & Me![TextPHONELOGIN] & " ' "), or

varX = DLookup("[PHONELOGIN]", "SMS", & )
"[PHONELOGIN] = " & Chr$(34) & Me![TextPHONELOGIN] &
Chr$(34))

However, using DLookup like that is going to work: you're always going to be
popping up the message box, even if the PHONELOGIN doesn't exist.

Try:

Private Sub TextPHONELOGIN_AfterUpdate()
Dim varX As Variant
If IsNull(Me![TextPHONELOGIN]) = False Then
varX = DLookup("[PHONELOGIN]", "SMS", & _
"[PHONELOGIN] = '" & Me![TextPHONELOGIN] & "'")
If Not IsNull(varX) Then
MsgBox "The Phone Login " & varX & " already exists",
vbCritical + vbOKOnly
End If
End If
End Sub

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
"Bobbak" <bo****@ottawa.com> wrote in message
news:64**************************@posting.google.c om...

"Wayne Morgan" <co***************************@hotmail.com> wrote in

message news:<oE*****************@newssvr31.news.prodigy.c om>...

You could use the DLookup function to see if the value exists in the field. Another option would be to index the field choosing No Duplicates. If you do this, then Access won't allow a duplicate entry in the field.

--
Wayne Morgan

OK I tried the Dlookup code but I keep getting a Datatype mismatch in
criteria error. Here is my code:

Private Sub TextPHONELOGIN_AfterUpdate()
Dim varX As Variant
If IsNull(Me![TextPHONELOGIN]) = False Then
varX = DLookup("[PHONELOGIN]", "SMS", "[PHONELOGIN] = " &
Me![TextPHONELOGIN])
MsgBox "The Phone Login " & varX & " already exsist",
vbCritical + vbOKOnly
End If
End Sub

[Pieter Linden]

> OK I tried the Dlookup code but I keep getting a Datatype mismatch in

criteria error. Here is my code:

Private Sub TextPHONELOGIN_AfterUpdate()
Dim varX As Variant
If IsNull(Me![TextPHONELOGIN]) = False Then
varX = DLookup("[PHONELOGIN]", "SMS", "[PHONELOGIN] = " &
Me![TextPHONELOGIN])
MsgBox "The Phone Login " & varX & " already exsist",
vbCritical + vbOKOnly
End If
End Sub
You need the text delimters in there...
Private Sub TextPHONELOGIN_AfterUpdate()

Const cQUOTE As String ="'"
Dim varX As Variant
If IsNull(Me![TextPHONELOGIN]) = False Then
varX = DLookup("[PHONELOGIN]", "SMS", "[PHONELOGIN] = " & cQUOTE &
Me![TextPHONELOGIN] & cQUOTE)
MsgBox "The Phone Login " & varX & " already exsist",
vbCritical + vbOKOnly
End If
End Sub