how to iterate through subforms on a form

The subform consists of data from a child table and some calculated ''new" values.  If the user presses and update button, I want to go through every subform occurance and change the original values tied to thie child table to the new calculated value.  I cannot see how to do the iteration through the child forms.
(It is access 2000, so some of the new capability won't be there.)
jpopowskiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OdeMonkeyCommented:
If the "new" values are calculated when the subform is loaded and are changed by the Update click, the new values will be displayed when the subform is requeried.
0
jpopowskiAuthor Commented:
The 'new' values are unbound.  I compute them to let the user see what they will be. I only want to update the underlying table if the user selects 'Update'.

At the click, the logic would be
for every subform in the form
  move the unbound values to the bound columns so that the data is saved when the form is exited.

I got as far as this
  For Each tForm In Application.Forms("frmDelHistOrder").Controls("orderlines")
where orderlines is the container that holds the subforms (one for each line).

However, trying to do anything with the control
tform(i).curValue = tform(i).newValue does not work.

I need to know how to access the values in the subform that appears multiple times under orderlines.

Thanks.
0
OdeMonkeyCommented:
I am not sure I have captured what you really want, but this database has a form for orders with a subform for line items.  The extended price is unbound and is calculated as price * qty whenever one or the other is updated.  When the update button is clicked, the curent values are used to update the extended price in the table.

Dan
Jpopowski.mdb
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

OdeMonkeyCommented:
BTW:  It is a little awkward to store a calculated field in a table when all the elements used in its calculationare also present.  I only did this to illustrate.  I failed to think of a better example.

D
0
jpopowskiAuthor Commented:
I couldn't read the database.  However, what I'm really asking is how does one access the subforms in a form (where there are multiple subforms of the same name.)  I may have to punt, set a flag in the master form, and then do 'before update' processing on the line items.It just seems to me that there must be a way to walk through the subforms in visual basic.
0
OdeMonkeyCommented:
The db is Access 2003, the oldest I have access to.  Anyway, I will try to describe what I did.

When you say "access the subforms in a form (where there are multiple subforms of the same name.)", I believe you mean accessing the individual rows displayed in a subform that is in Continuous Form mode.  Each row will have the same controls and the same names as every other. My Main for has three text boxes (Order ID, Customer, and Order Total) and one button (Update).  My subform has Item ID, Order ID, Description, Quantity, Price and Extended Price.  Extended Price is unbound.  Wehn the form loads or when price or quantity are changed, Extended price is recalculated, but is not written to the table, Line Items.  When the update button is clicked, I use a SQL Update query to update the Extended Price field in the table, I use a second query to recalculate the Order Total and set the Total text box to the new value.

Sorry, I don't know of a way to convert to Access 2000 from 2003.  I have made a few changes in this version:  added comments and performed appropriate clean up tasks after finishing ADO processes.

Dan
Jpopowski.mdb
0
OdeMonkeyCommented:
In the spirit of community and believing I have something to offer, I worked for a couple hours to understand the question and to develop a sample database that illustrates a solution.  Unfortunately, the author is unable to open an Access 2003 database to experiment with it. I don't care if the question is deleted, but I do hope he looks at the solution as I have described it  in my last post.

Dan
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jpopowskiAuthor Commented:
I really wanted a way to refer to the repeating subform controls. (form.subform.firstoccurence.control1, form.subform.firstoccurence.control2, form.subform.secondoccurence.control1, form.subform.secondoccurence.control2 and so on)
This solution does seem to take care of the updating piece, but I still don't have an answer to the questionof how to name and access the controls within the subform.  However, I am awarding the points since you so generously spent time on it.
0
OdeMonkeyCommented:
I guess I should have noted at the outset that I have never found a way to reference the records in a form separately, although I do seem to recall trying to do so.  There is no way that I am aware of.
0
jpopowskiAuthor Commented:
Thanks for trying to help--I truly appreciate that you and other experts are there.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.