限制允许的记录数

Private Sub Form_Current()

    Me.AllowAdditions = (Nz(DCount("[IDFieldName]","[TableName]",""),0)<10)

End Sub

根据评论,这似乎足够了:

Me.AllowAdditions = (DCount("[IDFieldName]","[TableName]")<10)

“我想限制表最多只能容纳10条记录。”

对表应用检查约束,使其接受的行数不超过10行。

创建一个一次性过程并运行一次。。。

Public Sub limit_rows()
    Dim strSql As String
    strSql = "ALTER TABLE tblDetails" & vbCrLf & _
        "ADD CONSTRAINT 10_rows_max" & vbCrLf & _
        "CHECK ((SELECT Count(*) FROM tblDetails) < 11);"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql
End Sub

还需要一点时间。

有关用法,请参阅联机注释:

Public Sub SetFormAllowAdditions( _
    ByVal frm As Form, _
    Optional ByVal RecordCountMax As Long = 1)

' Limit count of records in (sub)form to that of RecordCountMax.
' 2016-10-26, Cactus Data ApS, CPH
'
' Call in (sub)form:
'
'   Private Sub LimitRecords()
'       Const RecordsMax As Long = 5
'       Call SetFormAllowAdditions(Me.Form, RecordsMax)
'   End Sub
'
'   Private Sub Form_AfterDelConfirm(Status As Integer)
'       Call LimitRecords
'   End Sub
'
'   Private Sub Form_AfterInsert()
'       Call LimitRecords
'   End Sub
'
'   Private Sub Form_Current()
'       Call LimitRecords
'   End Sub
'
'   Private Sub Form_Open(Cancel As Integer)
'       Call LimitRecords
'   End Sub
'
' If the record count of a subform is to be limited, also
' the parent form must be adjusted:
'
'   Private Sub Form_Current()
'       Call SetFormAllowAdditions(Me.Form)
'   End Sub
'

    Dim AllowAdditions  As Boolean

    With frm
        AllowAdditions = (.RecordsetClone.RecordCount < RecordCountMax)
        If AllowAdditions <> .AllowAdditions Then
            .AllowAdditions = AllowAdditions
        End If
    End With

End Sub

如果要限制可以使用特定表单添加到表中的记录总数,可以使用以下代码:

Private Sub Form_Current()
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone 'Clone because we don't want to move the current record
    If Not rs.EOF Then rs.MoveLast 'Initialize recordset
    If rs.RecordCount >= 10 Then
        Me.AllowAdditions = False
    Else
        Me.AllowAdditions = True
    End If
End Sub