Results 1 to 13 of 13
  1. #1
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52

    Question Learning VBA

    Hello,
    I have been struggling trying to teach myself Access for my new job. I have learned a lot, however...

    Basically no matter what I do, I can't complete my database without having a better understanding of VBA.
    I know next to nothing about it actually. So when I come across a problem and come to the forum for help I always get hung up on the coding aspect of it.

    I am coming to the conclusion that I need to stop working on my database and learn more about VBA because all I am doing by coming to the forum is putting a bandaid over the problem.
    "I dont actually understand why what that guy on the internet said to do worked, but its good for now."



    I need to know what I am doing and why.

    What are your suggestions as far as where to go to try and learn this stuff?
    I have googled it but all the results are set up so differently, I would just like to know what has worked best for others.

    Thank you!!

  2. #2
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    That's a great big topic

    Over the years there have been some sites with VBA tutorials focused on various parts of the language but, checking my bookmarks, many of them seem to have disappeared.

    In my case, most of what I have learned I've learned by reading forums like this one ad trial and error. Generally I've found that almost any question I have has likely been asked before and searching for the right keywords almost always finds the answer has been asked and answered. This typically gives me more useful search terms to use (inbuilt-function names etc) if I need to learn more. And Access itself includes a help reference that explains what these functions do that can help supplement what you read on a forum.

    Unfortunately, I can't give you a easy to understand tutorial that starts at the basics and progresses up from there. Logic and programming are the same thing. The best I can suggest is to:
    1. Learn what built-in functions exist (e.g., http://www.techonthenet.com/access/functions/index.php) and learn what they do and where they can be run (some are for VBA, some only work in queries etc).
    2. Learn how to construct your own functions and subs, and know the difference between a general code module (where functions and subs are available everywhere inside the database) and a form or report's code module (where functions and subs are typically only available from within the form itself (note that objects on a subform are NOT able to run private code on the parent form).
    3. Look at loads of examples of code trying to address similar problems. Try to determine what tools they're using to solve the problem and do more follow-up search to learn how those specific tools work.
    4. Be aware that some words are reserved for Access to us internally and if you use them in code you can get errors or weird results (https://support.microsoft.com/en-us/kb/286335)
    5. Learn about naming conventions for table, field, and variable names and use them to save yourself grief in the long run. Avoid spaces and special characters in all of your names. Enclose object names in square brackets [] if they could potentially contains spaces etc so that Access knows the space inside the name is not the end of the name.
    You'll obviously need to start with a understanding of the types of operations you will have available to you (variables, if-then-else, switch, with, for-next, do while loops, recordsets, arrays, parameters, etc). You also may need to learn about the existence of reference libraries that can extends the default capabilities of the VBA language. Some code requires that these additional libraries be loaded or you get errors. These are all tools.

    Once you know what tools you have to work with, you then need to figure out how to solve whatever logical problem you're trying to solve. The best tip is to break down the problem into a series of trivial tasks. If you build custom functions, try to make sure that the function only does one thing. It's better to have many small functions than one large complicated one. That way you can easily reuse functions in different parts of your database with less risk of unexpected results. And if you discover a bug in a function, you can fix it in a way that's far less likely to foul up old code that referenced it.

  3. #3
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    Quote Originally Posted by CraigDolphin View Post
    That's a great big topic
    Thank you!!!!

    This all sounds like great advice! I guess for now I will just start with the basics & hit the dictionary!

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    My favorite VBA book is "Microsoft Access 2010 VBA Programming Inside Out". Unfortunately, Microsoft Press does not offer it any more. You might be able to find it on Amazon. I highly recommend the E-Book version.

    Learning about forms, reports, etc. can be done through books like Microsoft Access Inside Out. However, version 2013 is heavy in Web App. You have to download the extra EBook chapters do get any VBA stuff.

    I started to create some video tutorials myself. You may find them helpful. As mentioned, many tutorials are no longer being hosted.
    https://www.accessforums.net/showthr...671#post277671

  5. #5
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    Thank you!!

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Some additional suggestions/recommendations:

    Too many people think that Access is going to build a database for them. Access is software and does nothing more than you tell it.
    Do not jump into Access without some basic knowledge of Database concepts and Systems Analysis.

    For a great into to Databases and Data Modelling see tutorials 1,2, and 4 in this series by Dr. Daniel Soper.
    The series starts here.

    For Systems/Business Analysis there are a number of free youtube videos by BA-Experts. They are great for concepts and approach and offer some humor at the same time. Don't overlook these. Each is about 10-15 minutes long.

    Always work from a problem/opportunity description. (Requirements)
    Construct a data model ( on paper with pencil ) to make sure you understand the "problem/opportunity".
    There are tutorials at RogersAccessLibrary that you can work through to understand tables and relationships to develop a data model that you can test.

    Here are 2 tutorials that you have to work through BUT you will learn. Each tutorial will take about 40-60 minutes.
    Class Info System

    Consolidated Widgets

    You should familiarize yourself with Normalization.

    For VBA Programming, there is a free youtube series by Steve Bishop (over 70 videos).
    The series starts here.

    Good luck.

    If you find this stuff interesting, watch anything on youtube dealing with Relational Database by Dr. Jennifer Widom.
    She is very fast, but excellent for concepts and review. Here's a link.

    Good luck.
    Last edited by orange; 08-24-2016 at 10:35 AM.

  8. #8
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    I would also recommend checking out your local community college. Most will have an introductory class on VBA and you can learn some of the basics while also having a resource you can ask questions.

  9. #9
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    This is a great starting point, having 37 well written, well defined, clearly named chapters...as well as a half dozen complete database examples:

    Microsoft Access 2010 Desktop Databases

    And the first thing you really need to do is to learn the ABCs of Access...literally! Learn the names of the various Objects! What a Form is, a Field is, a Control is...the difference between a Combobox and a Listbox...etc.

    One of the biggest problems we face, here, and on other Access forums, is that many newbies can't ask a clear question simply because they don't know the proper words to use to describe their problems! Sometimes we can guess what is being asked, but I've seen threads where members posted beacoup responses, only to finally discover that what the OP said and what they meant to say was totally unrelated!

    And continue to ask questions, using threads with appropriately titles! Titles such as 'Please Help!' or 'Code Doesn't Work' will often receive no responses at all...while 'Learning VBA' tells everyone pretty much what your question was going to be!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Benlav is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    6
    This guy on youtube is great. https://youtu.be/kogGwRIHH6o

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  12. #12
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    WOW thank you everybody!!!

    I know that the VBA that runs in Excel is different then Access and in Word, etc..
    But I also know that Visual Basic is a programming software on its own, with no ties to a specific application.
    I've been using tutorials for that just to get a feel for how to structure my first code (difference between Private/Public, Sub/Function, End Sub/End With, If/Then etc...).
    The only reason I have been doing this is because I have had such a hard time finding tutorials for Beginner level Access VBA, every thing I have stumbled across assumes you have a basic knowledge of how it works, and I dont.

    It has helped a lot with terminology and learning how objects/controls can interact with each other. But I fear it will all be for nothing since I will only be using Access VBA and not the stand-alone Visual Basic program.

    Should I not be doing this?

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    It depends on the source. I have a series I can recommend if you want to learn Visual Basic. Having said that, the Visual Studio IDE is different from the VBA editor. Also, any decent tutorial on a programming language is going to require understanding of or incorporate Application Architecture.

    For the most part, Access is something that people will refer to as Forms Over Data. If you want to use Access to the extreme, understanding Application Architecture will be a big help. Bottom line, you will need a large amount of time to wrap your head around Access.

    Aside from the GUI and programming, you will still need to understand RDBMS in order to effectively use Access. So, set some short term and also some long term goals.

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

Similar Threads

  1. Learning how to program
    By engnrguy in forum Programming
    Replies: 4
    Last Post: 06-15-2015, 04:30 PM
  2. Suggest me some learning sources
    By shish in forum Access
    Replies: 1
    Last Post: 04-23-2012, 02:16 AM
  3. I'm stumped with learning VBA in Access
    By phineas629 in forum Programming
    Replies: 2
    Last Post: 01-06-2012, 04:57 PM
  4. Learning VB
    By aabh in forum Programming
    Replies: 3
    Last Post: 02-16-2010, 02:37 AM
  5. Learning Access...need help.
    By Banner in forum Queries
    Replies: 1
    Last Post: 10-12-2009, 02:48 AM

Tags for this Thread

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