How do I prohibit creation of new records in subform?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mjg - 17 Mar 2008 21:31 GMT

I have a main form w/ two subforms in Access. Based on their parent/child
fields, the subforms call-up existing matching records. My problem is: when
there is no matching record in my subform it automatically creates a new
record and populates it with whatever is entered in the parent field on the
main form. I would like the user to be able to add and edit records on the
main form regarless if there is a match on the subform or not. When there is
a matching child record in the subform, I would like the user to be able to
edit those fields.

I don't think I can use referential integrity to control this because my
main form has many (duplicate) records and this is the 'parent' field in the
link (i.e. a one-to-many relationship).

Any tips?

Reply to this Message

Maurice - 17 Mar 2008 21:50 GMT

My first hunch would say that the subform could be set to allow edits=true
and allow additions=false. You can set these properties in the form
properties of the subform.

hth

Signature

Maurice Ausum

> I have a main form w/ two subforms in Access. Based on their parent/child
> fields, the subforms call-up existing matching records. My problem is: when
[quoted text clipped - 10 lines]
>
> Any tips?

Reply to this Message

mjg - 18 Mar 2008 15:34 GMT

Your solution worked! It ended up being slimpler than I'd anticipated -
thanks.

Now I'm trying to figure out how to get the tab-order to skip that subform,
but only when there is no matching record (nothing to edit).
-mjg

> My first hunch would say that the subform could be set to allow edits=true
> and allow additions=false. You can set these properties in the form
[quoted text clipped - 16 lines]
> >
> > Any tips?

Reply to this Message

Allen Browne - 18 Mar 2008 04:46 GMT

There is something wrong with the subform if it is automatically creating
new record. The trick will be to identify what's doing that, and fix that
problem.

Typically you are looking for some code that assigns a value to a bound
control. It could be in the Current event of the form (the subform), or
perhaps the Enter event of the first control on that form.

To help you pin it down, set the form's RecordSelector property to Yes. This
shows a block to the left, the height of the record. When the form is
dirtied, the icon in the RecordSelector changes to a pencil to indicate
editing is in progress, so this may help you identify when it's being
dirtied.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a main form w/ two subforms in Access. Based on their parent/child
> fields, the subforms call-up existing matching records. My problem is:
[quoted text clipped - 15 lines]
>
> Any tips?

Reply to this Message