If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
New to DCount, Help Please
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 |
#3
|
|||
|
|||
New to DCount, Help Please
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 |
#4
|
|||
|
|||
New to DCount, Help Please
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 |
#5
|
|||
|
|||
New to DCount, Help Please
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 |
Thread Tools | |
Display Modes | |
|
|