Reall Tough One To Figure Out If Statement

[Dave]

On my form named TimeCards there is a sub-form named Time and Hours that
tracks employee hours and payroll plus the date the time was entered.
On the sub-form named Time and Hours there is a control named WorkDate
(Control Source Work Date) that tells me the date that the employee had time
on.
If there were more than (2) employees on the same date, i.e. Work Date, then
I need a message box to popup telling me so.
Employees from drop down list on sub-form have a EmployeeID if this helps.
Drop down shows employee name only.
MsgBox "More than 2 Employees have worked on this job"


Thanks,


Dave

 

[Steve Schapel]

Dave,

Try something like this on the After Update event of the Employees
combobox...

DoCmd.RunCommand acCmdSaveRecord
If DCount("*","YourTableName","[Work Date]=#" & Me.Work_Date & "#") >
2 Then
MsgBox "More than 2 Employees have worked on this job"
End If

This assumes the Work Date is entered on the subform before the
Employee. Otherwise, it may be more applicable to do this on the After
Update event of the subform itself.

 

[Dave]

Tried this, syntax error ???

If DCount("*", "Employee", "[Work Date]=#" & Me.Work Date & "#") > 2 Then

 

[Steve Schapel]

Me.Work_Date

 

[Dave Elliott]

OK, problem is this code works except that the sub-form named Time and
Hours has many records on it that you can scroll thru.
Example: Time and Hours has 10 records on different days, i.e. Date. On some
days it only has (2) employees on the same date which this sceneario is OK!
But if on the same Day, i.e. Date there are more than (2) employees then the
Message Box is needed.
The main form TimeCards is a record by itself but the sub-froms on it has
many records or can have.
I ONLY need the message box if there are more than (2) Employees on the same
day, i.e. Date. Control is Work Date
The below code shows the message box every time you goto the next record on
the sub-form or another main form TimeCards record.
THIS Message box needs to run via the main form on current event and only
once for each TimeCard, i.e. Main Record
CAN THIS BE DONE????



If DCount("*", "Employee", "[WorkDate]") > 2 Then
MsgBox "More than 2 Employees have worked on this job"
End If

 

[Steve Schapel]

Dave,

The code I suggested before was designed to be used on the subform, so
that you get the message box alert at the point of entering a new
record, if such new record will result in there being more than 2
employees on the same day. I feel sure this will work correctly. The
code you have quoted below does not make sense, in that the condition
argument of the DCount function does not result in an evaluation.

 

[Dave]

The sub-form named Time and Hours has many records on it that you can
scroll thru.
Example: Time and Hours has 10 records on different days, i.e. Date. On some
days it only has (2) employees on the same date which this sceneario is OK!
But if on the same Day, i.e. Date there are more than (2) employees then the
Message Box is needed.
The main form TimeCards is a record by itself but the sub-froms on it has
many records or can have.
I ONLY need the message box if there are more than (2) Employees on the same
day, i.e. Date. Control is Work Date
The below code shows the message box every time you goto the next record on
the sub-form or another main form TimeCards record.
THIS Message box needs to run via the main form on current event and only
once for each TimeCard, i.e. Main Record
CAN THIS BE DONE????

 

[Steve Schapel]

Dave

Assuming you are referring to the code I gave you, your statement "The
below code shows the message box every time you goto the next record on
the sub-form or another main form TimeCards record" is not correct. It
will *only* show the message box if you attempt to enter a new Employee
on a Work Date where there are already two entered. Did you try the
code I gave you? What event(s) did you assign this code to? Can you
please post back with the exact code you are using.