Sub form question

[Question Boy]

I have a form with a sub-form.

I only want the user to be able to enter one record (never more). How can I
limit it. Right now I'm having issues because the users accidentally tab
through the form onto another record and make duplicate/triplicate/...
entries.

Thank you,

QB

 

[Dirk Goldgar]

Am I right in understanding that you want to limit the records on the
subform, not the main form? You can do this with a combination of code in
the subform and the main form.

In the code module of the form object that is being displayed as the
subform, add this function in the General section:

'----- start of public function code for subform -----
Public Sub LimitRecords()

Const conRecLimit = 1

With Me.RecordsetClone
If .RecordCount > 0 Then .MoveLast
Me.AllowAdditions = (.RecordCount < conRecLimit)
End With

End Sub
'----- end of public function code for subform -----

Then create an event procedure for the Current event of that form, and call
the above function from there:

'----- start of code for subform's Current event -----
Private Sub Form_Current()

LimitRecords

End Sub
'----- end of code for subform's Current event -----

In the Current event of the *main* form, you must also call the LimitRecords
function on the subform:

'----- start of code for main form's Current event -----
Private Sub Form_Current()

Call Me.YourSubformControlName.Form.LimitRecords

End Sub
'----- end of code for main form's Current event -----

In the above, replace "YourSubformControlName" with the name of the subform
control name; that is, the control on the main form that is displaying the
subform.

 

[Question Boy]

Worked like a charm!

But I do have a little question. I understand the code for the sub-form,
but why the code in the parent form?

QB