recommend reading please. most viable VBA skills

[oldfryeguy]

I learned a bit of SQL in a free class a few years ago. But now with my second database gig I'm hitting VBA hard. What are the most used skills or objects?

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[The_Doc_Man]

Welcome to the forum.

Most used skills and objects?

Most Used Skills:
The ability to step far enough away from the tree to see the forest.
The ability to approach things methodically (as opposed to stepping in and shooting from the hip.)
The ability to recognize that objects have fine structure; that they frequently convey multiple items of information.

Most Used Objects: (Object names highlighted in blue)
Probably Recordsets (which rely on Tables, TableDefs, Queries, and QueryDefs);
Forms (which employ Controls to display limited amounts of data from Recordsets);
Reports (which employ Controls to display lots of data from Recordsets)

Every object has an object type, and the Intellisense feature can help you see & select the properties of your object of interest. Which is why you want to remember to use the proper type of object declaration rather than using generic objects.

 

[Edgar_]

I learned a bit of SQL in a free class a few years ago. But now with my second database gig I'm hitting VBA hard. What are the most used skills or objects?

By far, in VBA, the thing that will give you the most power is debugging with the immediate window, the locals window and playing with intellisense. Practice both getting and setting the things you see in those two and you'll find a world of undocumented knowledge.

Take Doc's suggestion for most used objects. Want to know what a recordset has? declare a recordset variable and see what items intellisense give you. Want to see what data, collections and properties it comes with and how to access them? initialize the variable and inspect it in the locals window. The entire setup can be visualized and you don't have to memorize anything, you get references to everything in the database from there. Got an error? check the locals window and instantly see why you got that error.

EDIT: Confused the local window with the immediate window. The place where you check your variables is the locals window and you get outputs on the immediate window.

 

[CJ_London]

Other considerations-

• put Option Explicit at the top of every module
• To try not to get a single sub/function to do more than one task
• give your controls and variables, subs and functions meaningful but not too long names
• Use indentation properly so code is easy to follow
• Document your code to describe what it suppose to do

my usual method is to write the documentation first ( it can always be tweaked) to describe what each step is required to do
Then the loops/iifs etc indented that are anticipated to complete the step
Finally the code itself

 

[oldfryeguy]

Welcome to the forum.

Most used skills and objects?

Most Used Skills:
The ability to step far enough away from the tree to see the forest.
The ability to approach things methodically (as opposed to stepping in and shooting from the hip.)
The ability to recognize that objects have fine structure; that they frequently convey multiple items of information.

Most Used Objects: (Object names highlighted in blue)
Probably Recordsets (which rely on Tables, TableDefs, Queries, and QueryDefs);
Forms (which employ Controls to display limited amounts of data from Recordsets);
Reports (which employ Controls to display lots of data from Recordsets)

Every object has an object type, and the Intellisense feature can help you see & select the properties of your object of interest. Which is why you want to remember to use the proper type of object declaration rather than using generic objects.

Thanks! In my VBA code editor, the immediate box never displays output of any kind. I googled and read for several hours yesterday, couldn't find a solution. Also, if I 'play button ' - run code, a pop up box asks what macro I would like to run. I was able to run code from the access form where the buttons are located.

By far, in VBA, the thing that will give you the most power is debugging with the immediate window and playing with intellisense. Practice both getting and setting the things you see in those two and you'll find a world of undocumented knowledge.

Take Doc's suggestion for most used objects. Want to know what a recordset has? declare a recordset variable and see what items intellisense give you. Want to see what data, collections and properties it comes with and how to access them? initialize the variable and inspect it in the immediate window. The entire setup can be visualized and you don't have to memorize anything, you get references to everything in the database from there. Got an error? check the immediate window and instantly see why you got that error.

Thanks! In my VBA code editor, the immediate box never displays output of any kind. I googled and read for several hours yesterday, couldn't find a solution. Also, if I 'play button ' - run code, a pop up box asks what macro I would like to run. I was able to run code from the access form where the buttons are located.

 

[oldfryeguy]

Thanks everyone! Late in the day I 'found' record sets. Seemed to address the following but will ask anyway! To me, SQL is sort of easy and explicit. But with VBA I'm having trouble with telling SQL to get variables from a form ( all user work is done on them) what's with this " me." How can I know what going to happen if several forms are opened?

 

[oldfryeguy]

Other considerations-

• put Option Explicit at the top of every module
• To try not to get a single sub/function to do more than one task
• give your controls and variables, subs and functions meaningful but not too long names
• Use indentation properly so code is easy to follow
• Document your code to describe what it suppose to do

my usual method is to write the documentation first ( it can always be tweaked) to describe what each step is required to do
Then the loops/iifs etc indented that are anticipated to complete the step
Finally the code itself

I'm not good at this yet but I do this as best I can. One problem is I crash. I become too detailed and have to research if something will work. Then I start testing code. How do you know -what-exactly to document?

 

[Gasman]

I started with Access 2007 for Dummies book. My bosses bought it for me.

 

[oldfryeguy]

i probably should move to thread now... but i think its this...

 

[jdraw]

Welcome to AWF, oldfryeguy! There are a number of articles in the Database Planning and Design link in my signature on a variety of topics related to database and Access that may be helpful.

 

