Form code and message box help continued

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

Guest

continued from a post on Sunday 8/12/2007

Could someone please tell me if I am close with this code or am I completly
off track.

What I want to achieve is: If the user is entering trainers onto a roster
and they select a trainer who will be on leave on that date, I want them to
receive a message telling them the trainer is not available.

The form has these controls

TrainerId
TrainerName
RosterdDate
StartTime
EndTime

I also have a table for leave which is tblTrainerLeave and has the following
controls

TrainerID
LeaveStartDate
LeaveEndDate


Private Sub TrainerID_BeforeUpdate(Cancel As Integer)

If Me.TrainerID = [tblTrainerLeave].[TrainerID] Then
If Me.RosterDate >= [tblTrainerLeave].[LeaveStartDate] Then
If Me.RosterDate <= [tblTrainerLeave].[LeaveEndDate] Then
MsgBox "This trainer is on leave from
[tblTrainerLeave].[LeaveStartDate] to [tblTrainerLeave].[LeaveEndDate] please
select another trainer."
End If
End If
End If
End Sub

I keep receiving this error

Run-time error '2465':

can't find the field 'l' referred to in your expression.

I have know idea what field it's talking, I can't find it at all.


Thank you in advance


Rodney
 
Rodney,

I cannot tell what is going on either. But I have two suggestions, but I
recommend option #1?

1. If your RosterDate field is filled in before the TrainerID, why not use
a combo box for the trainers that automatically filters out the trainers that
won't be available on that date. Then, on the afterupdate event of the
RosterDate field, all you have to do is requery the cboTrainer combo box.
The query would look something like:

SELECT TrainerID, TrainerName
FROM tbl_Trainers
WHERE TrainerID NOT IN (SELECT TrainerID from tblTrainerLeave
WHERE Form!yourForm.txtRosterDate
BETWEEN
[LeaveStartDate] and [LeaveEndDate])

2. The other option would be using what you have, and changing the code to
something like:

Private Sub TrainerID_BeforeUpdate(Cancel As Integer)

Dim strCriteria as string

strCriteria = "[TrainerID] = " & me.txtTrainerID & " AND " _
& "#" & me.txtRosterDate & "# BETWEEN [LeaveStartDate] " _
& " AND [LeaveEndDate]"
Cancel = (DCOUNT("TrainerID", "tblTrainerLeave", strCriteria) > 0)

If Cancel then msgbox "This trainer is on leave on this date"
End Sub
 
Thanks dale,
I will try both of these and see how they go. I will get back to you.

Thank you

Rodney

Dale Fye said:
Rodney,

I cannot tell what is going on either. But I have two suggestions, but I
recommend option #1?

1. If your RosterDate field is filled in before the TrainerID, why not use
a combo box for the trainers that automatically filters out the trainers that
won't be available on that date. Then, on the afterupdate event of the
RosterDate field, all you have to do is requery the cboTrainer combo box.
The query would look something like:

SELECT TrainerID, TrainerName
FROM tbl_Trainers
WHERE TrainerID NOT IN (SELECT TrainerID from tblTrainerLeave
WHERE Form!yourForm.txtRosterDate
BETWEEN
[LeaveStartDate] and [LeaveEndDate])

2. The other option would be using what you have, and changing the code to
something like:

Private Sub TrainerID_BeforeUpdate(Cancel As Integer)

Dim strCriteria as string

strCriteria = "[TrainerID] = " & me.txtTrainerID & " AND " _
& "#" & me.txtRosterDate & "# BETWEEN [LeaveStartDate] " _
& " AND [LeaveEndDate]"
Cancel = (DCOUNT("TrainerID", "tblTrainerLeave", strCriteria) > 0)

If Cancel then msgbox "This trainer is on leave on this date"
End Sub


--
Email address is not valid.
Please reply to newsgroup only.


Rodney said:
continued from a post on Sunday 8/12/2007

Could someone please tell me if I am close with this code or am I completly
off track.

What I want to achieve is: If the user is entering trainers onto a roster
and they select a trainer who will be on leave on that date, I want them to
receive a message telling them the trainer is not available.

The form has these controls

TrainerId
TrainerName
RosterdDate
StartTime
EndTime

