Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how to iterate through subforms on a form

Posted on 2008-11-14
10
Medium Priority
?
223 Views
Last Modified: 2013-11-28
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.)
0
Comment
Question by:jpopowski
  • 6
  • 4
10 Comments
 
LVL 4

Expert Comment

by:OdeMonkey
ID: 22965950
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
 

Author Comment

by:jpopowski
ID: 22967367
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
 
LVL 4

Expert Comment

by:OdeMonkey
ID: 22968238
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:OdeMonkey
ID: 22968245
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
 

Author Comment

by:jpopowski
ID: 22968373
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
 
LVL 4

Expert Comment

by:OdeMonkey
ID: 22969226
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
 
LVL 4

Accepted Solution

by:
OdeMonkey earned 750 total points
ID: 22969313
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
 

Author Closing Comment

by:jpopowski
ID: 31517004
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
 
LVL 4

Expert Comment

by:OdeMonkey
ID: 22972363
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
 

Author Comment

by:jpopowski
ID: 22972662
Thanks for trying to help--I truly appreciate that you and other experts are there.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question