Limit number of records in subform (1 Viewer)

[fergler]

I want to limit the number of records in my subform to 5. I've tried different codes found on this forum:

Form_Current
If RecordsetClone.RecordCount >= 5 Then
Me.AllowAdditions = False
End If
End Sub

Or:
Private sub Form_BeforeInsert (Cancel as Integer)
If Me.recordcount >= 5 Then
Cancel = true
Me.Undo
End If
End sub

The result has been that when I add a new record to the Main Form, I can't add any records to the subform at all. The subform fields basically disappear or lock. I've racked my brain and can't figure out what the problem could be. Is it because the subform table itself has more than 5 records? Am I trying to do the impossible.

Thanks for any help you can give me.

Jennifer

 

[Rich]

The code belongs in the subform

 

[fergler]

The code was placed in the subform.

 

[gemma-the-husky]

because you set allow additions to false and you dont seem to be setting it back to true for the new record, so you wont be able to put anything in

i take it you are trying to limit the form to 5 items per main record, nit 5 items in total

you probably also need to requery the recordset clone?

you could probably do the same without that - just with

allowadditions = dcount("*"",recordsource)<5

in the current event

 

[fergler]

Thanks Gemma. Yes, I want to limit the items per the main record to 5.

I'm sorry, I can't quite figure out how

allowadditions = dcount("*"",recordsource)<5

fits it with my current code.

 

[gemma-the-husky]

you are using the recordset clone to test the number of items.

you can actually just do a dcount on the qry driving the subform which is recordsource

hence dcount("*"",recordsource) tells you how many records there are

and in the current event, putting

me.allowadditions = dcount("*"",recordsource)<5

will set allow additions to true if the record count is less than 5, and false otherwise

i thought your problem might be you were only setting the value to false, and not to true

 

[unclejoe]

Hi Jennifer,

How do you want to limit the records? What is the form's recordsource?

Maybe you can use "TOP 5" and filter it by descending?
snip

I want to limit the number of records in my subform to 5. I've tried different codes found on this forum:
Jennifer

 

[fergler]

Thanks everyone for all your help. It turns out, after investigating further, that for some reason my link between the main form and the subform was cancelled or corrupted. So, even though the subform would open, it was opening to a blank record. Now that that is fixed, I am able to successfully limit my records entered to 5.

Thanks again.