fixed number of records in a table

[Khalil Handal]

Hi,
Is it possible to have a fixed number of records in a table?


The user has a form to enter records and they should have only 9 records. How can this be done?

Thanks
Khalil

[CJ_London]

always better to show examples, your post can be interpreted as

'table can only have 9 records'
'table can only have 9 records per user'
'user can only enter up to 9 records at a time'
'user has to enter 9 records at a time'

assuming it is the first per your header, you can't stop them entering directly into a table, but programatically you might use something like

if dcount("*","myTable")>9 then
    msgbox "Yo can only have 9 records"
    cancel=true
end if

in your form before update event

[Khalil Handal]

Hi,
Thank you for the response
I used the code on the form before update event but did not work.
I have a front end and a back end database. It allowed me to add record number 10

Khalil

[orange]

Khalil,

Post your code.

[CJ_London]

try =9 instead

[kd2017]

you can't stop them entering directly into a table

They could by using data macros. I know they're not popular though.

[orange]

You can use a routine as below. But I don't believe there is a way to see what check constraints
exist on tables in Access. So if you do use such routines, you should document same.

' ----------------------------------------------------------------
' Procedure Name: limit_NumRows
' Purpose: Add constraint to table to limit the number of records allowed
' Procedure Kind: Sub
' Procedure Access: Public
' Parameter tblInvolved (String): table to get the constraiint
' Parameter AllowMax (Integer): N=Max number of records allowed
' Author: Jack 'from researching forums
' Date: 20-Aug-18
' ----------------------------------------------------------------
Public Sub limit_NumRows(tblInvolved As String, AllowMax As Integer)
    Dim strSql As String
    strSql = "ALTER TABLE " & tblInvolved _
       & " ADD CONSTRAINT MaxRecs" _
       & " CHECK ((SELECT Count(*) FROM " & tblInvolved & ") <=" & AllowMax & ");"
    Debug.Print strSql
    MsgBox strSql
    CurrentProject.Connection.Execute strSql
End Sub

[Khalil Handal]

Hi,
I have this code working:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   
   Dim intNumberOfRecords As Integer
   intNumberOfRecords = DCount("*", "tblInstitutions")
   Debug.Print intNumberOfRecords
If intNumberOfRecords > 8 Then
    MsgBox "Yo can only have 8 records"
    Cancel = True
End If


End Sub

Thanks to all of you
Khalil

[Micron]

Could be as simple/short as

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Recordset.Recordcount = 8 Then MsgBox "You can only have 8 records."
End Sub

I can understand having a max record count for the many side of a relationship but not for a whole table.

[Welshgasman]

Why not just set the AllowAdditions property of the form to False on load if 9 records exist?

[orange]

Suggest we need the real requirement from Khalil.
Does he want a message to indicate his logical max records has been reached? But user could add more.
Does he want to prevent physical additions once his max records has been reached?

[Khalil Handal]

Hi,
Thank again for all the replies.

I want to prevent physical additions once max records has been reached with a message indicating that the max number of records has ben reached.

Khalil

[isladogs]

Another method:

Private Sub Form_Current()
    If Me.Recordset.RecordCount >= 9 Then
        Me.AllowAdditions = False
        MsgBox "No more records can be added"
    End If
      
End Sub

[Micron]

Isladogs, in a form with navigation controls, you'll get that message for every record you land on that has 9 records.

[isladogs]

Good point!
Perhaps something like the Form_BeforeUpdate event instead? Not tested!