Form code and message box help

[Rodney]

Could someone please help me with this code.

What I want is when the user is creating a roster if they select a trainer
who is on leave during that time the user receives a message.

TrainerID and RosterDate are two controls on this form.


Private Sub RosterDate_AfterUpdate()

If Me.TrainerID = [tblTrainerLeave].[TrainerID] AND
Me.RosterDate >= [tblTrainerLeave].[LeaveStartDate] AND
<=[tblTrainerLeave].[LeaveEndDate] Then

Message Box "This trainer is on leave from
[tblTrainerLeave].[LeaveStartDate] to
[tblTrainerLeave].[LeaveEndDate] please select another trainer"

End If

End Sub


I know the above code is not right but have no idea how to wright it
correctly.

Is this possible to do or am I asking to much


Thank you in advance


Rodney

 

[Douglas J. Steele]

It's MsgBox, not Message Box.

 

[Steve]

Here's another idea to consider ---

I presume you are using a combobox to select a trainer. Why not make the
rowsource a query that only returns trainers who are not on leave. Then a
user can only select trainers who are not on leave. If you feel it is
necessary for the user to see which trainers are on leave, add an unbound
subform and make the recordsource a query which only returns trainers who
are on vacation.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Bob Quintal]

Comments on hte code are in-lined

Could someone please help me with this code.

What I want is when the user is creating a roster if they
select a trainer who is on leave during that time the user
receives a message.

TrainerID and RosterDate are two controls on this form.

Private Sub RosterDate_AfterUpdate()

If Me.TrainerID = [tblTrainerLeave].[TrainerID] AND
Me.RosterDate >= [tblTrainerLeave].[LeaveStartDate]
AND <=[tblTrainerLeave].[LeaveEndDate] Then

'in the line above, the AND <=..... is incomplete
' You need to reference the Me.Rosterdate again.
AND RosterDate <=[tblTrainerLeave].[LeaveEndDate] Then

Message Box "This trainer is on leave from
[tblTrainerLeave].[LeaveStartDate] to
[tblTrainerLeave].[LeaveEndDate] please select another
trainer"

' Message Box should be MsgBox

End If

End Sub


I know the above code is not right but have no idea how to
wright it
correctly.

Is this possible to do or am I asking to much


Thank you in advance


Rodney

 

[Rodney]

Thank you Bob and Douglas your help is much appreciated.

Rodney

Comments on hte code are in-lined

Could someone please help me with this code.

What I want is when the user is creating a roster if they
select a trainer who is on leave during that time the user
receives a message.

TrainerID and RosterDate are two controls on this form.

Private Sub RosterDate_AfterUpdate()

If Me.TrainerID = [tblTrainerLeave].[TrainerID] AND
Me.RosterDate >= [tblTrainerLeave].[LeaveStartDate]
AND <=[tblTrainerLeave].[LeaveEndDate] Then

'in the line above, the AND <=..... is incomplete
' You need to reference the Me.Rosterdate again.
AND RosterDate <=[tblTrainerLeave].[LeaveEndDate] Then

Message Box "This trainer is on leave from
[tblTrainerLeave].[LeaveStartDate] to
[tblTrainerLeave].[LeaveEndDate] please select another
trainer"

' Message Box should be MsgBox

End If

End Sub


I know the above code is not right but have no idea how to
wright it
correctly.

Is this possible to do or am I asking to much


Thank you in advance


Rodney