Working with VBA and where it starts

[dullster]

Isaac wrote me a sample VBA. Where does this language go? Does it go into the query itself or into the sql version. I'm trying to learn where the vba would go?

Solved - Combining IIf Statements

There are various things that could and maybe should be improved about this function, but I'm just doing it fairly quickly to give you the general idea of how to turn something into a VBA function and then use it in a query. The name should probably be changed. Local variables inside the...

www.access-programmers.co.uk

 

[June7]

VBA code goes in a VBA module, not in a query. Do you know how to open the VBA editor? VBA code can be behind forms and reports or in independent modules. I expect the code you reference needs to be in an independent module.

An expression in query can call a VBA Function procedure (not a VBA Sub procedure). Do you know the difference between Function and Sub?

What do you mean by "sql version" - SQL is a query.

 

[CJ_London]

From previous posts you have opened the vba editor to write code triggered by an event. That is in a form module. You need to create a standard module and put the code in there - see the ribbon for creating a new standard module. When you save it give the module a sensible name such as modCalcs and any other calcs you might have can go in the same module

Make sure you put Option Explicit at the top of the module

Say your function is called myCalc(pk as long) as long

Your code will do whatever your code is required to do using pk as a reference and return a long value.

In your query you would put in a column e.g.
Expr1: mycalc(thisPK)

To pass the pk of the field to the function

 

[cheekybuddha]

Where does this language go?

1. Use Alt+F11 to open the code editor.

2. Before doing anything else, you will need to set a setting called "Require Variable Declaration".
2.i In the menu bar of the VBA editor go to 'Tools' and select 'Options'
2.ii In the first tab of the dialog that opens ('Editor'), uncheck 'Auto Syntax Check' and check 'Require Variable Declaration'
2.iii Close the dialog.

3. Next you'll have to get familiar with code modules and the procedures they contain. I'll leave it to others to give you pointers for now.

 

[dullster]

VBA code goes in a VBA module, not in a query. Do you know how to open the VBA editor? VBA code can be behind forms and reports or in independent modules. I expect the code you reference needs to be in an independent module.

An expression in query can call a VBA Function procedure (not a VBA Sub procedure). Do you know the difference between Function and Sub?

What do you mean by "sql version" - SQL is a query.

Ah, yes, I know to how to open a VBA editor. I did not know the difference between a Function and a sub so I googled it. I need a function.

I am new to VBA so I was thinking it was pasted into the sql. When you asked that it dawned on me what I was not getting.

 

[dullster]

From previous posts you have opened the vba editor to write code triggered by an event. That is in a form module. You need to create a standard module and put the code in there - see the ribbon for creating a new standard module. When you save it give the module a sensible name such as modCalcs and any other calcs you might have can go in the same module

Make sure you put Option Explicit at the top of the module

Say your function is called myCalc(pk as long) as long

Your code will do whatever your code is required to do using pk as a reference and return a long value.

In your query you would put in a column e.g.
Expr1: mycalc(thisPK)

To pass the pk of the field to the function

Thank you. This is starting to make sense.

 

[The_Doc_Man]

@dullster - Let's clarify something. Where does VBA code go? Basically, it goes only in a limited number of places as starting points. Why the limit? Because MSACCESS.EXE is the MAIN program. All you can ever do is supply module-based data (variables and structures), functions, and subroutines. The first part of the answer is that VBA code goes in modules, with two flavors of most importance - general modules (usually where you put general utility routines) and class modules (associated with specific actions in forms or reports.) In either case, you put the VBA code in a module. But that only defines the code. How do you get to it?

Obviously, a rhetorical question... Access does things based on "Events" which are standardized points where some critical action happens. The event name usually tells you what the event is all about. For example, Form_Open - meaning the object used to store the specification of the form gets opened; Form_Load - meaning the contents of the form specification get loaded; Form_Current - meaning the moment when the form's current record is determined and used to load up bound controls. Look up "Events" online for a long lost.

On a form or report, when you open the Properties panel, one of the tab options is the Events panel, which lists the places / actions for which you are allowed to provide supplemental code that augments or even changes what the "standard" Access action would be. VBA code "behind" an event is how you customize or automate the behavior of your form or report. Some events are called with the option to CANCEL an event. For example, you can CANCEL the Form_Open event if you determine that the current user shouldn't be using that form. You can CANCEL an update if you don't think the data on the form is accurate. You can CANCEL an Unload if you decide you aren't ready to start closing down the form.

How do you actually activate VBA code elements? Two places where your VBA code becomes significant are at the entry points for events, and for those cases of public functions that can be called during execution of a query. You can click on one of the Event properties to select the option [Event Procedure], which automatically builds a nearly empty shell and opens the VBA page for you. The other method is to build a PUBLIC function that uses VBA code to return a function value. Has to be public for SQL to see VBA code, and has to be a function because SQL code cannot call a true subroutine. It requires a return value, which means "must be a function."

This is a "steep learning curve" topic so I cannot tell you everything there is to know about VBA. But this might help orient you into seeing WHY VBA is useful.