advise if record don't exists

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there!
I have a continuous form that is used to enter data on the work done by
employees on a certain day. The user enters the EmployeeID, WorkDate,
HoursWorked,WorkID in text fields (This form is bounded to a table named Work)

When the user enters an employeeID, I want the program to check in the table
Employee to see if he exists. If he doesn't exist, a msgbox appears to advise
the user.
I have read many posts to help me design my code, but I can't make it work
properly. It works sometimes but I almost always get Syntax error (missing
operator) This happens when I go in the last line (for entering new data)

Here is my code:

Some parts are in french, sorry
Employee is also the name of the textbox in the form


Private Sub Employee_LostFocus()
Dim message, msg, title As String
msg = "Aucun n'employé n'a ce numéro. Veuillez le changer avant de changer
de ligne...(Sinon ça ne marchera pas et cette ligne ne s'affichera pas dans
la paie)"
title = "Erreur"

If DCount("EmployeeID", "Employee", "EmployeeID = " & Me.Employee) = 0
Then
message = MsgBox(msg, vbExclamation, title)

End If
End If
End Sub
----------------------------

I tried to put and if statement before the dcount like someone proposed in
another post but it didn't work. It just ignored the dcount. This is what i
tried:
if me.newrecord then
exit sub
else
my code ...
end if

I also tried somethig similar with Dlookup and didn't work either
Can someone please help me

Thanks a lot

Louis Pat
 
This part of your code looks okay, but notice the addition of the brackets.
I recommend always using brackets when adressing a recordset field. The only
other thing I see that could be an issue would be the data type of
[EmployeeID]. Your code is correct if it is numeric.

If DCount("[EmployeeID]", "Employee", "[EmployeeID] = " & Me.Employee) =
0
Then
message = MsgBox(msg, vbExclamation, title)
End If

If it is string or text data type then:

If DCount("[EmployeeID]", "Employee", "[EmployeeID] = '" & Me.Employee &
"'") = 0
Then
message = MsgBox(msg, vbExclamation, title)
End If
 
Louis,
There's a much easier solution...
Use a combo box to select an EmployeeID. You would populate it with ONLY
legitimate EmployeeIDs from the Employee table. That combo's Limit to List
= Yes would not allow an illegitimate EmployeeID to be entered at all.
It can act just as a text control with manual entry... but with the added
benefit of validity checking
"Pas de coding!" :-D
Better to prevent a bad entry that code to correct it.
hth
Al Camp
 
I agree as long as Auto Expand is set to True.
We certainly like the idea of minimizing the code we have to write, but the
true goal is to make the user's life as easy as possible.
Research has been done that shows entering data through the keyboard in a
heavy data entry environment is much faster than navigating controls with the
mouse. Therefore, I always recommend Auto Expand.
 
Research has been done that shows entering data through the keyboard in a
heavy data entry environment is much faster than navigating controls with
the
mouse.
Click to expand...
A combo box does not "require" a mouse unless you want to take advantage of
that capability.

Of course Auto Expand would be on. It's as much a part of the validity
checking as Limit to List.

Why code a Dlookup and a message box for an error that can be easily
prevented from occuring?

Perhaps you felt that I was referring to your response when I wrote...I didn't see your post when I responded, and that was directed at Louis and
his original "coding" solution.
Thanks,
Al Camp
 
No, Al. I was only pointing out that Auto Expand should be on so that
keyboard entry would be as easy as possible.
The various controls we have available do have their advantages and can
help a user who is not that familiar with an application. I have, however,
seen many instances where poor interface design hamper rather than help.
It is just my philosophy to design forms and workflow to make it as easy as
possible for users. IMHO, the Quicken data entry model is one to study and
imulate.
 
Back
Top