CODEWITHSUNDEEP
vbams-access
tmband25
tmband25

Reputation: 11

Limiting appearance of new record in MS Access Continuous Form

I have a continuous (sub)form that I want to only allow to display up to three records, including insert/update/delete. You should not be able to insert a fourth record.

So I have some events setting Me.AllowAdditions on/off, and this sort of works except:

When inserting the third record, as far as I can tell the appearance of the "new" new record occurs before everything I would want to hook in to (AfterUpdate or AfterInsert). There isn't even a third record yet to disable Me.AllowAdditions with, so even if I get it to disable in BeforeInsert/BeforeUpdate I'm not going to get my insert to finish. The AfterUpdate disable works, but that only triggers when I click off the control, causing the insert to complete.

Is there a way to stop the next new record slot appearing before the insert is complete? The only remaining tactic I can think of is forcing the insert ASAP before the usual trigger, but that's not good behavior as far as I'm concerned.

The whole lot could be replaced with unbound controls and VBA, which I probably should have done, but just thought I'd check if this is even possible without butchering Access (ex: essentially allows disallowing updates and then using VBA to workaround it).

Upvotes: 1

Views: 293

Answers (1)

Gustav
Gustav

Reputation: 55816

That can be done using this function where you adjust RecordsMax to 3:

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, Gustav Brock, 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

Please study the in-line comments for the usage and implementation.

Upvotes: 1

Related Questions