Going around the master-child field setup to add new record

[LeoVer]

I have a form that has a list box and a subform amongst other objects. The
subform is linked to the main form through the value selected in the listbox
and another field in the main form. When I select a value in the list box I
get the record in the subform allowing me to edit the details. So far so
good. Now if I want to add a record in the subform it gives me an error
because the link you need with the master field is the one you are about to
make. However, when you close the error message the record is created anyway,
with the correct linked field. Is there a way around this error message or is
there a better way to go about it?
Thanks,
LeoVer

 

[Jeff Boyce]

In a standard main form/subform construction, the main form holds the
"parent" record and the subform holds the "child(ren) record(s)". Thus, it
doesn't make sense to try adding a child record before you've
added/indicated the parent.

Is your main form bound to any query or table, or are you simply using the
listbox to list possible "parents"? If that is the case, you might want to
consider setting up that main form more like what Access expects to find...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[LeoVer]

Jeff,
Thanks for the fast answer. I thought as much but I was hoping,... I will
have to create a seperate form to create a new records.
thanks again,
LeoVer

 

[Jeff Boyce]

That isn't what I was suggesting.

If the main form you now have is bound to the parent record, your subform
can be used to create related child records. No need for "a separate
form"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[LeoVer]

I know, but the subform is not just linked to the parent form but also to a
list box in the parent form that functions as a control to select the
specific existing record you want to display in the subform by means of the
parent and child proprties. That is the reason why I cannot use the same
subform to create a new record. The subform would look for a value in the
listbox that does not yet exists.

 

[Jeff Boyce]

Once more ...

If you modify your main form/subform construction to follow the standard
approach, this would not be an issue.

For example, you could convert that main form to one that is bound to your
"main" table and has an unbound combobox in the header to allow the user to
select one of the "main" records. Your subform would only need to be
connected to the main form (i.e., the rowID of the parent record) in order
to display existing child records AND to allow creation of new children
records.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP