The old subform trick.....

[Ray]

I have a main form with a subform. The main form has a combo box which
lists all the clients which in turn are displayed in the subform.

The subform is bound to the combo box and all work well. however, I
would like to be able to add a new client in the subform but this is,
of course bound to the combo box. Can anyone tell me if there is a way
to enter a new client in the subform with out receiving an error
message?

TIA - Ray

[Mike Storr]

I am assuming you have some sort of relationship setup between the combox
source and the source of the subform. Using a button or some other method
you can move the subform to a new record with

MainForm!SubControl.Form.Setfocus
DoCmd.GoToRecord , , acNewRec

You will however need to provide some way of entering the new related data
that appears in the combobox unless the combobox just displays one of the
fields in the subform. In wich case, you'll probably want to requery the
combobox after saving the record in order to display the new entry.

Mike Storr
www.veraccess.com

"Ray" <wa*****@xtra.co.nz> wrote in message
news:42**************************@posting.google.c om...

I have a main form with a subform. The main form has a combo box which
lists all the clients which in turn are displayed in the subform.

The subform is bound to the combo box and all work well. however, I
would like to be able to add a new client in the subform but this is,
of course bound to the combo box. Can anyone tell me if there is a way
to enter a new client in the subform with out receiving an error
message?

TIA - Ray

[Steve Jorgensen]

On 20 Jan 2004 12:40:00 -0800, wa*****@xtra.co.nz (Ray) wrote:

I have a main form with a subform. The main form has a combo box which
lists all the clients which in turn are displayed in the subform.

The subform is bound to the combo box and all work well. however, I
would like to be able to add a new client in the subform but this is,
of course bound to the combo box. Can anyone tell me if there is a way
to enter a new client in the subform with out receiving an error
message?

TIA - Ray

I love the "old subform trick", and I use it a lot, but this is a case where
it doesn't work. The problem is that the one of the jobs of the master/child
form relationship is to automatically insert the link value into the child
record when you create a new one. In this case, it's probably an auto-number,
so whatever you try to insert into it is going to fail, and your insert will
not happen.

What you have to do is sacrifice the master/child links technique, and simply
filter the subform from the AfterUpdate handler of the combo box (using Filter
and FilterOn properties). I also like to set AllowAdditions to True only when
adding a new record, and set it to False when editing an existing record.

[David W. Fenton]

no****@nospam.nospam (Steve Jorgensen) wrote in
<j1********************************@4ax.com>:

On 20 Jan 2004 12:40:00 -0800, wa*****@xtra.co.nz (Ray) wrote:

I have a main form with a subform. The main form has a combo box
which lists all the clients which in turn are displayed in the
subform.

The subform is bound to the combo box and all work well. however,
I would like to be able to add a new client in the subform but
this is, of course bound to the combo box. Can anyone tell me if
there is a way to enter a new client in the subform with out
receiving an error message?

I love the "old subform trick", and I use it a lot, but this is a
case where it doesn't work. The problem is that the one of the
jobs of the master/child form relationship is to automatically
insert the link value into the child record when you create a new
one. In this case, it's probably an auto-number, so whatever you
try to insert into it is going to fail, and your insert will not
happen.

What you have to do is sacrifice the master/child links technique,
and simply filter the subform from the AfterUpdate handler of the
combo box (using Filter and FilterOn properties). I also like to
set AllowAdditions to True only when adding a new record, and set
it to False when editing an existing record.

Well, your response, Steve, is showing me that you have a different
philosophy about adding records than I do. I almost never add new
records for major entities in the form that is used to edit them. I
do them in an unbound dialog form that collects only the subset of
fields required to create a new record, then uses DAO to insert the
record and then loads that new record into the data editing form
for further editing.

I see no reason why such a method could be used with the scenario
of the original poster.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Tue, 20 Jan 2004 22:43:40 GMT, dX********@bway.net.invalid (David W.
Fenton) wrote:

no****@nospam.nospam (Steve Jorgensen) wrote in
<j1********************************@4ax.com>:

On 20 Jan 2004 12:40:00 -0800, wa*****@xtra.co.nz (Ray) wrote:

I have a main form with a subform. The main form has a combo box
which lists all the clients which in turn are displayed in the
subform.

The subform is bound to the combo box and all work well. however,
I would like to be able to add a new client in the subform but
this is, of course bound to the combo box. Can anyone tell me if
there is a way to enter a new client in the subform with out
receiving an error message?

I love the "old subform trick", and I use it a lot, but this is a
case where it doesn't work. The problem is that the one of the
jobs of the master/child form relationship is to automatically
insert the link value into the child record when you create a new
one. In this case, it's probably an auto-number, so whatever you
try to insert into it is going to fail, and your insert will not
happen.

What you have to do is sacrifice the master/child links technique,
and simply filter the subform from the AfterUpdate handler of the
combo box (using Filter and FilterOn properties). I also like to
set AllowAdditions to True only when adding a new record, and set
it to False when editing an existing record.

Well, your response, Steve, is showing me that you have a different
philosophy about adding records than I do. I almost never add new
records for major entities in the form that is used to edit them. I
do them in an unbound dialog form that collects only the subset of
fields required to create a new record, then uses DAO to insert the
record and then loads that new record into the data editing form
for further editing.

I see no reason why such a method could be used with the scenario
of the original poster.

Well, it could, of course, and I've used that technique also. I actually
waffle back and forth on which approach is best.

The problem with the separate form technique is that adding the record happens
in 2 steps, and it's easier to leave a record 1/2 entered this way than if you
enter all the data in one form. Also, that technique requires all the
required fields to be added in the initial add-form, and what if you change
the Required status of a field later? The code is fragile in those cases.