[The_Doc_Man]

How can I know what going to happen if several forms are opened?

By knowing what will happen if each form is opened separately.

Unless you have some "global" variables somewhere, each form is an entity unto itself. Each form has its own semi-private context. Variables declared in the top of a class module (i.e. before the first sub or function declaration) are public to the code in that class module but private to the rest of the world including other forms. Code in the class module is public within that module but private to the rest of the world.

The complications come when (a) two or more of those "several" forms touch the same data or object and (b) if any of the forms have a timer running behind the scenes while other things are going on and (c) if more than one of the forms has some complex event code that would be active at the same time.

The problem with overlapping event code execution is that you don't know which form's event code activation will occur next, since each form has its own execution context. But if they are written correctly, the ONLY uncertainty is exact timing. If the event code sequences don't try to touch the same things at the same time, there is no ambiguity in the result. Unfortunately, since the forms (being essentially a visual entity) ALL will touch the display, the list of problems must include display effects.

There might be visual confusion in the code-competition case since having multiple active forms might lead to confusion about which form will be on the top of the display stack (visual layering). Also, which form has the control that currently has focus for the application as a whole. If the forms each take action to force themselves "on top" of the visual layers, that could lead to a confusing display. AND because of timer-based routines that can trigger overlapping code execution, those confusing visual effects could be triggered. So timer execution is a special case of overlapping code execution.

 

[The_Doc_Man]

How do you know -what-exactly to document?

Easy question. If you are thinking about something that will or won't affect the application you are building... document it. Document everything including what you DON'T think matters - and WHY it doesn't. Even document your assumptions about why one approach is better than another. It will save you (or support you) in those inevitable second-guessing situations... the late night "what was I thinking?" ruminations that come to you in a dream.

The nice thing about thorough documentation is that if you have to step away for a while, inevitably you will forget something. But if you capture your thoughts - even if you have to distill them down or organize them a bit - then you will be able to pick up the pieces if/when something breaks. And let's face it - code that gets used a lot has good odds of eventually breaking.

The NICER thing about thorough documentation is that it doesn't affect your project's code at all. Databases can become bloated based on what is called "issues in garbage collection." It happens under some circumstances. But documentation bloat doesn't transfer to the code you write. So if you want to write a thin document, fine. It is better than nothing. And if you want to write a computer memoir equivalent to War and Peace, that works too.

 

[NauticalGent]

I learned a bit of SQL in a free class a few years ago. But now with my second database gig I'm hitting VBA hard. What are the most used skills or objects?

Highly recommend watching @sonic8 's Better VBA series. It will get you started and primed for the "hard stuff".

Also, there are 2 books that are my go to references when I just need to read up on certain topics:

Microsoft Access 2010 VBA Programming Inside Out
Access 2002 Desktop Developer's Handbook

Best of luck

 

[Isaac]

By far, in VBA, the thing that will give you the most power is debugging with the immediate window and playing with intellisense. Practice both getting and setting the things you see in those two and you'll find a world of undocumented knowledge.

Take Doc's suggestion for most used objects. Want to know what a recordset has? declare a recordset variable and see what items intellisense give you. Want to see what data, collections and properties it comes with and how to access them? initialize the variable and inspect it in the immediate window. The entire setup can be visualized and you don't have to memorize anything, you get references to everything in the database from there. Got an error? check the immediate window and instantly see why you got that error.

I agree. And the nice thing is that, the same result you crave from intellisense is only encouraging you to do the same thing you ought to be doing anyway - declaring and setting objects at a fairly granular level.

Example from Excel vba, but principle is the same:
- you declare and set ws as a Worksheet, then type ws. Here you get the methods and properties which is nice to be shown. But this also encourages you to do what is likely the best approach in much of your coding anyway, which is to declare and set a worksheet object if you are going to act on a worksheet, rather than typing Thisworkbook.Worksheets("Sheet1"). over and over.

So the two things go in the same direction

 

[Edgar_]

the immediate box never displays output of any kind

That's correct, I was confusing the name of the window I want you to see, the window name is Locals. You see outputs from writing Debug.Print on the immediate window or using this syntax ?variable. You'll see outputs on the Locals window if you get an error and click Debug:

However, you can make that window show you outputs in other ways. Some people like stepping through their code by adding a break and then hitting the F8 key to see what VBA sees with each step. But I personally like to just write Stop right after the line I care to see what I get.

Here's a break:

This is a Stop in action:

 

[Edgar_]

Also, if I 'play button ' - run code, a pop up box asks what macro I would like to run

You were trying to run Class module code, that code only runs when the Class is initialized. The code you write inside a Form is Class module code. If you want to use the Play button to run code, try adding a regular module from the Insert menu. Then add your code there and you'll be able to run it if your cursor is inside a subroutine without parameters.

You can also run Subs from the immediate window. If it's a Subroutine without parameters, just type its name and hit enter. If it has parameters, write it ?MySub(someParam), for example: ?Left("hello",2) and hit enter, it will return "he". But you can also do some programming there if you ever need that, for example, write tomorrow=29 and then hit enter and then write ?tomorrow and hit enter, and it will return 29.

It's your playground, try it.

 

[Isaac]

You were trying to run Class module code, that code only runs when the Class is initialized

or a module with nothing but functions