I also have a table for leave which is tblTrainerLeave and has the following
controls

TrainerID
LeaveStartDate
LeaveEndDate


Private Sub TrainerID_BeforeUpdate(Cancel As Integer)

If Me.TrainerID = [tblTrainerLeave].[TrainerID] Then
If Me.RosterDate >= [tblTrainerLeave].[LeaveStartDate] Then
If Me.RosterDate <= [tblTrainerLeave].[LeaveEndDate] Then
MsgBox "This trainer is on leave from
[tblTrainerLeave].[LeaveStartDate] to [tblTrainerLeave].[LeaveEndDate] please
select another trainer."
End If
End If
End If
End Sub

I keep receiving this error

Run-time error '2465':

can't find the field 'l' referred to in your expression.

I have know idea what field it's talking, I can't find it at all.


Thank you in advance


Rodney
Click to expand...
Click to expand...
 
Dale,
I have tryed the options you heve suggested below and,

Option 1: works but we want the user to receive a message to remind them
that the trainer is no leave and the period they are on leave for.

Option 2: For some reason doesn't work, I select a trainer from the
TrainerID combo box and it doesn't populate the TrainerID control, With no
error messages or anything.

And my apologies on my original post I didn't mention that the table I
refered to in the code I was using are not controls on the form I want to
refere back to that table. The leave table details are:

Table name: tblTrainerLeave

Field names:
TrainerID
LeaveStartDate
LeaveEndDate

Does this make any differecne.

Thanks for your help it is appreceated

Rodney
Rodney said:
Thanks dale,
I will try both of these and see how they go. I will get back to you.

Thank you

Rodney

Dale Fye said:
Rodney,

I cannot tell what is going on either. But I have two suggestions, but I
recommend option #1?

1. If your RosterDate field is filled in before the TrainerID, why not use
a combo box for the trainers that automatically filters out the trainers that
won't be available on that date. Then, on the afterupdate event of the
RosterDate field, all you have to do is requery the cboTrainer combo box.
The query would look something like:

SELECT TrainerID, TrainerName
FROM tbl_Trainers
WHERE TrainerID NOT IN (SELECT TrainerID from tblTrainerLeave
WHERE Form!yourForm.txtRosterDate
BETWEEN
[LeaveStartDate] and [LeaveEndDate])

2. The other option would be using what you have, and changing the code to
something like:

Private Sub TrainerID_BeforeUpdate(Cancel As Integer)

Dim strCriteria as string

strCriteria = "[TrainerID] = " & me.txtTrainerID & " AND " _
& "#" & me.txtRosterDate & "# BETWEEN [LeaveStartDate] " _
& " AND [LeaveEndDate]"
Cancel = (DCOUNT("TrainerID", "tblTrainerLeave", strCriteria) > 0)

If Cancel then msgbox "This trainer is on leave on this date"
End Sub


--
Email address is not valid.
Please reply to newsgroup only.


Rodney said:
continued from a post on Sunday 8/12/2007

Could someone please tell me if I am close with this code or am I completly
off track.

What I want to achieve is: If the user is entering trainers onto a roster
and they select a trainer who will be on leave on that date, I want them to
receive a message telling them the trainer is not available.

The form has these controls

TrainerId
TrainerName
RosterdDate
StartTime
EndTime

I also have a table for leave which is tblTrainerLeave and has the following
controls

TrainerID
LeaveStartDate
LeaveEndDate


Private Sub TrainerID_BeforeUpdate(Cancel As Integer)

If Me.TrainerID = [tblTrainerLeave].[TrainerID] Then
If Me.RosterDate >= [tblTrainerLeave].[LeaveStartDate] Then
If Me.RosterDate <= [tblTrainerLeave].[LeaveEndDate] Then
MsgBox "This trainer is on leave from
[tblTrainerLeave].[LeaveStartDate] to [tblTrainerLeave].[LeaveEndDate] please
select another trainer."
End If
End If
End If
End Sub

I keep receiving this error

Run-time error '2465':

can't find the field 'l' referred to in your expression.

I have know idea what field it's talking, I can't find it at all.


Thank you in advance


Rodney
Click to expand...
Click to expand...
Click to expand...
 
Back
Top