Of course, you could solve that by putting -all- the fields on the add-form,
but now you have significant duplication between the add-form and the
edit-form. This, too, can be worked around by using the same subform for
adding/editing, but contained in a separate master form. This always seemed a
bit kludgey to me, but perhaps, it is worthy of some experimentation since I
have never actually tried this out beyond a simple feasibility test. One
issue I see right off the bat is that the add form would kind of want to be
modal, but the action probably has no other reason to be modal, and this could
be an inconvenience for the user. If the form is not modal, then you have to
have all the messy synchronization when the new record is saved. It would be
better if the add form didn't have to know or care where it was invoked form.

OK, that's a bit blathery, and thinking out loud, but ...

[David W. Fenton]

no****@nospam.nospam (Steve Jorgensen) wrote in
<e2********************************@4ax.com>:

On Tue, 20 Jan 2004 22:43:40 GMT, dX********@bway.net.invalid
(David W. Fenton) wrote:

no****@nospam.nospam (Steve Jorgensen) wrote in
<j1********************************@4ax.com>:

On 20 Jan 2004 12:40:00 -0800, wa*****@xtra.co.nz (Ray) wrote:

I have a main form with a subform. The main form has a combo
box which lists all the clients which in turn are displayed in
the subform.

The subform is bound to the combo box and all work well.
however, I would like to be able to add a new client in the
subform but this is, of course bound to the combo box. Can
anyone tell me if there is a way to enter a new client in the
subform with out receiving an error message?

I love the "old subform trick", and I use it a lot, but this is
a case where it doesn't work. The problem is that the one of
the jobs of the master/child form relationship is to
automatically insert the link value into the child record when
you create a new one. In this case, it's probably an
auto-number, so whatever you try to insert into it is going to
fail, and your insert will not happen.

What you have to do is sacrifice the master/child links
technique, and simply filter the subform from the AfterUpdate
handler of the combo box (using Filter and FilterOn properties).
I also like to set AllowAdditions to True only when adding a
new record, and set it to False when editing an existing record.
Well, your response, Steve, is showing me that you have a
different philosophy about adding records than I do. I almost
never add new records for major entities in the form that is used
to edit them. I do them in an unbound dialog form that collects
only the subset of fields required to create a new record, then
uses DAO to insert the record and then loads that new record into
the data editing form for further editing.

I see no reason why such a method could be used with the scenario
of the original poster.

Well, it could, of course, and I've used that technique also. I
actually waffle back and forth on which approach is best.

The problem with the separate form technique is that adding the
record happens in 2 steps, and it's easier to leave a record 1/2
entered this way than if you enter all the data in one form.
Also, that technique requires all the required fields to be added
in the initial add-form, and what if you change the Required
status of a field later? The code is fragile in those cases.

I've implemented this dozens of times and have never needed to go
back and alter it. Required fields don't generally change my
schemas. And I also defined "required" as something different the
fields that are required in the target data table. In general, I
don't have very many non-foreign-key required fields in any of my
apps.

Now, that said, I realize that I don't use dialog forms for
anything but *parent* entities. For instance, I don't use a dialog
to add invoice detail items. Indeed, that's one of the few cases
where I allow any editing in a continuous form at all.

So, I guess I will alter what I said: for high-level entities, a
dialog form is better, especially if there are a small number of
required fields and a large number of optional ones.

Now, in almost any schema, some tables are both parents and
children. Take a person table and a company table. The person
records are likely to be children of a company table, but in
person-centric applications we treat it the other way, with company
being something of a lookup table for the person, where company is
just an attribute of the person. If, on the other hand, your app
was company-centric, this might be different.

In either application, though, I'd add both companies and people
with an unbound dialog form.

So, it's not a hard and fast rule, obviously.
Of course, you could solve that by putting -all- the fields on the
add-form, but now you have significant duplication between the
add-form and the edit-form. . . .
Well, I long ago concluded that forms that are useful for editing
are often not well-suited to the process of adding records. In a
lot of my apps, records are added in scenarios where you want to
get down very basic information (say, you're on a telephone call
and entering a new person, and their company is also new to your
database) -- needing to enter this data in complex forms, where the
basics you want to enter quickly may not even be on the same screen
(most of my complex entities are on multi-tab forms) makes this
process more difficult. Secondly, it makes abandoning a record
harder, as you must delete a record that has already been created.
Third, it means you have to do possible duplicate checking using
the events of the editing form, and I find this kind of code
unnecessarily complicates the editing form.

Putting all of these things into a separate unbound form is much
easier to handle, in my experience, and though you have a small
amount of duplication, the function is not duplicated at all.
. . . This, too, can be worked around by
using the same subform for adding/editing, but contained in a
separate master form. This always seemed a bit kludgey to me, but
perhaps, it is worthy of some experimentation since I have never
actually tried this out beyond a simple feasibility test. One
issue I see right off the bat is that the add form would kind of
want to be modal, but the action probably has no other reason to
be modal, and this could be an inconvenience for the user. If the
form is not modal, then you have to have all the messy
synchronization when the new record is saved. It would be better
if the add form didn't have to know or care where it was invoked
form.

OK, that's a bit blathery, and thinking out loud, but ...

I object to it on the basis of the fact that it's simply too hard
to control updates to a bound form. Record cancellation and
validation and duplicate checking is tough to implement reliably
without great complications (do you fire only when adding a new
record? well, how do you determine that you're adding a new record?
Etc.). I find it much easier to manage with a separate unbound
form.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tony Toews]

starwars <no****@tatooine.homelinux.net> wrote:

Comments: This message did not originate from the Sender address above.
It was remailed automatically by anonymizing remailer software.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm