Results 1 to 13 of 13
  1. #1
    Stretholox is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    24

    Silly Macro Question

    Ok so I've been asked by my work to create an Access database and I've been patching it together the best I can mostly by being carried by people on this forumn. While I've been putting in macros and some customized VBA code I have never fully understood how that works.



    I have a report. Click design view and view code in the design tab of the report design tools. This takes me to a Visual Basic for Applications area where all the macros on that report are shown in VBA code. However, it doesn't appear that I can add to that code by following the rules of the other macros.

    For example, every macro uses

    Private Sub Command159_Click () as the identifier (159 is one of many command numbers that coorespond to macros created on the report). I created a new button (its number is 164) and followed the same format as another macro by copying and pasting its code below the End Sub and replacing the numbers with the new number (164). But Access is giving me an Identification error. I've been able to get around understanding how the VBA module works by creating a macro that is structurally similair to the custom macro I want and converting everything before going through and deleting repetition. Unfortunately, this sometimes creates errors with other macros because of "EVENT PROCEDURE." Thus, recently I have not been able to update any of the custom macros I made on this report.

    I'm getting pretty frustrated with not knowing how VBA code works so if someone could explain the afformentioned problems it would be really helpful. Thank you so much!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Access has macros and it has VBA. Macros and VBA are very different programming tools.

    Macros are not shown in the VBA editor, the VBA editor shows VBA code.

    Select [Event Procedure] in an event on the Properties Sheet and then click the ellipsis (...). This will open the VBA editor to that event procedure. Type code.

    If you want to build a macro then don't select [Event Procedure].
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Stretholox is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    24
    Can I have a button execute a set of VBA code? Can I have macros in the same report where I use VBA code to execute expressions.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Certainly buttons can execute VBA code or macros. That's what events do. Buttons have events. The one most commonly used is Click.

    Macros and VBA can both be used in the same object. What expressions do you want to execute?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    you have manualy created the vba procedure, it needs to be associated to an onclick event to work correctly. on a form you have on click events, select one and then select vode and you will see a new procedure with that control name, paste your code into there and it should work.

  6. #6
    Stretholox is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    24
    Quote Originally Posted by trevor40 View Post
    you have manualy created the vba procedure, it needs to be associated to an onclick event to work correctly. on a form you have on click events, select one and then select vode and you will see a new procedure with that control name, paste your code into there and it should work.
    Sorry for not responding for awhile. I've been on break from work since school was off. I tried doing what you just said but when I select something for the on click and then view code the code I am viewing seems to not really correlate to what the the program is actually doing. For example, I have a code to view a specific student's grades. It should work that the user will click the button, the program will ask for confirmation and give an input option for the student's name. The user enters the name. The program opens that particular student's information. Instead it is just openning all student's grades. If I don't save and just let it be "EVENT PROCEDURE" it does what I want. Unfortunately, I don't understand why that is the case. I have the knowledge to create the programs I want but I don't understand how everything integrates. This is what I get for having to piece bits and pieces of functionality together for a project on the spot.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    We need something to analyze. Post code and/or the db. Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Stretholox is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    24
    Here is the Database zip copy. I really do appreciate any help you can give me. This forum is amazing. Try not to laugh at how rudimentary it is. I tried asking my boss to just hire a programmer to make a database in Django but they insisted the freshman with no experience do it. There is no confidential data all of the information is made up for as I am working through the functions of the database before they give me access to confidential FERPA info.
    Attached Files Attached Files

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention. Also, no reserved words as names - Name is a reserved word.

    Why do you have two Competency tables?

    A form can be used to enter/edit data for only one table. The only form in db includes all tables as well as a query in its RecordSource. Is purpose of this form merely to view/find data, not data entry/edit?

    Should give controls meaningful names. Instead of Command35, btnEditComp is easier to comprehend when reading code.

    You have macros and VBA. If you're going to use VBA, why not ALL in VBA. I don't use macros. IMO, VBA is much easier to debug.

    Which button code is relevant to the posted issue?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Stretholox is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    24
    Quote Originally Posted by June7 View Post

    Why do you have two Competency tables?

    A form can be used to enter/edit data for only one table. The only form in db includes all tables as well as a query in its RecordSource. Is purpose of this form merely to view/find data, not data entry/edit?

    Should give controls meaningful names. Instead of Command35, btnEditComp is easier to comprehend when reading code.

    You have macros and VBA. If you're going to use VBA, why not ALL in VBA. I don't use macros. IMO, VBA is much easier to debug.

    Which button code is relevant to the posted issue?
    I have two competency tables because they are actually going to be pulled from a clinical database that is isolated by competency type. Additionally in the form AxiumCompilation I want them to be able to go from a student overview to editing a competency for that specific student. Unfortunately I couldn't get the button to work automatically but it isn't the end of the world. Truthfully it might be better to have a Table that comes from both Competency A and Competency B and allow editing to all competencies for a given student at the same time. Unfortunately, since they might update competency A in one time frame and competency B in a different time frame (importing from the clinical database) I'm not sure how that will work. I could use a query that pulls from the tables Comp A and Comp B which are imported when they need to be updated. The system here is really weird and I am aware of the problem that will happen when a user edits information in this database and then imports again from the clinical database (the fact that the data will be overrided). Unfortunately I will have to figure that out later.

    Good point about the meaningful names. Thanks!

    The reason I am using macros and VBA is because I don't have any experience with either so I am simply doing whatever is easiest. In addition, I was running into all sorts of issues with the VBA code I wrote or copied from other sources and it was frustrating. I am hoping that I can use VBA to edit some simple elements of the macros. If I begin to understand the interaction better I might be more inclined to go back and use all VBA.

    The buttons are in the Course Report with View a Specific Student, Edit a Specific Competency, Edit All Competencies and Command 164 which I was using to play around with and try to figure out the answer to this question.

    Thank you for all your help.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If data update will override existing and cause issue with historical records, options are:

    1. save the values that are subject to update into the historical records

    2. a new record for each update and 'deactivate' the previous


    I am not clear on what the issue is. The "View a Specific Student" button on report is working as designed. It opens AxiumCompilation form. What do you want to happen?

    Using a report as a 'menu' is unusual. Why a report and not form?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Stretholox is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    24
    Quote Originally Posted by June7 View Post
    If data update will override existing and cause issue with historical records, options are:

    1. save the values that are subject to update into the historical records

    2. a new record for each update and 'deactivate' the previous


    I am not clear on what the issue is. The "View a Specific Student" button on report is working as designed. It opens AxiumCompilation form. What do you want to happen?

    Using a report as a 'menu' is unusual. Why a report and not form?
    I'm using the report as a menue but it also has its own information. The majority of the time they will only use the information in the Report or Form so I have everything running through it to make it cleaner.

    The problem is the button "view a specific student" is an even procedure that whenever I edit the VBA code it does nothing or breaks. Not because the code is wrong.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I still don't understand the issue. Code can certainly be edited. What edit do you want?

    Did you understand the instructions in post 2 and post 5 that describe how to associate VBA procedure with event property?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-19-2013, 10:29 AM
  2. Replies: 3
    Last Post: 01-20-2013, 01:14 AM
  3. Macro Question Please Help
    By bmschaeffer in forum Access
    Replies: 7
    Last Post: 10-14-2011, 06:20 PM
  4. Silly error on a simple function call - help
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 5
    Last Post: 06-10-2011, 01:23 PM
  5. Silly question
    By HelenP in forum Forms
    Replies: 1
    Last Post: 11-23-2010, 11:25 AM

Posting Permissions

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