473,604 Members | 2,562 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
Nov 12 '05 #1
6 2028
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!SubCon trol.Form.Setfo cus
DoCmd.GoToRecor d , , 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.c o.nz> wrote in message
news:42******** *************** ***@posting.goo gle.com...
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

Nov 12 '05 #2
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.
Nov 12 '05 #3
no****@nospam.n ospam (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
Nov 12 '05 #4
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 ...
Nov 12 '05 #5
no****@nospam.n ospam (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
automaticall y 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
synchronizatio n 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
Nov 12 '05 #6
starwars <no****@tatooin e.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
Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2334
by: Joseph J. Egan | last post by:
I am extending an existing Access/VBA app and need to update a subform displayed in continuous view within a containing form. The existing form and subform have worked fine to date with the subform's RecordSource set to an underlying DB table. To extend the existing app, I need to add controls to the subform and set their Text values etcetera at runtime. (Clearly if a single RecordSource would do the trick, I would extend the...
3
1661
by: Andrew Wrigley | last post by:
Hi I have what seems a corruption of an mdb that is not solved by the decompile command line trick. The problem appears when clicking on a subform control (check box) that has a event handler associated with it. Everything works ok, until I click on the check box, whereupon Access fails with an application error. It appears that execution never
4
6993
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the user enters the last qualifying field on the main form. In one case this works fine, the subform shows the data the user wants to update -- which means showing all the data put in previously (ie showing this via the requery and the continuous...
2
1368
by: Geoff | last post by:
I have a form with a subform. The latter contains a calculated field, , derived from a query. In the footer of this subform is an unbound text box whose control source = SUM() . works fine I want to save the value in into a variable in the main form called . This is bound to Tbl_Products
2
5190
by: emc_cos | last post by:
I have a very nice parent child relationship happening between a main form and 8 subforms. The main form is the data entry point for a table that holds textual data like names, notes and the like. It also holds the primary key which is an alpha-numeric file designation. The subforms are the data entry point for another table that holds numeric values for time spent on tasks.
5
2079
by: Don Do | last post by:
Ok, this is my 2nd ever post. 1st one was solved quickly. Thanks. Now I'm stuck again. My mdb has a main form with 3 subforms. Each tied to the succeeding by single autonumberID/foreign fields. Each of the subforms is in continuous form format. The forms seems to work well as far as viewing or adding records in each of the subforms, except sbf2. After I have added records in sbf4, everything still looks good. I can click around,...
0
991
by: sandy21380 | last post by:
I have the following code in my program allowing users to search for entries matching up to five different criteria at once. I changed the form to now contain a subform and would like to allow the users to search by fields contained in the subform. The field on the subform that I would like to include in the search is , but just including in the code did not do the trick -the search was not recognizing the subform field . The code is pasted...
4
5198
WyvsEyeView
by: WyvsEyeView | last post by:
I am doing the very standard thing of filtering the contents of one combo box based on another combo box. I've done it many times, but always on a main form. Now I'm trying to do it on a datasheet subform and it is not working. After making the selection in the first combo box, when I move to the second combo box, I get the Access input parameters box. If I input the value that is in the first combo box, I get the expected results in the second...
5
9611
mshmyob
by: mshmyob | last post by:
This demo is a simple way to change the colours in a subform to highlite different conditions. Access only allows a maximum of 4 conditional changes to individual controls but with this simple trick you can apply thousands of colour changes to any number of controls or even entire rows in a subform set to continuous forms. Note: This works only in AC2007 I believe. Since I notice more and more AC2007 questions and always see questions...
0
7929
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8274
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
5882
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5441
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3906
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3954
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2433
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1522
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1262
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.