Learning VBA

[AishlinnAnne]

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!!

[CraigDolphin]

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.

[AishlinnAnne]

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!

[ItsMe]

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

[AishlinnAnne]

Thank you!!

[ItsMe]

Here are some other resources
Paul
http://www.baldyweb.com/

DataPig
http://www.datapigtechnologies.com/AccessMain.htm

MSDN Access Developer Portal
https://msdn.microsoft.com/en-us/lib.../fp179695.aspx

[orange]

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.

[RayMilhon]

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.

[Missinglinq]

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)>

[Benlav]

This guy on youtube is great. https://youtu.be/kogGwRIHH6o

[orange]

Benlav;

Yes he is. He is Steve Bishop that I mentioned in post #7.

[AishlinnAnne]

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?

[ItsMe]

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.