Validation of a field using Access Forms

  • Thread starter Thread starter Bobbak
  • Start date Start date
B

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?
 
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 said:
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.
Click to expand...

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
 
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



Bobbak said:
"Wayne Morgan" <[email protected]> wrote in
message news: said:
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.
Click to expand...

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
Click to expand...
 
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
Click to expand...

You need the text delimters in there...
Private Sub TextPHONELOGIN_AfterUpdate()
Click to expand...
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
 
Back
Top