loop through array of controls in detail section of a form

jmj2015
jmj2015 used Ask the Experts™
on
I am using MS Access 2002. I have a form with a record source that returns several records. The detail section of the form has multiple controls corresponding to the columns in the recordset: item number, quantity, etc. Since the recordset contains multiple records, when I open the form I see a list of controls, or a control arrray.

This is very hard to explain. Here's what my form looks like:
*****************
ItemNum     Qty
123         0
456         0
789         0

AddButton
*****************

ItemNum and Qty values in this form are inside of textbox controls. When the user clicks the Add Button, I need to loop through the control array and add any item numbers that have a quantity >=1 to a dynamic control array on another form. Is this possible? If so how?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I would create based your forms recordsource an append query that checks for a value Qty >= 1
Behind the add button just code:

currentdb.execute ("append query name")

Getting the idea ?

Nic;o)

Author

Commented:
Not sure if that's it, but it reminds me I should specify that the Qty field is updatable in the form, while the ItemNum field is not updatable.

Author

Commented:
Furthermore, the Qty field is an unbound control not part of the recordsource, whereas the ItemNum field is part of the recordsource
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Hi jmj2015,

The fact it can be updated isn't a problem as access will store the data in the table.
The not being a table field is the problem as a query can't see that.

I would change your "a record source that returns several records" into a mekatable query, thus creating a temp table with the ItemNum and Qty. For Qty just add in the select:
select 0 as Qty, ...

Now you can run my proposed query on the temp table.

Clear ?

Nic;o)

Author

Commented:
I think I see where you're going. Sounds promising, except that it appears a Make Table query can't be used as a row source. Access tells me "The query cannot be used as a row source. Perhaps I can run the make table query when the form opens, and have the temp table as my recordsource instead of the actual table?
Commented:
The maketable query is activated in the form's OnOpen event or before you open the form in the code behind the button for opening of the form. Just place the currentdb.execute before the docmd.openform "yourform", thus all data is filled with the latest data.
(assuming it's no multiuser .mdb with a large update frequency).
The (sub) form is based on the created temp table and that can be used for an append query that checks for a value Qty >= 1.

Hmm, hope you still see the "red line"

Nic;o)

Author

Commented:
It worked! Nico you are the man!

Commented:
Glad I could help with directions, proves you're learning fast :)

Success with the application !

Nic;o)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial