Passing Query Result to a Form or List

[birdDBheadache]

I have a form in MS Access where I enter a value (I'll call it Value_A) that is then passed to multiple queries. After the queries run, the results of the final query are exported to a .csv file for import into another program.

To summarize my workflow: 1) I enter a Value_A into a field in Form1, 2) I click a button in Form1 that initiates an event where Value_A is passed to a series of queries (using an On Click Event sub procedure with DoCmd), 3) the results of the final query are exported (within the same sub procedure), and I enter the next Value_A and start again.

I have about 400 values of Value_A to enter. Is there an easy way to pass all the values of Value_A through the subprocedure or to the field in the form? I can query all values of Value_A or put them into a table.

My apologies if my description is somewhat confusing.

[zmbd]

query all values of Value_A or put them into a table

- To make sure I understand, all of the data for "Value_A" are already in the database ?

If this is true:
Then I would take your current code and alter it slightly so that we open a DAO record-set on your field with all of your "Value_A" and then step thru this record-set feeding the returned value into the current code so that the queries are ran.

This could be done in a new form so that your current form need not be altered, or we could even just add a button to the current form that will fire the batch off.

[birdDBheadache]

Yes. That is correct. All the data for Value_A are already in the database. Right now I just query it and then enter it manually (which seems like a waste of time). So stepping through each value is exactly what I need to do.

[zmbd]

I can NOT stress this enough... DO NOT WORK ON THE ORIGINAL FILE nor the PRODUCTION FILE.

You can either use the Operating system or Access to make a copy of the file, add "backup_" to filename,(Using the OS you might need to remove "copy of" if its there).
Make a second copy of the file, remove "copy of" and add "dvlpmnt_"
dvlpmnt_ will be the file you play with... if Access crashes, no big deal, if you trash something close; then, you can use the operating system to copy backup_ and save as the dvlpmnt_ starting from scratch.

This should be easy enough for you to do, depending on your VBA background. I've already given you the basic outline; thus, you need only decide which way to go, new form (if the database is not split AND there are no other values being pulled from the exsiting form - my choice), new command button (if the database is split OR there are other values being pulled from the form), or alter the current command button (not my choice in either case).

[birdDBheadache]

I don't have much VBA background, but I can do fairly basic things. I like the idea of using a new form, but I am flexible. If you can show me an example of how to step through each record and pass it to the query, I think I can pretty easily apply it to my existing database.
Thanks for your help.

[NeoPa]

Access VBA DAO Recordset Loop Using Two Recordsets gives some example recordset processing code. It handles two recordsets so may be more complex than you need, but it should cover that.

[birdDBheadache]

That worked! Thanks! I was even able to use my existing form with only minor tweaking.

[zmbd]

Thnx Neopa!

teach me not to read all of the insight articles... at least I was on the right path!

(^_^)

[NeoPa]

There was nothing amiss from your post Z. I merely added some flesh to what you'd already explained.

PS. Hit me up if you'd like a copy of my Bytes DB which stores links to all the articles I've felt to be important. I can think of no better expert to make use of it as you post so fully and frequently.