Cancel if no employee id matches the list.

  • Thread starter Thread starter James
  • Start date Start date
J

James

The code below works and then goes to the employeeID field
when they enter the wrong emploeyy id. however if they
don't know a correct employeeid then they will keep
getting the error message unless they enter 'Esc'. Then
if they fill out any of the other fields the record will
update. I only want the record to update (or add) if the
employeeid is one from the table 'employee'. How do I
have the employeeid checked when the ok button is
pressed. Or what is the best way to do this? Thanks in
advance.

Private Sub EmployeeID_BeforeUpdate(cancel as integer)
If DCount("EmployeeID", "Employee", "EmployeeID =" & __
Me.EmployeeID) Then

Else
MsgBox "Enter a valid employee ID!", , "Invalid ID"
cancel = true
End If

End Sub
 
looks like your code evaluates the result of the DCount
function as a boolean - True or False. Try writing an
equation instead, that the If statement will test as true
or false:

Private Sub EmployeeID_BeforeUpdate(Cancel as Integer)

If DCount("EmployeeID", "Employee", "EmployeeID =" & __
Me.EmployeeID)= 0 Then
MsgBox "Enter a valid employee ID!", , "Invalid ID"
Cancel = True
End If

End Sub

if the "DCount.... = 0" equation doesn't work the way you
want, try "DCount.... <> 1"

hth
 
the Me.NewRecord validation will not work, because a
record is "new" until it is saved to the table for the
first time (when you close the form, or go to another
record, or click Save Record from the menu bar).

seems like the easiest way to require a correct entry
would be to run the original code (without the
me.newrecord part) at the control level rather than the
form level.

if that doesn't give the desired result, you could try
changing the code to check the record's primary key field -
if it's null, then close, else....
that may work well for you, because the primary key field
can never be null when you save a new or edited record,
anyway.

i won't be coming back to this thread, so if it still
won't work and nobody else helps you fix it, you're
welcome to email me. :)

-----Original Message-----
Thank you for your help. I put in the =0 and that works. I also moved it
to the ok button so that it has to check the employee id when it closes.
However if it is a new record it will not close. It still looks for the
empvalidate to be greater than 0. ASo I added the If Me.NewRecord... code
and now it will not check for the employee id. It closes no matter what.
What am I doing wrong. The code is below:

Private Sub btnOkay_Click()
On Error GoTo Err_btnOkay_Click
Dim empvalidate As String

empvalidate = DCount
Click to expand...
("EmployeeID", "Employee", "EmployeeID =" &
 
Back
Top