Check Dates

[Laudrup]

I have a database that allocates people to jobs, which is based on the
Resource Scheduling database.

The form I use to allocated the names to the a job works by selecting
a name from a listbox then when I click on the command button it
copies the EmployeeID into the job.

Simple enough, the problem is I also have a link to another database's
table which stores the dates a person is on a course, and therefore un-
available for work.

My problem is I want the command button to search this other table to
see if the selected person is on a course that day and ask me if I
would still like to add the person to the job.

Relevant tables used in the form

Employees
Employ_Ref
Surname....

ScheduleDetails (Where jobs allocation is saved)
ScheduleDetailsID
ScheduleID
Employ_Ref

Schedule (job date is saved)
ScheduleID
ScheduleDate


Other database table that holds the courses;

Training
Train_Ref
Employ_Ref
StartDate
EndDate

So basically I need the code to check if the ScheduleDate falls
between the StartDate & EndDate then if the Employ_ref within those
dates.




Currently the command button uses the following;

Private Sub ASSIGNcmd_Click()
If Me.Tab1.Value = 0 Then
Me.CustomerIDtxt.Value = Me.List0.Column(0)
ElseIf Me.Tab1.Value = 1 Then
Me.CustomerIDtxt.Value = Me.List1.Column(0)
End If


Dim strMsg As String
strMsg = "Data has changed."
strMsg = strMsg & " Do you wish to save the changes?"
strMsg = strMsg & " Click Yes to Save or No to Discard changes."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?") = vbYes
Then
Else
DoCmd.RunCommand acCmdUndo
End If
End Sub


I have a very limited amount of knowledge of VBA and have really been
struggling with this for some time so any help would be greatly
appreciated.

 

[Ken Sheridan]

Something like this should do it:

Dim strCriteria As String
Dim strMessage As String
Dim varTraining As Variant

strMessage = "This employee is undertaking training " & _
"on the scheduled date." & vbNewLine & vbNewLine & _
"Do you wish to continue adding this employee to the job?"

strCriteria = "Employ_Ref = " & Me.Employ_Ref & _
" And StartDate <= #" & Format(Me.ScheduleDate,"mm/dd/yyyy") & _
"# And EndDate >= #" & Format(Me.ScheduleDate,"mm/dd/yyyy") & "#"

varTraining = DLookup("Train_Ref", "Training", strCriteria)

If Not IsNull(varTraining) Then
If MsgBox(strMessage, vbYesNo + vbQuestion, "Warning") = vbYes Then
'''<rest of your code to assign employee goes here>'''
End If
End If

The above assumes that Employ_Ref is a number data type. If its text then
warp the value in quotes:

strCriteria = "Employ_Ref = """ & Me.Employ_Ref & """" & _

Ken Sheridan
Stafford, England

 

[Laudrup]

Thank you so so much that work perfectly!

You have no idea how much more time and stress thats saved me from.

Thank you! Thank you! Thank you!