Form code and message box help

[Guest]

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

 

[StopThisAdvertising]

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)

--
This is to inform 'newbees' here about PCD' Steve:
http://home.tiscali.nl/arracom/whoissteve.html
Until now 3275+ pageloads, 2200+ first-time visitors (these figures are rapidly increasing)

To PCD' Steve: (this is also to inform those who do not understand...)
This is *not* about the sigline...(although you are far away from a 'resource' status).
But we have started to hunt down *each and every* of your posts.

Why???
Because you are the ONLY person here who continues to advertise in the groups.

It is not relevant whether you advertised in *this* particular post or not...
==> We want you to know that these groups are *not* your private hunting grounds!

For those who don't like too see all these messages:
==> Simply killfile 'StopThisAdvertising'.
Newbees will still see this warning-message.

ArnoR

 

[Guest]

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