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.

 

[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.

 

[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.

 

[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.