473,326 Members | 1,972 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Unbound Nested Subform Simulating Continuous Form

Need some help to stop me going around in circles on this one....

Have a nested subform (subform2) which simulates a continuous form for the
record on the parent subform.

Subform2 has rows of either an option button plus two text fields or a
checkbox plus two text fields

Am wanting to save the user entries into an underlying table. Tag property
for each option button, check box or text field has the value of the key
field for each record to be saved to the underlying table.

Any help/suggestions on best way to do this appreciated.

Thanks

Bob
Jan 2 '06 #1
20 10750
Why use an unbound form, when you want to save the values into a table?

Why go to the trouble of trying to simlate an unbound continuous form when a
bound one is handed to you on a platter and requires next to effort to
develop?

The "best way" will be to use a bound continuous subform.

--
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.

"Robert" <ro*******@bigpond.com> wrote in message
news:vj*******************@news-server.bigpond.net.au...
Need some help to stop me going around in circles on this one....

Have a nested subform (subform2) which simulates a continuous form for the
record on the parent subform.

Subform2 has rows of either an option button plus two text fields or a
checkbox plus two text fields

Am wanting to save the user entries into an underlying table. Tag property
for each option button, check box or text field has the value of the key
field for each record to be saved to the underlying table.

Any help/suggestions on best way to do this appreciated.

Thanks

Bob

Jan 2 '06 #2
Allen,

Started with a bound subform but switched to using an unbound subform for
better error control, more control over saving records (single button on
parent subform) and for more user friendly data capture (option buttons &
check boxes rather than combo boxes on continuous subform).

Parent subform has "Project" (one) details and nested subform has (many)
"Products" that can be selected via option buttons or check boxes and layout
of the form is not on a one record (Product) per row basis.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...
Why use an unbound form, when you want to save the values into a table?

Why go to the trouble of trying to simlate an unbound continuous form when
a bound one is handed to you on a platter and requires next to effort to
develop?

The "best way" will be to use a bound continuous subform.

--
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.

"Robert" <ro*******@bigpond.com> wrote in message
news:vj*******************@news-server.bigpond.net.au...
Need some help to stop me going around in circles on this one....

Have a nested subform (subform2) which simulates a continuous form for
the record on the parent subform.

Subform2 has rows of either an option button plus two text fields or a
checkbox plus two text fields

Am wanting to save the user entries into an underlying table. Tag
property for each option button, check box or text field has the value of
the key field for each record to be saved to the underlying table.

Any help/suggestions on best way to do this appreciated.

Thanks

Bob


Jan 2 '06 #3
What's your question?
You want to save some values?
And you don't want to do it the Access way?

You, of course, are way better than those stiffs who designed Access on
the one hand, but on the other hand, you don't know how to solve this
simple problem? Fabulous! Congratulations!

So save them. Use an INSERT INTO query if they're new. Use an UPDATE
query if they're not.
What's the problem?

Jan 2 '06 #4
If you do come up with a better design that the one Access gives you for
free, please let us know.

--
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.

"Robert" <ro*******@bigpond.com> wrote in message
news:1S********************@news-server.bigpond.net.au...
Allen,

Started with a bound subform but switched to using an unbound subform for
better error control, more control over saving records (single button on
parent subform) and for more user friendly data capture (option buttons &
check boxes rather than combo boxes on continuous subform).

Parent subform has "Project" (one) details and nested subform has (many)
"Products" that can be selected via option buttons or check boxes and
layout of the form is not on a one record (Product) per row basis.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...
Why use an unbound form, when you want to save the values into a table?

Why go to the trouble of trying to simlate an unbound continuous form
when a bound one is handed to you on a platter and requires next to
effort to develop?

The "best way" will be to use a bound continuous subform.
"Robert" <ro*******@bigpond.com> wrote in message
news:vj*******************@news-server.bigpond.net.au...
Need some help to stop me going around in circles on this one....

Have a nested subform (subform2) which simulates a continuous form for
the record on the parent subform.

