Is it possible to Limit Records in a Form

[amunafc]

Beloved Gurus, Is it possible to limit number of records in a form? e.g. I want to enter only five records and not more than that?

 

[ghudson]

You could try a DCount function in the Before Update event of the form to count the number of records in the table bound to the form and cancel the event if there are already five records in the table.

 

[amunafc]

ghudson
I tried the Dcount but it is coming up with terrible errors. I got some example from the site below:
http://www.techonthenet.com/access/functions/domain/dcount.php

 

[ghudson]

Sometimes it helps to add "microsoft" to your google search... http://office.microsoft.com/en-us/access-help/dcount-function-HA001228817.aspx

This should do what you want...

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

    If DCount("*", "[COLOR="Red"]YourTableNameHere[/COLOR]") >= 5 Then
        MsgBox "Table already has 5 records, you cannot add any more records", vbExclamation, "Record Count Exceeded"
        DoCmd.CancelEvent
    Else
        MsgBox "table does not contain 5 records"
    End If

Exit_Form_BeforeUpdate:
    Exit Sub

Err_Form_BeforeUpdate:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "Form_BeforeUpdate()"
    Resume Exit_Form_BeforeUpdate

End Sub

 

[vbaInet]

It's also been covered on this forum.

Just adding to ghudson's solution, you could use:

Cancel = True

in place of:

DoCmd.CancelEvent

Since this event has the cancel action.

 

[ghudson]

It's also been covered on this forum.

Just adding to ghudson's solution, you could use:

Cancel = True

in place of:

DoCmd.CancelEvent

Since this event has the cancel action.

For my education... What is the difference between using these two in the forms BeforeUpdate event?

Cancel = True
DoCmd.CancelEvent

Thanks!

 

[Banana]

Just another alternative to provide visual feedback to the users:

Use the Form's Current event:

Private Sub Form_Current()

If Me.Recordset.Recordcount >= 5 Then
   Me.AllowAdditions = False
Else
   Me.AllowAdditions = True
End If

End Sub

That way, the form won't show a new blank record when there are 5 or more records and is more visual to the user to what is intended.

 

[vbaInet]

For that method, no difference really. Outside that method, there are some methods that don't have the Cancel action as part of their argument, one would use CancelEvent for those methods. Was just informing the OP re the use of the argument.

 

[amunafc]

GURUS, that was fantastic, the dB is now working, thank you all 1000x. I will come with more questions later

 

[adyas]

Just another alternative to provide visual feedback to the users:

Use the Form's Current event:

Private Sub Form_Current()
 
If Me.Recordset.Recordcount >= 5 Then
   Me.AllowAdditions = False
Else
   Me.AllowAdditions = True
End If
 
End Sub

That way, the form won't show a new blank record when there are 5 or more records and is more visual to the user to what is intended.

Ok, what did I do? Or what didn't I do?

I added this code to my subforms and it has limited the record input for the entire table. I've deleted the code but the restriction is still in place.

Can someone advise please?

Ps I know nothing about VB.

 

[bob fitz]

Open the form in design view. Set the "AllowAdditions" of the property to Yes.