Alert massage if there are an emploee order in the last 7 days

[Falcon88]

Hiii all

i have a MainFrm based on OrdersTbl that contains :
-OrderID
-OrderDate
-EmploeeID

every emploee may have more than one order , in the mainFrm (EmploeeID Before Update event), if there are previous Order in the last seven days for this emploee, an alert massage like: "There are an Order for this emploee before (Number of days) days are you sure to add new order ? "
if yes continue
else
cancel and undo.

 

[arnelgp]

use:
If Nz(DCount("*","OrdersTbl","EmployeeID = " & Me.EmployeeID & " And OrderDate Between Date()-7 And Date()") , 0) <> 0 Then
If Msgbox("There are an Order for this employee before " & _
DateDiff("d", DLookup("OrderDate", "OrderTbl", "EmployeeID = " & Me.EmployeeID & " And OrderDate Between Date()-7 And Date()"), Date()) & " days, are you sure to add new order?", vbYesNo+vbInformation) = vbNo Then
Me.Undo
End If

End If

 

[Falcon88]

use:
If Nz(DCount("*","OrdersTbl","EmployeeID = " & Me.EmployeeID & " And OrderDate Between Date()-7 And Date()") , 0) <> 0 Then
If Msgbox("There are an Order for this employee before " & _
DateDiff("d", DLookup("OrderDate", "OrderTbl", "EmployeeID = " & Me.EmployeeID & " And OrderDate Between Date()-7 And Date()"), Date()) & " days, are you sure to add new order?", vbYesNo+vbInformation) = vbNo Then
Me.Undo
End If

End If

very thanks mr arnelgp . but i don't want to use Date() (Today date) to compare , i want to use comparision between last orderdate for last Order for this emploee and the OrderDate for the current Order For this Emploee.
Example :

OrderID= 20120
and
OrderDate= 15/ April / 2016
and
emploeeID = XFGH

if the user trying to add :
an New Order
OrderID : 23425
in
20/April / 2016
to
the Emploee : XFGH

an alert massage :
"There are an Order for this emploee before (5) days are you sure to add new order ? "

 

[arnelgp]

If Nz(DCount("*","OrdersTbl","EmployeeID = " & Me.EmployeeID & " And OrderDate Between #" & Format(Me.OrderDate-7, "mm/dd/yyyy") & "# And #" & Format(Me.OrderDate, "mm/dd/yyyy") & "#") , 0) <> 0 Then
If Msgbox("There are an Order for this employee before " & _
DateDiff("d", DMax("OrderDate", "OrderTbl", "EmployeeID = " & Me.EmployeeID & " And OrderDate Between #" & Format(Me.OrderDate-7, "mm/dd/yyyy") & "# And #" & Format(Me.OrderDate, "mm/dd/yyyy") & "#"), Date()) & " days, are you sure to add new order?", vbYesNo+vbInformation) = vbNo Then
Me.Undo
End If

End If

 

[Falcon88]

if user try to enter an order with orderdate befor the last orderdate for an emploee.
please see to this attached file and see for that code :

 

[Falcon88]

Sir :arnelgp the code that you write gives me sometimes minus days results , and sometimes msg appears where there are more than 7 days before ( last Employee OrderDate ) . in the attached file (in the previous post) , try to insert any employee in 01/May/2016 (as the first order For this emploee ) and insert new order for the same employee in 10 / May /2016 , By default an alert msg must don't appears. please help me to make a query that gives me comparison between : the current OrderDate and the last orderDate for this Employee , then if there are more than 7 days between this two dates gives me this an alert msg in the main form (EmploeeID Before Update event) .

 

[arnelgp]

try again plz.

 

[Falcon88]

Ok Sir arnelgp . What is the benefit of CDate () function ? can we dispense CDate() function ?

 

[Falcon88]

i want if (the diffrence between the current [Orderdate] and the Max. [Orderdate] (the Nearest [OrderDate] before the current [Orderdate] ) for this employee through the last 7 days) less than than (the diffrence between the current [Orderdate] and the Min. [Orderdate] (the Nearest [OrderDate] After the current [Orderdate] ) for this employee through the Next 7 days ) MsgBox("There are an Order for this employee Before…….. " and if (the diffrence between the current [Orderdate] and the Max. [Orderdate] (the Nearest [OrderDate] before the current [Orderdate] ) for this employee through the last 7 days) More than than (the diffrence between the current [Orderdate] and the Min. [Orderdate] (the Nearest [OrderDate] After the current [Orderdate] ) for this employee through the Next 7 days) MsgBox("There are an Order for this employee After………" Exactly i want to know the nearest [Orderdate] either before or after to the current [OrderDate] for this employee.

 

[Falcon88]

i want if (the diffrence between the current [Orderdate] and the Max. [Orderdate] (the Nearest [OrderDate] before the current [Orderdate] ) for this employee through the last 7 days) less than (the diffrence between the current [Orderdate] and the Min. [Orderdate] (the Nearest [OrderDate] After the current [Orderdate] ) for this employee through the Next 7 days ) MsgBox("There are an Order for this employee Before…….. " if (the diffrence between the current [Orderdate] and the Max. [Orderdate] (the Nearest [OrderDate] before the current [Orderdate] ) for this employee through the last 7 days) More than (the diffrence between the current [Orderdate] and the Min. [Orderdate] (the Nearest [OrderDate] After the current [Orderdate] ) for this employee through the Next 7 days) MsgBox("There are an Order for this employee After………" Exactly i want to know the nearest [Orderdate] either before or after to the current [OrderDate] for this employee.