Subform2 has rows of either an option button plus two text fields or a
checkbox plus two text fields

Am wanting to save the user entries into an underlying table. Tag
property for each option button, check box or text field has the value
of the key field for each record to be saved to the underlying table.

Any help/suggestions on best way to do this appreciated.

Jan 2 '06 #5
Lyle,

Sorry new at access, am using a currentdb.execute "insert into...statement
for new records being added. Have product number (unique identifier) in the
tag property of each control which allows grouping controls into records.
Just not sure on how to read through all the controls of the nested subform
and group them into records using the tag property including for each record
specifyig which control is to be saved to which feld before using the insert
into query.

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
What's your question?
You want to save some values?
And you don't want to do it the Access way?

You, of course, are way better than those stiffs who designed Access on
the one hand, but on the other hand, you don't know how to solve this
simple problem? Fabulous! Congratulations!

So save them. Use an INSERT INTO query if they're new. Use an UPDATE
query if they're not.
What's the problem?

Jan 2 '06 #6
Probably how to read through would depend on how you created them in
the first place. You say your nested subform simulates a continuous
form? How? Is the initial data (that is displayed) part of a recordset?
Where does it come from? How many rows are on this simulated continous
form? How is this number decided? What happens when the user wants to
create a new row? What happens when the user wants to delete a row? If
you use recordset is it a DAO or ADO recordset? etc. etc etc.

Jan 2 '06 #7
Lyle,

Nested subform appears to user as as a single form containing a number of
option buttons (4), a number of text boxes (12) and check boxes (11).

Each pair of the option buttons (not option group) represents an either/or
product choice

Each check box represents a product choice.

The text boxes are some additional fields used for additional info that
relate to 6 of the product choices (option buttons/checkboxes)

Subform is being used for initial data entry (same methodology will be used
to edit existing records but via a separate view/edit form). During initial
data entry user can include/exclude products by selecting/unselecting the
optionbuttons/checkboxes.

User enters details for a single project on the parent subform and selects
one or more products (multiple) on the nested subform.

Am using ADO to add record on the ubound parent subform and using
CurrentDB.Execute "Insert Into... to add each product record in the unbound
nested subform to the related (many) table using ProjectNr and ProductNr as
key fields. The Tag property for each option button/check box has the
ProductNr in it. The Textboxes also have the ProuctNr in the Tag property to
allow these to be associated with the relevant Product
(Optionbutton/Checkbox).

Have been trying to write code to use the TAG info and convert the info
loaded in the nested product subform by using the Insert Into query in a
loop to add the (multiple) product records to the underlying
tblProjectProducts. Not sure if using an array is necessary?
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...
Probably how to read through would depend on how you created them in
the first place. You say your nested subform simulates a continuous
form? How? Is the initial data (that is displayed) part of a recordset?
Where does it come from? How many rows are on this simulated continous
form? How is this number decided? What happens when the user wants to
create a new row? What happens when the user wants to delete a row? If
you use recordset is it a DAO or ADO recordset? etc. etc etc.

Jan 2 '06 #8
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:43**********************@per-qv1-newsreader-01.iinet.net.au:
If you do come up with a better design that the one Access gives
you for free, please let us know.


I don't believe in using editable continuous forms, except in very
specific situations.

If I want to edit data presented in a continuous form, I use a
detail form that can be bound or unbound. This makes the continuous
form like a very elaborate listbox with lots of extra features, and
with the detail form populated in the OnCurrent event of the list
form.

Going unbound gives control over certain things, but also takes away
any number of extremely useful features (such as the .Dirty
property). I've only implemented this kind of thing very, very
seldom.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jan 2 '06 #9
"Robert" <ro*******@bigpond.com> wrote in
news:c%*******************@news-server.bigpond.net.au:
Sorry new at access, am using a currentdb.execute "insert
into...statement for new records being added. Have product number
(unique identifier) in the tag property of each control which
allows grouping controls into records. Just not sure on how to
read through all the controls of the nested subform and group them
into records using the tag property including for each record
specifyig which control is to be saved to which feld before using
the insert into query.


