Is this possible?

[Jim]

I need to have a message come up when a customer orders more then 3 times in
a 30 day period (resetting every 30 days) and to not let another order be
entered for that customer unless a name is typed into an over ride box or
something similar, so we can track who authorized it. I'm using a slightly
modified version of the Northwind database invoice form. Is this something
that can be done? I'm using Access 2003.

Thanks



Jim

 

[Douglas J. Steele]

In the form's BeforeUpdate event, put logic to count how many orders have
already been received in the past 30 days

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("*", "TableName" "OrderDate BETWEEN " & _
Format(DateAdd("d", -30, Date()), "\#mm\/dd\/yyyy\#") & _
" AND " & _
Format(Date(), "\#mm\/dd\/yyyy\#")) > 3 Then

If Len(Me!OverrideBox & "") = 0 Then
Cancel = True
MsgBox "Must get override"
End If

End If

End Sub

 

[Klatuu]

I think the OP will want to include the customer in the DCount
If DCount("*", "TableName" "[CUST_ID] = " & Me.txtCustID & _
" AND OrderDate BETWEEN " & _
Format(DateAdd("d", -30, Date()), "\#mm\/dd\/yyyy\#") & _
" AND " & _
Format(Date(), "\#mm\/dd\/yyyy\#")) > 3 Then

 

[Douglas J. Steele]

Possibly, although maybe they're a very small business, with only 1
customer. <g>

Good catch.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I think the OP will want to include the customer in the DCount
If DCount("*", "TableName" "[CUST_ID] = " & Me.txtCustID & _
" AND OrderDate BETWEEN " & _
Format(DateAdd("d", -30, Date()), "\#mm\/dd\/yyyy\#") & _
" AND " & _
Format(Date(), "\#mm\/dd\/yyyy\#")) > 3 Then
--
Dave Hargis, Microsoft Access MVP

In the form's BeforeUpdate event, put logic to count how many orders have
already been received in the past 30 days

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("*", "TableName" "OrderDate BETWEEN " & _
Format(DateAdd("d", -30, Date()), "\#mm\/dd\/yyyy\#") & _
" AND " & _
Format(Date(), "\#mm\/dd\/yyyy\#")) > 3 Then

If Len(Me!OverrideBox & "") = 0 Then
Cancel = True
MsgBox "Must get override"
End If

End If

End Sub

 

[Klatuu]

LOL
Maybe that's why my business folded up in 1992?
--
Dave Hargis, Microsoft Access MVP

Possibly, although maybe they're a very small business, with only 1
customer. <g>

Good catch.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I think the OP will want to include the customer in the DCount
If DCount("*", "TableName" "[CUST_ID] = " & Me.txtCustID & _
" AND OrderDate BETWEEN " & _
Format(DateAdd("d", -30, Date()), "\#mm\/dd\/yyyy\#") & _
" AND " & _
Format(Date(), "\#mm\/dd\/yyyy\#")) > 3 Then
--
Dave Hargis, Microsoft Access MVP

In the form's BeforeUpdate event, put logic to count how many orders have
already been received in the past 30 days

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("*", "TableName" "OrderDate BETWEEN " & _
Format(DateAdd("d", -30, Date()), "\#mm\/dd\/yyyy\#") & _
" AND " & _
Format(Date(), "\#mm\/dd\/yyyy\#")) > 3 Then

If Len(Me!OverrideBox & "") = 0 Then
Cancel = True
MsgBox "Must get override"
End If

End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I need to have a message come up when a customer orders more then 3
times
in a 30 day period (resetting every 30 days) and to not let another
order
be entered for that customer unless a name is typed into an over ride
box
or something similar, so we can track who authorized it. I'm using a
slightly modified version of the Northwind database invoice form. Is
this
something that can be done? I'm using Access 2003.

Thanks



Jim