代码之家 › 专栏 › 技术社区 › USERNNNNN

限制允许的记录数

record max ms-access vba database

USERNNNNN · 技术社区 · 6 年前

我有一张“tbdetails”表格,里面保存着学生的详细信息。简单地说,我想将表限制为最多只能保存10条记录。我的表“frmDetails”表单有以下OnCurrent事件,但它不起作用:

Private Sub Form_Current()
   Forms(Detail).MaxRecords = 10
End Sub

我在网上查找发现,但我可以很容易地插入11条和12条记录。欢迎任何回答,不需要VBA(如果可以不使用VBA),这可以通过“属性”菜单或其他方式完成吗?

编辑:

现在当我保存时

运行时错误438:对象不支持此属性或方法

所以这里肯定有问题

4 回复 | 直到 6 年前

SunKnight0 6 年前

Private Sub Form_Current()

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

End Sub

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

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

HansUp 6 年前

“我想限制表最多只能容纳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

Gustav 6 年前

还需要一点时间。

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

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

Erik A 6 年前

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

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