Page 1 of 2 12 LastLast
Results 1 to 15 of 19

How to you tell Access 2010 to run VBA code?

  1. #1
    vickimurray is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2012
    Location
    Oklahoma City, OK
    Posts
    17

    How to you tell Access 2010 to run VBA code?

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

  2. #2
    JoeM is online now Experienced Access User
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    1,794
    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.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    vickimurray is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2012
    Location
    Oklahoma City, OK
    Posts
    17
    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.

  4. #4
    ssanfu is offline VIP
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    3,466
    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....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    vickimurray is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2012
    Location
    Oklahoma City, OK
    Posts
    17
    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

  6. #6
    JoeM is online now Experienced Access User
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    1,794
    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:
    Code:
    DoCmd.OpenQuery "QAR 5-46 VA Youth cnt", acViewNormal, acEdit
    DoCmd.OpenQuery "QAR 5-47 VA 90 Days", acViewNormal, acEdit
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  7. #7
    vickimurray is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2012
    Location
    Oklahoma City, OK
    Posts
    17
    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

  8. #8
    ssanfu is offline VIP
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    3,466
    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?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  9. #9
    vickimurray is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2012
    Location
    Oklahoma City, OK
    Posts
    17
    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.

  10. #10
    JoeM is online now Experienced Access User
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    1,794
    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).
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  11. #11
    ssanfu is offline VIP
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    3,466
    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
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  12. #12
    vickimurray is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2012
    Location
    Oklahoma City, OK
    Posts
    17
    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".

  13. #13
    JoeM is online now Experienced Access User
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    1,794
    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).
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  14. #14
    vickimurray is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2012
    Location
    Oklahoma City, OK
    Posts
    17
    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?!?!?!

  15. #15
    JoeM is online now Experienced Access User
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    1,794
    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.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. HELP>Access 2010 User Login Form Code.
    By zaaimanm in forum Programming
    Replies: 5
    Last Post: 10-22-2012, 06:28 PM
  2. VBA Code for buttons, Access 2010
    By nchesebro in forum Programming
    Replies: 3
    Last Post: 06-29-2011, 08:21 AM
  3. vba code to populat word template in Access 2010
    By graffjohn in forum Programming
    Replies: 2
    Last Post: 06-11-2011, 08:36 PM
  4. Access 2010 - Error Code 438 Problem
    By Lexus350 in forum Access
    Replies: 5
    Last Post: 03-03-2011, 09:46 PM
  5. Code does not work in 64 Bit Access 2010
    By mwolfod in forum Programming
    Replies: 26
    Last Post: 12-12-2010, 02:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums