Everything starts within the before update of the service time field.
First we declare two variables tmeAddService and tmeMinusService.
These two variables are Date. Why? because time comes under the date
variable. If you notice when you create a date field in tables - the
data type always says date/time. Because they share the same variable.
Ok, well I want these two variables to equal servicetime minus 90
minutes and servicetime plus 90 minutes.
Thus we use the DateAdd function. A bit confusing because we're not
playing with dates here, but as I said time also shares the date
variable. So we are going to take 90 minutes away from service time.
Thus:
DateAdd (the function to add to a date/time value) "n" (what time
interval we want to use n = minutes, m = months) - "-90" (this is the
figure we are going to add so to subtract we need to detail a negative
figure) - "Me.ServiceTime" (The value that we are going to perform the
calculation on).
Thus tmeMinusService = DateAdd("n", -90, Me.ServiceTime) means
tmeMinusService = Me.Servicetime - 90 minutes.
Ok that’s the first part - now we need to ascertain whether to warn the
user or prevent the user from adding the order.
In anser to your question
in Dans words!!).
I created a new query called qryOrderChk. I usually put
chk on the end of names when I am using that object for validation.
The query is a basic select query. The criteria prevents the current
record from being displayed (<>[Forms]![frmOrders]![OrderId]) and also
only displays records that have the same service date as the current
record ([Forms]![frmOrders]![ConfirmedServiceDate]). Now back to the
code.
So first up I wanted to check whether any records match the current
record with the same service date and time. I do this with the following
line:
If DCount("OrderId", "qryOrderChk", "ServiceTime =
Forms!frmOrders!ServiceTime") > 0 Then
So all I am doing here is saying how many (DCount) records (OrderId)
within (qryOrderChk) which have a (serviceTime) equal to the current
record (Forms!frmOrders!ServiceTime). If that figure is more than 0 (>0)
then run the next piece of code:
DoCmd.OpenForm "frmOrderChk", acNormal, , "[ServiceTime] =
Forms!frmOrders!ServiceTime"
You know this bit - open the form frmOrderChk (which has a record source of qryOrderChk) and limit the records displayed to where the ServiceTime is the same as the service time of the current record on the form.
Forms!frmOrderChk!labDuplicate.Visible = True
This part makes visible the message telling the user they cannot make
the order.
Cancel = True prevents the record from being updated
Exit Sub quits the sub routine so no further code is executed.
This part is now done.
Second up we want to check that if there are no matching records then
check to see if there are any records within the 90 minute range.
Here I did exactly the same thing:
DCount("OrderId", "qryOrderChk", "ServiceTime >=#" & tmeMinusService &
"# And ServiceTime <= #" & tmeAddService & "#") > 0 Then
How many OrderIds are there in qryOrderChk which have a service time
equal to or greater than tmeMinusService (we have already made
tmeMinusService = the current servicetime - 90 minutes) but also has a
servicetime equal to or less than tmeAddService(90 minutes more than the current service time) If there is more than 0 then execute the following code:
DoCmd.OpenForm "frmOrderChk", acNormal, , "[ServiceTime] >= #" &
tmeMinusService & "# And [ServiceTime] <= #" & tmeAddService & "#"
Open the form frmOrderChk and only display the records that have a
service time of greater than or equal to tmeMinusService and that have a servicetime of less than or equal to tmeAddService.
Syntax: The syntax is a little more complicated on the second bit of
code.
Declared variables cannot be enclosed in " " marks thus you need to use
& to identify them and & again to unidentify them. You also need to
surround these variables with a # but these must be contained within the
" " marks.
