New to DCount, Help Please

Hi all,

I've set up a database for a nonprofit food distribution center. The
manager wants to limit the number of times a client can use the center to
once a month.

I have a mainform based on tblClients that has a continuous subform based on
tblAssistance in a Tab control. I want to compare the date of the last
visit to todays date to see if 30 days or more has passed. A friend of
mine wrote the code below but it doesn't work. Can someone figure this
out?

TIA

Jake

Private Sub cboAssistanceType_AfterUpdate()

Dim dteCheckdate As Date
Dim dteThisDate As Date
Dim iCount As Integer

'Capture date from the form
dteThisDate = Me.txtDateServed
'Find the date 30 days earlier than the date on the form
dteCheckdate = dteThisDate - 30
'If one or more records are found less than 30 days ago get a count
of them
iCount = DCount("DateServed", "tblAssistance", "ClientID = '" &
Forms!frmclients!ClientID & "' AND DateServed BETWEEN #" & dteCheckdate & "#
AND #" & dteThisDate & "#")
'If the count is zero then no aid was given within 30 days of this
date
If iCount 0 Then
'Flash warning
MsgBox "This Person Received Aid" & vbLf & "Less Than 30 Days
ago!!", vbCritical, "Not Allowed!"
'Undo the form (erase entry)
Me.Undo
End If
End Sub

What "doesn't work"? The Undo? If that is it, you need to move this code to
the BeforeUpdate event of the cboAssistanceType control, and then insert
this line of code just before the Me.Undo step:

Cancel = True


--
Ken Snell
MS ACCESS MVP

"GitarJake" wrote in message
news:xHWlc.40462$NZ4.22730@lakeread05...
Hi all,

I've set up a database for a nonprofit food distribution center. The
manager wants to limit the number of times a client can use the center to
once a month.

I have a mainform based on tblClients that has a continuous subform based
on
tblAssistance in a Tab control. I want to compare the date of the last
visit to todays date to see if 30 days or more has passed. A friend of
mine wrote the code below but it doesn't work. Can someone figure this
out?

TIA

Jake

Private Sub cboAssistanceType_AfterUpdate()

Dim dteCheckdate As Date
Dim dteThisDate As Date
Dim iCount As Integer

'Capture date from the form
dteThisDate = Me.txtDateServed
'Find the date 30 days earlier than the date on the form
dteCheckdate = dteThisDate - 30
'If one or more records are found less than 30 days ago get a
count
of them
iCount = DCount("DateServed", "tblAssistance", "ClientID = '" &
Forms!frmclients!ClientID & "' AND DateServed BETWEEN #" & dteCheckdate &
"#
AND #" & dteThisDate & "#")
'If the count is zero then no aid was given within 30 days of this
date
If iCount 0 Then
'Flash warning
MsgBox "This Person Received Aid" & vbLf & "Less Than 30 Days
ago!!", vbCritical, "Not Allowed!"
'Undo the form (erase entry)
Me.Undo
End If
End Sub

Hi Ken

In the After Update event, this code produces a Data Type Mismatch error on
the following line:

iCount = DCount("DateServed", "tblAssistance", "ClientID = '" &
Forms!frmclients!ClientID & "' AND DateServed BETWEEN #" & dteCheckdate & "#
AND #" & dteThisDate & "#")

In the Before Update event nothing happens.

Jake

"Ken Snell" wrote in message
...
What "doesn't work"? The Undo? If that is it, you need to move this code
to
the BeforeUpdate event of the cboAssistanceType control, and then insert
this line of code just before the Me.Undo step:

Cancel = True


--
Ken Snell
MS ACCESS MVP

"GitarJake" wrote in message
news:xHWlc.40462$NZ4.22730@lakeread05...
Hi all,

I've set up a database for a nonprofit food distribution center. The
manager wants to limit the number of times a client can use the center
to
once a month.

I have a mainform based on tblClients that has a continuous subform
based
on
tblAssistance in a Tab control. I want to compare the date of the last
visit to todays date to see if 30 days or more has passed. A friend of
mine wrote the code below but it doesn't work. Can someone figure this
out?

TIA

Jake

Private Sub cboAssistanceType_AfterUpdate()

Dim dteCheckdate As Date
Dim dteThisDate As Date
Dim iCount As Integer

'Capture date from the form
dteThisDate = Me.txtDateServed
'Find the date 30 days earlier than the date on the form
dteCheckdate = dteThisDate - 30
'If one or more records are found less than 30 days ago get a
count
of them
iCount = DCount("DateServed", "tblAssistance", "ClientID = '" &
Forms!frmclients!ClientID & "' AND DateServed BETWEEN #" & dteCheckdate
&
"#
AND #" & dteThisDate & "#")
'If the count is zero then no aid was given within 30 days of
this
date
If iCount 0 Then
'Flash warning
MsgBox "This Person Received Aid" & vbLf & "Less Than 30
Days
ago!!", vbCritical, "Not Allowed!"
'Undo the form (erase entry)
Me.Undo
End If
End Sub

I assume that ClientID is a numeric formatted field. As such, you don't use
the ' delimiters for the value that is being used for that field. Try this:

iCount = DCount("DateServed", "tblAssistance", "ClientID = " &
Forms!frmclients!ClientID & " AND DateServed BETWEEN #" & dteCheckdate & "#
AND #" & dteThisDate & "#")



--
Ken Snell
MS ACCESS MVP

"GitarJake" wrote in message
news:hEXlc.40599$NZ4.37767@lakeread05...
Hi Ken

In the After Update event, this code produces a Data Type Mismatch error
on
the following line:

iCount = DCount("DateServed", "tblAssistance", "ClientID = '" &
Forms!frmclients!ClientID & "' AND DateServed BETWEEN #" & dteCheckdate &
"#
AND #" & dteThisDate & "#")

In the Before Update event nothing happens.

Jake

"Ken Snell" wrote in message
...
What "doesn't work"? The Undo? If that is it, you need to move this code
to
the BeforeUpdate event of the cboAssistanceType control, and then insert
this line of code just before the Me.Undo step:

Cancel = True


--
Ken Snell
MS ACCESS MVP

"GitarJake" wrote in message
news:xHWlc.40462$NZ4.22730@lakeread05...
Hi all,

I've set up a database for a nonprofit food distribution center. The
manager wants to limit the number of times a client can use the center
to
once a month.

I have a mainform based on tblClients that has a continuous subform
based
on
tblAssistance in a Tab control. I want to compare the date of the
last
visit to todays date to see if 30 days or more has passed. A friend
of
mine wrote the code below but it doesn't work. Can someone figure
this
out?

TIA

Jake

Private Sub cboAssistanceType_AfterUpdate()

Dim dteCheckdate As Date
Dim dteThisDate As Date
Dim iCount As Integer

'Capture date from the form
dteThisDate = Me.txtDateServed
'Find the date 30 days earlier than the date on the form
dteCheckdate = dteThisDate - 30
'If one or more records are found less than 30 days ago get a
count
of them
iCount = DCount("DateServed", "tblAssistance", "ClientID = '"
&
Forms!frmclients!ClientID & "' AND DateServed BETWEEN #" &
dteCheckdate
&
"#
AND #" & dteThisDate & "#")
'If the count is zero then no aid was given within 30 days of
this
date
If iCount 0 Then
'Flash warning
MsgBox "This Person Received Aid" & vbLf & "Less Than 30
Days
ago!!", vbCritical, "Not Allowed!"
'Undo the form (erase entry)
Me.Undo
End If
End Sub

Thank You Ken,

Man! 2 little single quotes!

That was the fix!

Jake


"Ken Snell" wrote in message
...
I assume that ClientID is a numeric formatted field. As such, you don't
use
the ' delimiters for the value that is being used for that field. Try
this:

iCount = DCount("DateServed", "tblAssistance", "ClientID = " &
Forms!frmclients!ClientID & " AND DateServed BETWEEN #" & dteCheckdate &
"#
AND #" & dteThisDate & "#")



--
Ken Snell
MS ACCESS MVP

"GitarJake" wrote in message
news:hEXlc.40599$NZ4.37767@lakeread05...
Hi Ken

In the After Update event, this code produces a Data Type Mismatch error
on
the following line:

iCount = DCount("DateServed", "tblAssistance", "ClientID = '" &
Forms!frmclients!ClientID & "' AND DateServed BETWEEN #" & dteCheckdate
&
"#
AND #" & dteThisDate & "#")

In the Before Update event nothing happens.

Jake

"Ken Snell" wrote in message
...
What "doesn't work"? The Undo? If that is it, you need to move this
code
to
the BeforeUpdate event of the cboAssistanceType control, and then
insert
this line of code just before the Me.Undo step:

Cancel = True


--
Ken Snell
MS ACCESS MVP

"GitarJake" wrote in message
news:xHWlc.40462$NZ4.22730@lakeread05...
Hi all,

I've set up a database for a nonprofit food distribution center.
The
manager wants to limit the number of times a client can use the
center
to
once a month.

I have a mainform based on tblClients that has a continuous subform
based
on
tblAssistance in a Tab control. I want to compare the date of the
last
visit to todays date to see if 30 days or more has passed. A
friend
of
mine wrote the code below but it doesn't work. Can someone figure
this
out?

TIA

Jake

Private Sub cboAssistanceType_AfterUpdate()

Dim dteCheckdate As Date
Dim dteThisDate As Date
Dim iCount As Integer

'Capture date from the form
dteThisDate = Me.txtDateServed
'Find the date 30 days earlier than the date on the form
dteCheckdate = dteThisDate - 30
'If one or more records are found less than 30 days ago get
a
count
of them
iCount = DCount("DateServed", "tblAssistance", "ClientID =
'"
&
Forms!frmclients!ClientID & "' AND DateServed BETWEEN #" &
dteCheckdate
&
"#
AND #" & dteThisDate & "#")
'If the count is zero then no aid was given within 30 days
of
this
date
If iCount 0 Then
'Flash warning
MsgBox "This Person Received Aid" & vbLf & "Less Than 30
Days
ago!!", vbCritical, "Not Allowed!"
'Undo the form (erase entry)
Me.Undo
End If
End Sub