How to you tell Access 2010 to run VBA code?

[vickimurray]

I am below Novice on this apparently... I started with creating a Macro in Access 2010 which is very repetitious (open a query, update and close... move to next query). I used the Macro builder to make sure I got the right code commands and then converted to VBA because it is my understanding running code is better than running a macro (but I still haven't seen anything that explains to me why that is so). Then I continued writing code with that as a guide, compiled it and ran the code--everything worked fine.
Now is my problem. I would like to have a button that tells it to run the code but cannot figure out how to assign that code--or what is a better way to do this??? I have a form that has some update information in it that is used in the process so I want the user to enter the update information and save it then run the code but I do not want it to automatically run on "save".

[JoeM]

It is not so much that it is "better" to run your code via VBA rather than a Macro, it is just you can make your code more robust and dynamic using VBA (Macros are pretty basic - you can use things like "variables" in them).

In running VBA code, you need to "attach" the code to something. One of the most common things is to create a Command Button that you place on a Form to run your VBA code. You just create a Command Button on the Form, then go to the Properties of that Command Button, go to the "On Click" event property, and select Event Procedure and enter your code there between the shell it creates (or a call to your code you have written elsewhere).

You can also attach code to other "events", like the Opening of a Form, etc. Whatever works best, depending on your needs.

[vickimurray]

I have created the command buttons before but used the Wizard which does not give an option to select an Event Procedure.... should have remembered that the Wizards are not always the way to go. Thanks that solved it. And thanks for the clarification on macro vs. VBA.

[ssanfu]

Macro in Access 2010 which is very repetitious (open a query, update and close... move to next query)

I don't understand why you are opening a query and doing some kind of update. From VBA, you can execute an update query.... don't have to "open" it first.
Maybe post your code for review??? Might be able to help you streamline it....

[vickimurray]

Steve... thanks. The simple reason that I'm opening is that I couldn't figure out how to run the VBA but could figure out how to open and close in the Macros so I am sure it is not the most efficient way to do it. At first I was ending up with over 75 queries left open so ended up doing it this way as an alternative. I just simply do not know the VBA code and started doing it this way because of a time issue to get it done. I posted for help in the "queries" section and got a lot of help from them and saw some code for doing "bulk updating" (my terminology) but could not figure out where I needed to do the edits and, most of all, could not figure out how to run the code. Because of a thread there, I went in and named all of my queries for this report to start with "QAR " so that I could try to do that but gave up at that point. Below is the code that I am repeating. Since I will be doing similar for two other reports that use multiple queries, any assistance is greatly appreciated. I will go do my research and learning to follow this all up but my issue now is just deadlines with little or no knowledge. (BTW, was born in Anchorage. Cool!)

DoCmd.OpenQuery "QAR 5-46 VA Youth cnt", acViewNormal, acEdit
DoCmd.Close acQuery, "QAR 5-46 VA Youth cnt"
DoCmd.OpenQuery "QAR 5-47 VA 90 Days", acViewNormal, acEdit
DoCmd.Close acQuery, "QAR 5-47 VA 90 Days"

Thanks for any streamlining.
Vicki

[JoeM]

If they truly are Update queries, using "OpenQuery" on them actually runs them, but does not physically open the query. So there is no need for the "Close" statement, i.e. you should be able to replace the code you posted above with this:

DoCmd.OpenQuery "QAR 5-46 VA Youth cnt", acViewNormal, acEdit
DoCmd.OpenQuery "QAR 5-47 VA 90 Days", acViewNormal, acEdit

[vickimurray]

Sorry to disagree but that is what I originally had when I ended up with all the queries open on my screen. Just tried it again and the result is all of the queries updated but open

[ssanfu]

Went back and read your previous posts....

How and what are you updating? (75 queries???)

How different are all of the queries?
Would you post the SQL of a couple of the queries?

[vickimurray]

Here is my "below Novice" showing... I do not know what you are asking to see or how to get it when you ask me to post the SQL... call it a maturity (extended) moment.

[JoeM]

Sorry to disagree but that is what I originally had when I ended up with all the queries open on my screen. Just tried it again and the result is all of the queries updated but open

I wonder if that is a bug/feature in 2010. I use 2007, and that does not happen to me. Didn't happen in 2003 either when I used that.
Have you tried to make sure you have the lastest updates/patches to 2010 (I know some of the erlier versions had bugs).

[ssanfu]

Here is my "below Novice" showing... I do not know what you are asking to see or how to get it when you ask me to post the SQL... call it a maturity (extended) moment.

Been there.... still there....

Open the query in design view.
Somewhere on the ribbon (ugh) it will have SQL or SQL view (I have A2K).
It should begin with SELECT or UPDATE
Copy the text
You know the rest

[vickimurray]

Ahh.... found it on the status bar just above the notification area. Ribbons are so much fun.

This is the SQL for one of the queries. RESULTS: this query gives me a list of businesses that signed up for either or both of 2 programs and the dates they signed up:

SELECT [BC Businesses].CompanyName, [BC Businesses].[JF Registered], [BC Businesses].[KT User], [BC Businesses].[JF Reg Date], [BC Businesses].[KT Reg Date]
FROM [BC Businesses]
GROUP BY [BC Businesses].CompanyName, [BC Businesses].[JF Registered], [BC Businesses].[KT User], [BC Businesses].[JF Reg Date], [BC Businesses].[KT Reg Date]
HAVING ((([BC Businesses].[JF Reg Date]) Between [Forms]![BeginDate]![FromDate] And [Forms]![EndDate]![EndDate])) OR ((([BC Businesses].[KT Reg Date]) Between [Forms]![BeginDate]![FromDate] And [Forms]![EndDate]![EndDate]));

Query sample 2. RESULTS: a count of the businesses who completed survey(s):
SELECT DISTINCTROW Sum([Activities].[SURVEY]) AS [Sum Of SURVEY]
FROM Activities
WHERE (((Activities.[Activity Date]) Between [Forms]![BeginDate]![FromDate] And [Forms]![EndDate]![EndDate]));

Query Sample 3. RESULTS: a list of the businesses, how many surveys they completed during the month:
SELECT DISTINCTROW [BC Businesses].CompanyName, Sum(Activities.SURVEY) AS [Sum Of SURVEY], Format$([Activities].[Activity Date],'mmmm yyyy') AS Expr1
FROM [BC Businesses] INNER JOIN Activities ON [BC Businesses].[Company ID] = Activities.[Company ID]
GROUP BY [BC Businesses].CompanyName, Format$([Activities].[Activity Date],'mmmm yyyy'), Activities.[Company ID], Year([Activities].[Activity Date])*12+DatePart('m',[Activities].[Activity Date])-1
HAVING (((Sum(Activities.SURVEY)) Is Not Null) AND ((Format$([Activities].[Activity Date],'mmmm yyyy'))=[Forms]![Month Year Data]![MonthYear]));

The list goes on but many are just counts and then a query that lists them out as detail. I'm sure a LOT of redundancy on those but I have to have a list of each "opportunity" or "service" provided with the count of completions during the month and then after that (somewhat massive) list, there has to be a list of the same information with the detail (who did what). Very extensive because I have 128 "services" plus the "other" category. And then there is data I have to show about the business clients themselves!

I used to do this in Excel and used a lot of pivot tables but the amount of data was too much after a while. I have taught myself how to build the db and have struggle more with the reports than anything else and **FINALLY** just completed a set of reports that satisfies my Board members. For the past year I've been "upgrading" the database, doing queries to grab the data and exporting XL. I'm still searching for that illusive "free time" they keep mentioning to me to try to learn more about Access and the better way to accomplish. Sure do wish I had found this forum before yesterday! I downloaded a paper about "normalizing" my database and will try to get through that. Some of my tables are massive but I do not have redundancy in data (that's about the only thing I'm sure of at this point!)
So much for "quick reply".

[JoeM]

Notice what I said previously:

If they truly are Update queries, using "OpenQuery" on them actually runs them, but does not physically open the query. So there is no need for the "Close" statement, i.e. you should be able to replace the code you posted above with this:

All the sample queries you posted in your previous post start with "SELECT". These are NOT Update queries. Update queries start with "UPDATE". That is why they are remaining open - you are actually opening SELECT queries and not UPDATE queries.

Not really sure why you are opening and closing Select queries. That won't do much of anything (it just returns data to the screen and does not update anything).

[vickimurray]

Thanks, Joe. I have requested that my IT check for updates and/or patches.
I did not understand that difference. I think that the problem comes with my use of the term. When I run the query, it is to update the period that I am selecting data from, it is not updating the data. After the data for the selected period is "updated" then I run the report for that period. The master report contains data from subreports based on the SELECT queries. My guess is that I have managed to chose the most convoluted way to accomplish this?!?!?!

[JoeM]

I think we discovered the problem, and it is not a bug, it is that you were opening Select queries (which do not close on their own).

When I run the query, it is to update the period that I am selecting data from, it is not updating the data. After the data for the selected period is "updated" then I run the report for that period. The master report contains data from subreports based on the SELECT queries.

If your reports/subreports are based on these queries, there is no need to open the queries first (to "refresh" them or anything like that). Opening the report automatically re-runs/refreshes the queries it uses. So opening all those Select queries first is most likely totally unnecessary.