Limit Records in a Table 2

[DH]

Is it possible to limit the number of records in a table or lock a table to only contain "x" amount of records?
If so can you please explain.

Thank you.

 

[PaulF]

you could write some code that is attached to the data entry form which would check the recordcount of the table and prevent adding records if your limit is reached. But if you only want to displays a certain number of records, you can use the Top property in a query to set the value you want, regardless of the number of records in the table.

PaulF

 

[DH]

Thanks Paul

I would like to add some code that checked the recordcount that prevents adding records if a certain limit is reached such as 5 for example. Do you have any sample code or suggestions on how to add this to a form?

I am have never tried this before and am not sure what to try.

Thanks.

 

[PaulF]

In the form you can add code to check the size of the table. Then call this code when the form opens, and also before you add the new record. Then if the limit is reached, a message box appears, and closes the form.


Private Sub Form_BeforeUpdate(Cancel As Integer)
GetRecCount
End Sub

Private Sub Form_Open(Cancel As Integer)
GetRecCount
End Sub

Private Function GetRecCount()
Dim db As DAO.database, rst As DAO.Recordset
Dim intCount As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("Bus&quot
With rst
If rst.RecordCount > 0 Then
intCount = rst.RecordCount
End If
If intCount >= 5 Then
MsgBox "Table limit reached, No additional records allowed", vbInformation, "Table Limit Reached"
Me.Undo
DoCmd.Close acForm, "FormNameGoesHere"
Exit Function
End If
End With
Set rst = Nothing
Set db = Nothing
End Function

HTH
PaulF

 

[gwinn7]

Why not just use DCOUNT?

Private Function RecordLimitYN() as Boolean

If DCOUNT("ID", "Bus&quot > 4 then RecordLimitYN = True

End Function

The function simply returns true if the limit is reached and false if it does not.

Gary
gwinn7

 

[firebolt]

Looking for help with this question. Not sur what "BUS" means in the code supplied.

Thanks
Firebolt

 

[PaulF]

"BUS" is the name of the Table or Query

PaulF

 

[GeekGirlau]

One tip here - if you use the recordset method recommended by Paul, go to the last record before performing the record count. Otherwise you may not get an accurate count. Change the code to the following:

Private Function GetRecCount()
Dim db As DAO.database, rst As DAO.Recordset
Dim intCount As Integer


Set db = CurrentDb
Set rst = db.OpenRecordset("TableName",dbOpenSnapshot)

With rst
.MoveLast

If .RecordCount > 0 Then
intCount = .RecordCount
End If
End With

<remainder of function>

 

[firebolt]

Thanks, this code is almost working. I have no records in there yet, so when the form is opened I get a messahe that there is no current record and to debug. When I do this (.Movelast) is highlighted. This makes sense there are no records. How can I get around this when I attempt to enter that first record?

Firebolt

 

[firebolt]

It works...thanks to Paulf and GeekGirlau. I made one modification by putting the GetrecCount in Afterupdate not beforeupdate or on openform. Then I could start with 0 records and enter five more. This gives me all I need and completes my project. Great help. :-V

 

[firebolt]

Oops it is working and it's not. When I add the .movelast command into the routine I cannot enter any records because there is no current record and when I delete it and just use the code indicated first it goes beyond my limit of 5 records. Does anyone have any more suggestions..this is all that is not working now so I'm anxious to fix it up.

Thanks again
Firebolt

 

[Lightning]

Try adding error handling code to trap the No records error. If no records then use the error handler to exit the code.

If a record exists this willl be ignored.

HTH
Lightning