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

re: Unbound Nested Subform Simulating Continuous Form

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" <robertbas@bigpond.com> wrote in message
news:vj4uf.166652$V7.12611@news-server.bigpond.net.au...[color=blue]
> 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[/color]

re: Unbound Nested Subform Simulating Continuous Form

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" <AllenBrowne@SeeSig.Invalid> wrote in message
news:43b8d450$0$2262$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=blue]
> 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" <robertbas@bigpond.com> wrote in message
> news:vj4uf.166652$V7.12611@news-server.bigpond.net.au...[color=green]
>> 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[/color]
>
>[/color]

re: Unbound Nested Subform Simulating Continuous Form

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?

re: Unbound Nested Subform Simulating Continuous Form

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" <robertbas@bigpond.com> wrote in message
news:1S5uf.166719$V7.162183@news-server.bigpond.net.au...[color=blue]
> 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" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:43b8d450$0$2262$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=green]
>> 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" <robertbas@bigpond.com> wrote in message
>> news:vj4uf.166652$V7.12611@news-server.bigpond.net.au...[color=darkred]
>>> 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.[/color][/color][/color]

re: Unbound Nested Subform Simulating Continuous Form

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" <lylefairfield@aim.com> wrote in message
news:1136192778.890666.177250@o13g2000cwo.googlegr oups.com...[color=blue]
> 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?
>[/color]

re: Unbound Nested Subform Simulating Continuous Form

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.

re: Unbound Nested Subform Simulating Continuous Form

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" <lylefairfield@aim.com> wrote in message
news:1136207655.288203.47770@g49g2000cwa.googlegro ups.com...[color=blue]
> 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.
>[/color]

re: Unbound Nested Subform Simulating Continuous Form

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in
news:43b8fbd2$0$2256$5a62ac22@per-qv1-newsreader-01.iinet.net.au:
[color=blue]
> If you do come up with a better design that the one Access gives
> you for free, please let us know.[/color]

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/

re: Unbound Nested Subform Simulating Continuous Form

"Robert" <robertbas@bigpond.com> wrote in
news:c%7uf.166816$V7.91655@news-server.bigpond.net.au:
[color=blue]
> 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.[/color]

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/

re: Unbound Nested Subform Simulating Continuous Form

Robert wrote:[color=blue]
> 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.[/color]

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

re: Unbound Nested Subform Simulating Continuous Form

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.

re: Unbound Nested Subform Simulating Continuous Form

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" <lylefairfield@aim.com> wrote in message
news:1136231864.601715.319070@g14g2000cwa.googlegr oups.com...[color=blue]
>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.
>[/color]

re: Unbound Nested Subform Simulating Continuous Form

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" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns973F960D57D21f99a49ed1d0c49c5bbb2@127.0.0. 1...[color=blue]
> "Robert" <robertbas@bigpond.com> wrote in
> news:c%7uf.166816$V7.91655@news-server.bigpond.net.au:
>[color=green]
>> 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.[/color]
>
> 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/[/color]

re: Unbound Nested Subform Simulating Continuous Form

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" <not@here.com> wrote in message news:aqfuf.9354$tl.2103@pd7tw3no...[color=blue]
>
> Robert wrote:[color=green]
>> 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.[/color]
>
> 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
>[/color]

re: Unbound Nested Subform Simulating Continuous Form

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!

re: Unbound Nested Subform Simulating Continuous Form

"Robert" <robertbas@bigpond.com> wrote in
news:RChuf.166987$V7.83487@news-server.bigpond.net.au:
[color=blue]
> I'm not meaning to misuse Access.[/color]

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/

re: Unbound Nested Subform Simulating Continuous Form

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" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns973FCEF9CE4EEf99a49ed1d0c49c5bbb2@127.0.0. 1...[color=blue]
> "Robert" <robertbas@bigpond.com> wrote in
> news:RChuf.166987$V7.83487@news-server.bigpond.net.au:
>[color=green]
>> I'm not meaning to misuse Access.[/color]
>
> 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/[/color]

re: Unbound Nested Subform Simulating Continuous Form

Lyle Fairfield wrote:[color=blue]
> Bri wrote:
>[color=green]
>> Lyle Fairfield wrote:
>>[color=darkred]
>>> Double EWWWWWWWWWWWW![/color]
>>
>>
>> So, you have an alternative solution?
>>
>> --
>> Bri[/color]
>
>
> Self-immolation![/color]

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

re: Unbound Nested Subform Simulating Continuous Form

Oops, this got posted against the wrong thread.

--
Bri

re: Unbound Nested Subform Simulating Continuous Form

"Robert" <robertbas@bigpond.com> wrote in
news:%%Luf.169796$V7.142449@news-server.bigpond.net.au:
[color=blue]
> Probably a case of a picture worth a thousand words as I think
> from below there is some misunderstanding.[/color]

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/