Hello

I have a subform on a form and I want to limit the records that can be entered in it to be no more than 100. I've tried doing this in VBA with the code below. What I get when I test this is the msgbox appearing twice and I can't delete the extra record (and it wont undo itself). How should I change the VBA?

Thanks
pamount

Option Compare Database
Option Explicit

Sub Form_Current()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
If rst.RecordCount >= 100 Then
Me.AllowAdditions = False
Me.Undo
MsgBox "Not allowed more than 100 plans in bag", vbOKOnly
Else
Me.AllowAdditions = True
End If
End Sub

Do you have a .Requery or .Refresh some where in your form's code? This would fire the OnCurrent event a second time.

Perhaps a field/control has a default value being set that forces the allocation of a new AutoNumber. If a main form has a .Requery it will force a save of any record in a subform with pending data (.Data=True).

Any pending data in a subform is written (saved) before any data in the main form is saved. So if you move off a main record without saving (move to another record) the subform changes are saved even if you don't save the data in the main form.

You can use the .Dirty property to determine if there is a pending change and then clear it prior to moving to the next record. That may or may not help your problem; you may need to "step" through your code with the debugger to determine what's going on.

Oh, and welcome to Utter Access.

To get back to your original problem of checking the number of records in the subform - you should do that in the OnCurrent event of the main form: