2010 VBA "do while" code in a macro

[Cloyce]

In Access 2010, want to run perform a "do while" loop in a macro. I understand VBA is the way to do this. So, I think I want to run a VBA script in a macro.

This Macro runs against a Form that has a SubFrom (MainForm named "Expense ID" & SubForm, "Expense"). Macro is initiated by pressing a button.

I would like help with some VBA code showing how to perform the following:

1. Evaluate a field ("Activity") in the first record of Subform ("Expense") against condition "Is not Null".

2. If condition is true, set value of another field ("Post") in the same SubForm record to "1".

3. Move to next record and repeat steps 1 & 2 until all Subform records are evaluated.

4. Return to macro that initiated the VBA script to perform the next action in the macro.

I am not a VBA programmer. Would anyone be willing to help me? Thanks.

 

[Pat Hartman]

Do NOT do this via forms. If you want to do something to all records of a set, run an update query (preferable) or use a DAO/ADO code loop.

Update yourtable Set Post = 1 Where Activity Is Null;

 

[boblarson]

1. You can do this with Access 2010 macros. You do not need VBA.

2. The missing actions would appear for you if you click the SHOW ALL ACTIONS button on the Ribbon.

3. You shouldn't be storing a value based on another value in the same record. That violates the rules of normalization and puts the integrity of your data at risk. If someone somehow modifies the data through some other means than your form, your data for that field would be garbage. And there wouldn't be a way to know if it happened or not so the best thing to do is not do it and use a QUERY at runtime when you need to get that information.

 

[Cloyce]

Thanks Bob,
Regarding your item 2, please point me to the macro actions one might use to perform do while functionality....

 

[boblarson]

This is for 2007 but it is similar in 2010:
http://www.btabdevelopment.com/ts/default.aspx?PageId=29

(I still would not store that value)

 

[Cloyce]

Thanks again for replying, Bob....

Perhaps I am not being clear. I understood the issue about "Show All Actions". My question is, once all actions are displayed, how do you use them to perform "Do until" functionality?

Thanks for your time.

 

[boblarson]

Thanks again for replying, Bob....

Perhaps I am not being clear. I understood the issue about "Show All Actions". My question is, once all actions are displayed, how do you use them to perform "Do until" functionality?

Thanks for your time.

I don't have 2010 here with me at work so I can't go look at the moment. If someone else chimes in before I can, go ahead. Otherwise I will need to look when I get home. I know you can do loops in the macros in 2010. We were shown those before it was even released.

 

[Cloyce]

Thanks for sticking with me on this Bob....

Yes, if you would please check and let me know at your convenience.

Thanks again.

 

[Pat Hartman]

The update query is more efficient. I'm not sure why Bob dropped macros into the mix. Neither macros nor DAO/ADO code loops are as efficient as update queries. That's why I posted a sample update query and diddn't even bother with a DAO/ADO recordset loop. If what you are doing can be done in a query (and it seems it can based on your description), then you should use a query.

 

[Cloyce]

Thanks for the effort to assist.

Let me run at this another way:

Does anyone have sample code to illustrate a good way to do a form initiated "do loop" routine (in Access 2010)?

Thanks.

 

[Pat Hartman]

Is there some reason you don't want to use an update query?

 

[Cloyce]

Hello Pat,
Thanks for hanging with me. My immediate problem is resolve.

However, for future reference, I would like to know the best way to initiate a do-loop from a form. If an update query is the what you recommend, I would like to see an example.

Thanks.

 

[Pat Hartman]

I posted an example of an update query. Run it with .Execute if you are using DAO or ADO or with DoCmd.RunSQL or DoCmd.RunSQL "sqlstring goes here" or
DoCmd.OpenQuery "querydefname"

The last two have equivalent macro actions but if you have macro questions, it is best to post them in the macros forum. I never go there because I don't use macros except in rare occasions and never for anything that requires logic. Macro logic is simply bizarre to anyone who knows how to program.