Solved Limit records PER MONTH (1 Viewer)

[anski]

The code below by WatsonDyar works to limit number of records entered in a form.

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord = True And Me.RecordsetClone.RecordCount = 3 Then
MsgBox "Cannot add more than 3 rating officials. Press OK to continue.", vbOKOnly, "Maximum Number of Rating Officials Met"
DoCmd.GoToRecord , , acPrevious
End If
End Sub

I want to limit number of records entered in a form PER MONTH. Each month should have only a maximum of 5 records. How do I do this? tia.

 

[arnelgp]

almost same and you need a Datefield in your table:

Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = (DCount("1", "yourTable", "Format([DateField], 'yyyymm') = '" & Format(Date, "yyyymm") & "'") = 5)
If Cancel
MsgBox "Cannot add more than 5 rating officials. Press OK to continue.", vbOKOnly, "Maximum Number of Rating Officials Met"
End If
End Sub

 

[anski]

almost same and you need a Datefield in your table:

Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = (DCount("1", "yourTable", "Format([DateField], 'yyyymm') = '" & Format(Date, "yyyymm") & "'") = 5)
If Cancel
MsgBox "Cannot add more than 5 rating officials. Press OK to continue.", vbOKOnly, "Maximum Number of Rating Officials Met"
End If
End Sub

hi. this is what i did but it still allows me to add more than 5 records.

 

[ebs17]

Cancel = (DCount("1", "yourTable", "Format([DateField], 'yyyymm') = '" & Format(Date, "yyyymm") & "'") = 5)

Small typo. The date field from the form record must be used. This could have a different month than the current month from today.

Note: DCount checking on the table is more secure than RecordCount checking on the form recordset. If the form were additionally filtered, only the filter result would be counted.

If you program carefully, you are aware that table entries cannot only be made via a bound form. Protection at table level using CONSTRAINT would be safe.

 

[Pat Hartman]

Cancel = (DCount("*", "yourTable", "Format([DateField], 'yyyymm') = '" & Format(Date, "yyyymm") & "'") >= 5)

You should never use "=" as the relational operator in a situation like this. Always use ">=" for safety sake.

 

[Pat Hartman]

Never forget the software "glitch" that brought down Jurassic Park You may not have noticed it if you only saw the movie but, you would have if you had read the book. The other subilty was the genes they spliced to fill in the gaps with the dinosaurs came from an African frog that could change sex if the ratio in their community became too unbalanced. The scientists cloned only females. So some of them changed sex to allow them to reproduce.

Their security system "knew" how many of each type of animal was in each enclosure and so they counted, 1, 2, 3, 4, 5 - got em' all. Next enclosure ... So they never noticed that the animals were reproducing until it was too late.