New to DCount, Help Please

  • Thread starter Thread starter GitarJake
  • Start date Start date
G

GitarJake

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
 
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
 
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 & "#")
 
Back
Top