I, for one, am not going to try to puzzle out an answer to your
question, since you're quite obviously simply mis-using Access, and
avoiding all the features it provides for accomplishing the tasks at
hand.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jan 2 '06 #10
Bri

Robert wrote:
Allen,

Started with a bound subform but switched to using an unbound subform for
better error control, more control over saving records (single button on
parent subform) and for more user friendly data capture (option buttons &
check boxes rather than combo boxes on continuous subform).

Parent subform has "Project" (one) details and nested subform has (many)
"Products" that can be selected via option buttons or check boxes and layout
of the form is not on a one record (Product) per row basis.


I understand your issue and I have a different approach to solving it. I
create a temp table (in a temp database - linked) that I then use in the
bound subform. The Save button then does an Update query that moves the
data from the temp table to the permanent table. This gives me the
flexablility that a bound form gives with the ability to save all the
changes or none of the changes (like a transaction commit or rollback
but in a form rather than in code).

--
Bri

Jan 2 '06 #11
I am guessing:
SubForm (UnBound) -> Projects
Sub-SubForm (Unbound) -> Products

Products can exist only if a Project is specified. We have a many
(Product) relationship to one (Project).

So Project 1 may have Products 7, 8 , 12.

Do this mean there is a Project-Product Table with rows
1 - 7
1 - 8
1 - 12
?

You are using ADO to add Projects and DAO to add Products? Is this some
kind of egalitarianism? If not, why?

And Tag? You are using the Tag Property of each Product to hold the
Value of the Project to which it belongs? Quaint! I can't think of a
thing wrong with it except prejudice because "We don't do things like
that around here, Sonny!" Regardless, to me, its unusual.

I would have put a ProjectID field on my sub-subform and in the
OnCurrent event in my SubForm made the default value of that ProjectID
Field (in the sub sub form) the value of theProject ID of the SubForm
and requeried the SubSubForm . If subform was on new record I would
save the new project record first.

Bur I am totally guessing here AND I am wondering (1%) if you are using
Unbound and Unlinked as the same thing?

I am quite confused and can only hope you or someone else will clear
things up.

Jan 2 '06 #12
Lyle,

Yes to ProjectProducts table.

Parent subform and nested subform are both unbound but "linked" using code
via ProjectId (have a single save button on the parent subform)

The Tag property holds the ProductId (not the ProjectId) which is used
together with the ProjectId (as key fields) to add products (and some
additional Textbox fields associated with some of the products) for this
project to ProjectProducts table.

So rows in table are:

ProjectId ProductNr ProductSites ProductComment
1 7 12 Anything
1 8 3 Something


"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
I am guessing:
SubForm (UnBound) -> Projects
Sub-SubForm (Unbound) -> Products

Products can exist only if a Project is specified. We have a many
(Product) relationship to one (Project).

So Project 1 may have Products 7, 8 , 12.

Do this mean there is a Project-Product Table with rows
1 - 7
1 - 8
1 - 12
?

You are using ADO to add Projects and DAO to add Products? Is this some
kind of egalitarianism? If not, why?

And Tag? You are using the Tag Property of each Product to hold the
Value of the Project to which it belongs? Quaint! I can't think of a
thing wrong with it except prejudice because "We don't do things like
that around here, Sonny!" Regardless, to me, its unusual.

I would have put a ProjectID field on my sub-subform and in the
OnCurrent event in my SubForm made the default value of that ProjectID
Field (in the sub sub form) the value of theProject ID of the SubForm
and requeried the SubSubForm . If subform was on new record I would
save the new project record first.

Bur I am totally guessing here AND I am wondering (1%) if you are using
Unbound and Unlinked as the same thing?

I am quite confused and can only hope you or someone else will clear
things up.

Jan 2 '06 #13
David,

Am using unbound parent subform and unbound nested subform for data entry
(i.e. adding new projects) in a one (Project) to many (Products)
relationship.

