Stop adding record in subform after record count = 1



Can someone help in in what to put after the THEN statment to allow one
entry if the Record count is =>1 in the Before insert or should I set the
probert after the first entry?

This code is in Before insert event in Sub Form

Private Sub Form_BeforeInsert(Cancel As Integer) ' This goes in subform "b"
If Me.CurrentRecord = 2 And Forms![MainForm].[Field in Main Form] =
Cancel = True '??????
MsgBox "You can only have one invoice per bob entry", vbInformation,
"Data Error"

End If

'From here I want to go back to the form as if I only have
one entry now and sitting as if I just called it up.

End Sub

Thank You


Nov 18, 2013
Reaction score
In the subforms forms OnDirty event
Put the following code
Private Sub Form_Dirty(Cancel As Integer)
If Me.NewRecord = True Then
Me.DataEntry = False
End If
End Sub
That’s it
Works Like a Dream!
Takes the user back to the previous value when they try to type in a new value.

Good Luck
Last edited:

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads