By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 2,998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

Validation of a field using Access Forms

P: n/a
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?
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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?

Nov 12 '05 #2

P: n/a
"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
Nov 12 '05 #3

P: n/a
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

Nov 12 '05 #4

P: n/a
> 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
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.