Instead of the nested subform being continous with one of the controls being
a combobox having a list of products, the nested subform presents all of the
products available as a option buttons and check boxes that the user can
select/deselect.

Am using unbound subforms to provide better error control, more control over
saving records (single button on
parent subform) and for more user friendly data capture (option buttons &
check boxes rather than combo boxes on a continuous subform).

Just trying to determine best way to loop through controls on the nested
subform to form the record to add to the underlying tblProjectProducts using
Insert Into... query

I'm not meaning to misuse Access.

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
"Robert" <ro*******@bigpond.com> wrote in
news:c%*******************@news-server.bigpond.net.au:
Sorry new at access, am using a currentdb.execute "insert
into...statement for new records being added. Have product number
(unique identifier) in the tag property of each control which
allows grouping controls into records. Just not sure on how to
read through all the controls of the nested subform and group them
into records using the tag property including for each record
specifyig which control is to be saved to which feld before using
the insert into query.


I, for one, am not going to try to puzzle out an answer to your
question, since you're quite obviously simply mis-using Access, and
avoiding all the features it provides for accomplishing the tasks at
hand.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Jan 2 '06 #14
Bri,

Thanks.

Have included temp table and array in options for converting from the
Optionbuttons & Checkboxes entries/combinations.

Am now trying to determine best way to loop through optionbutton, checkbox
and textbox controls on the nested
subform to form the record (either directly or via a temp table or array) to
add to the underlying tblProjectProducts using
Insert Into... query

"Bri" <no*@here.com> wrote in message news:aqfuf.9354$tl.2103@pd7tw3no...

Robert wrote:
Allen,

Started with a bound subform but switched to using an unbound subform for
better error control, more control over saving records (single button on
parent subform) and for more user friendly data capture (option buttons &
check boxes rather than combo boxes on continuous subform).

Parent subform has "Project" (one) details and nested subform has (many)
"Products" that can be selected via option buttons or check boxes and
layout of the form is not on a one record (Product) per row basis.


I understand your issue and I have a different approach to solving it. I
create a temp table (in a temp database - linked) that I then use in the
bound subform. The Save button then does an Update query that moves the
data from the temp table to the permanent table. This gives me the
flexablility that a bound form gives with the ability to save all the
changes or none of the changes (like a transaction commit or rollback but
in a form rather than in code).

--
Bri

Jan 2 '06 #15
I'm ecstatic that David has offered to help you as I still can't quite
understand what you are doing. Now I can sleep without guilt!

Jan 3 '06 #16
"Robert" <ro*******@bigpond.com> wrote in
news:RC*******************@news-server.bigpond.net.au:
I'm not meaning to misuse Access.


You may not be meaning to do so, but you are.

You've rejected all the tools Access gives you to make editing data
easy.

You're missing one important principle that I believe in: continuous
forms should not be editable. If, instead, you use a linked single
form to display and edit the data for a record selected in the bound
display-only continuous form, you can get the UI you want and have
all the control you need over when the detail record is
created/saved (bound or unbound).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jan 3 '06 #17
David,

Probably a case of a picture worth a thousand words as I think from below
there is some misunderstanding.

Not editing existing records just adding new records.

Parent subform is unbound and is single form (used to add a project)

Nested subform is unbound and is single form (used to add many products for
a single project).

Nested subform is a grid of option buttons, check boxes (each one a product
choice) which the user selects/deselects to to include/exclude products for
the project

Using nested subform as there are five sets of product groups i.e. five
nested subforms, only one subform visible at a time depending on the project
type selected on the parent subform

Suggestions I was seeking originally was on best method (code) to loop
through all the controls on the nested subform and convert these to (many)
records to be saved to the ProjectProducts table relationally for the (one)
project being entered on the parent subform. Primary key for the
ProjectProducts table is (ProjectNr, ProductNr).

