limiting records on a continuous subform question

[Roadbeer]

Hey all,



I'm trying to limit the number of records that can be entered in a continuous subform based off the selection of a combo box on it's main form.

For instance, If I select 3 in the combo box, I want to limit the number of records that appear in the subform to not exceed 3 and suppress the 4th from appearing.

Any thoughts?

Thanks in advance.

[June7]

You want subform to be filtered so only a specified number of existing records will be displayed? Never seen that. What is purpose?

[Roadbeer]

New records to be displayed.

I'm trying to make a fool-proof registration form for an organization

If a member has 3 guests, on the member form when 3 is selected from the combo box, the continuous sub-form will only allow 3 records to be added to the table 'guests'

[June7]

Okay, that's different.

The real trick is figuring out what event to use - maybe the form BeforeUpdate.

Try something like:

Have a textbox in subform header or footer with expression: =Count("*")

Then code behind the subform:

Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord And Me.textboxname = Me.Parent.comboboxname Then
MsgBox "Whatever message you would like to display to the user."
Cancel = True
Me.Undo
End If
End Sub

[Roadbeer]

Thanks June, with a slight modification it worked.

For some reason the =Count("*") wouldn't stop adding records, but when I bound a text box to the tables PK and set the expression to =Count([ID]) it worked.

Now just for aesthetics, is it possible to suppress the second message box that comes up after the first that says "You can't go to the specified record"?

[June7]

I don't think I got that message when I tested code. If you want to provide your db for analysis, I will try to determine solution.