form / subform - limit allowadditions to one (1)

[Rick A]

Is there a way to allowadditions but only one?

I created a form with a subform. The "on exit" for last field on the subform has code that sets focus to a field on the main form. That's not the issue but background.

The issue is when allowadditions is yes and I tab out of the subform the screen shows an empty subform ready for more information. I do not want that behavior. I want the subform to continue showing the data currently entered. Is there a way to allowadditions but limit the number of new records/rows to one (1)?

Thanks,

 

[Allen Browne]

What is the connection between the main form's table and the subform's
table?

Take an example where the main form is the Invoice table, and the subform is
the InvoiceDetail table (the line items on the invoice). Presumably you have
created a relationship between the two tables (Tools | Relationships) based
on a common field (InvoiceID in both tables.)

If you want to insist that no invoice can ever have more than one line item,
just make the InvoiceID in the related table the primary key. It will then
be impossible to enter 2 lines for the same invoice.

If you want to do it with code, cancel the BeforeInsert event of the form if
there is already a record in the table. This kind of thing:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Select the record in the main form first.
Else
strWhere = "InvoiceID = " & Me.Parent!InvoiceID
varResult = DCount("*", "Invoice", strWhere)
If varResult > 0 Then
Cancel = True
MsgBox "One related record only."
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Is there a way to allowadditions but only one?

I created a form with a subform. The "on exit" for last field on the
subform has code that sets focus to a field on the main form. That's not
the issue but background.

The issue is when allowadditions is yes and I tab out of the subform the
screen shows an empty subform ready for more information. I do not want
that behavior. I want the subform to continue showing the data currently
entered. Is there a way to allowadditions but limit the number of new
records/rows to one (1)?

Thanks,

 

[Rick Allison]

Allen,

Thanks for the information

I found the "cycle" property and it works perfectly. I set it to "Current
Record".

Thanks,