Don't think any of the above is mis-use of Access but am certainly not an
expert and if there is a better way of doing this the access way then am
keen to learn so I posted the question. I probably confused the issue by
using the term "simulating continuous form" but by that I meant that the
multiple products were saved to the relational ProjectProducts table as if
they were entered via a continous (date entry) form.


"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
"Robert" <ro*******@bigpond.com> wrote in
news:RC*******************@news-server.bigpond.net.au:
I'm not meaning to misuse Access.


You may not be meaning to do so, but you are.

You've rejected all the tools Access gives you to make editing data
easy.

You're missing one important principle that I believe in: continuous
forms should not be editable. If, instead, you use a linked single
form to display and edit the data for a record selected in the bound
display-only continuous form, you can get the UI you want and have
all the control you need over when the detail record is
created/saved (bound or unbound).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Jan 4 '06 #18
Bri

Lyle Fairfield wrote:
Bri wrote:
Lyle Fairfield wrote:
Double EWWWWWWWWWWWW!

So, you have an alternative solution?

--
Bri

Self-immolation!


That's just as useless a response as your original one. And this is from
someone who regularly asks others to post example code to prove their
points.

Unless, of course, you meant to have a burning affair with a fiery red
head? Oh, Wait! You said 'self'... Do you have/had red hair?

--
Bri

Jan 4 '06 #19
Bri
Oops, this got posted against the wrong thread.

--
Bri

Jan 4 '06 #20
"Robert" <ro*******@bigpond.com> wrote in
news:%%********************@news-server.bigpond.net.au:
Probably a case of a picture worth a thousand words as I think
from below there is some misunderstanding.


The point is that I didn't read your explanation because you're
advocating creating a form that is unbound that replicates the look
of a continuous form.

THAT IS A WASTE OF TIME.

I don't *care* how you think you're going to accomplish it -- the
task is unnecessary and an attempt to work against all the tools
that Access provides to make data entry and editing easy.

Indeed, if you're only *adding* records, it makes my suggestion even
*more* appropriate. You'd have a collection of unbound controls on
your parent form where you'd input the data for the new record, then
a SAVE button that would write those values to the child table and
requery the BOUND subform.

One of your objections was the problematic nature of certain
controls on continuous forms. That is *onl* relevant if you're
editing the records in the continuous form. For instance, if you use
a filtered combo box to enter data into a particular field (where
the combo box is filtered based on data in the record you're
editing), that is irrelevant if the continuous form is not being
used to edit -- you just display the data, with no need for the
combo box.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jan 4 '06 #21

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

Similar topics

3
by: Dos Lil | last post by:
I have a unbound field in the subform(for calculating the datediff) which has the control property ==DateDiff("n",,). I have another unbound field in the main form which is for displaying the...
2
by: Simon P | last post by:
Hello group, I'm in desperate need of help. Here goes : I have the following tables : CONTACTS (ContactID, FirstName, LastName, Company, etc.), SHOWS (ShowID, ShowDescription) and SHOWDETAILS...
4
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...
1
by: gavo | last post by:
Hello everyone! Using A2K i have a form(a) with a subform(b) and within the subform there is a continuous subform(c). in the subform (b) there is a command button used to call a public...
4
by: Robert | last post by:
Have main form with an unbound subform1 which is used for data entry. Subform1 has a nested continuous unbound subform (subform2) which is used to enter multiple records related to the record being...
3
by: google | last post by:
I'm developing an application for use within my company in Access 2003. I'm new to '03, the application I did for my former employer was in '97. The two applications have similar functionality...
8
by: Robert | last post by:
I have a form (Worksheet) that works fine by itself. I have now created a seperate form (MainForm) that has a command button in the header and an unbound subform (FormFrame) in the Detail section....
6
by: Greg Strong | last post by:
Hello All, Is is possible to use an ADO recordset to populate an unbound continuous Subform? I've done some Googling without much luck, so this maybe impossible, but let me try to explain...
11
by: jwessner | last post by:
I have a form (Form1) which contains basic Project data and a subform listing the personnel assigned to the Project as a continuous form. Selecting a person on that project and clicking on a command...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.