Restrict the number of records entered in form

[azhar2006]

Good evening everyone
I am trying to use this code to restrict the user to enter only 20 records per day. But I get an error when executing.

Private Sub Form_BeforeInsert(Cancel As Integer)
 Dim CountOfTodaysEntries As Integer
  CountOfTodaysEntries = DCount("ID", "tblMaid", "s_date = Date() AND ID = " & Me.ID)
  If CountOfTodaysEntries > 20 Then
    MsgBox "Max daily entries reached.  Try again tomorrow."
    Cancel = True
  End If
End Sub

 

[Minty]

And what is the error and what line is it occurring on?

 

[Gasman]

You have been here for over 12 years and cannot supply the error message?

 

[azhar2006]

And what is the error and what line is it occurring on?

 

[Gasman]

I would put the criteria into a string variable and debug.print it, the when correct use that in the dcount. Also try with just the date to start with. I'd probably concatenate that as well.

 

[Gasman]

There is your problem

 

[azhar2006]

There is your problem
View attachment 116449

Thank you very much. I found the error, the error is when I add a new record. Of course Access does not generate the ID number until after entering the first information in this record. So it tells me an error. Because I added the statement ( AND ID).

Dim CountOfTodaysEntries As Integer
  CountOfTodaysEntries = DCount("ID", "tblMaid", "s_date = Date()")
  If CountOfTodaysEntries >= 20 Then
    MsgBox "Max daily entries reached.  Try again tomorrow."
    Cancel = True
  End If

 

[azhar2006]

But there is another problem. It increases the number of records from 20 to 21. Why. Should I make the number of records minus one, meaning 20 is 19 in the code?
Can I add the number of allowed records to the message?

 

[The_Doc_Man]

That count of records includes "phantom" records that might have been created incorrectly. Because your DCount and that form probably have slightly different criteria, they can come up with different numbers of records. For instance, if the form doesn't have the reference to Me.ID but the DCount DOES, the form doesn't care whether there is a pending record waiting for an ID.

I might choose to use criteria similar to "s_date = Date() AND NOT ( ISNULL( Me.ID ) )" as a way to know when you have 20 STORED and COMPLETED records. But of course if you have another way to decide what is a complete record, certainly use your other method.

 

[Gasman]

Delete some records and try again. Use = 20

Put some debug lines in a walk your code

However is there only meant to be 20 records regardless of who keyed them in, or 20 per user?

 

[azhar2006]

That count of records includes "phantom" records that might have been created incorrectly. Because your DCount and that form probably have slightly different criteria, they can come up with different numbers of records. For instance, if the form doesn't have the reference to Me.ID but the DCount DOES, the form doesn't care whether there is a pending record waiting for an ID.

I might choose to use criteria similar to "s_date = Date() AND NOT ( ISNULL( Me.ID ) )" as a way to know when you have 20 STORED and COMPLETED records. But of course if you have another way to decide what is a complete record, certainly use your other method.

"s_date = Date() AND NOT ( ISNULL( Me.ID ) )"

 

[Gasman]

I do not think that is going to work?

 

[azhar2006]

I do not think that is going to work?

I don't know why I love you so much. You are amazing Gasman

 

[Pat Hartman]

You should look for > 19. That will produce the correct result once you move the test to the form's BeforeUpdate event and check first for Me.NewRecored.

The BeforeInsert event runs as soon as the user types a character into the form and BEFORE any data is entered in any controls.

 

[azhar2006]

You should look for > 19. That will produce the correct result once you move the test to the form's BeforeUpdate event and check first for Me.NewRecored.

The BeforeInsert event runs as soon as the user types a character into the form and BEFORE any data is entered in any controls.

When I use the code in the BeforeUpdate event I get an error on the line to add a new record. But if I use the code in the BeforeInsert event I don't get an error. I get a message that I can't add a new record for today.

 

[arnelgp]

you can also use CmdNewRec click event:

Private Sub CmdNewRec_Click()
If DCount("ID", "tblMaid", "s_date = #" & Format(Date(), "m/d/yyyy") & "#") = 20 Then
    Msgbox "Maximum 20 entries have been reached. Try again tomorrow."
Else
    DoCmd.GotoRecod, , acNewRec
End If
End Sub

 

[Gasman]

you can also use CmdNewRec click event:

Private Sub CmdNewRec_Click()
If DCount("ID", "tblMaid", "s_date = #" & Format(Date(), "m/d/yyyy") & "#") = 20 Then
    Msgbox "Maximum 20 entries have been reached. Try again tomorrow."
Else
    DoCmd.GotoRecod, , acNewRec
End If
End Sub

@arnelgp
Why would you go to all that trouble to format and concatenate the date, when it works just as well as Date() ? See post #6.
Plus I was going to suggest > 19 this morning.

 

[Pat Hartman]

When I use the code in the BeforeUpdate event I get an error on the line to add a new record. But if I use the code in the BeforeInsert event I don't get an error. I get a message that I can't add a new record for today.

I don't see where you tried the code I suggested. You also didn't tell us what your error message is.

 

[azhar2006]

I don't see where you tried the code I suggested. You also didn't tell us what your error message is.

 

[Pat Hartman]

Please use your words. Don't make me open the database to try to figure out what you did.