LEARN MS-ACCESS TIPS AND TRICKS: Macros

Showing posts with label Macros. Show all posts

RUNSQL Action in MACRO and VBA

a.p.r. pillaiOctober 11, 2015Action, Macros, Queries No comments

Introduction.

Microsoft Access beginners are often confused about the difference between the RunSQL action in a Macro and the 'DoCmd.RunSQL' method in Visual Basic for Applications (VBA). Regardless of where you use it—Macro or VBA—you must provide the SQL statement of an Action Query or a Data Definition Query. These are the only types of queries supported.

If you’re unsure which queries fall under these categories, refer to the following list:

Action Query Type.

**Action Query Types**
Query Type	Statement
Append	INSERT INTO
Delete	DELETE
Make-Table	SELECT ... INTO
Update	UPDATE

Data Definition Queries.

**Data-Definition Query Types**
Query Type	Statement
Create a table	CREATE TABLE
Alter a table	ALTER TABLE
Delete a table	DROP TABLE
Create an Index	CREATE INDEX
Delete an Index	DROP INDEX

Using the SQL Statement of any other Query type in the RUNSQL Action will end up in errors. In Macro, the length of an SQL statement can be a maximum of 256 characters or fewer.

The 'DoCmd.RunSql' method of VBA can execute an SQL statement with a maximum length of 32768 characters or less.

Note: You are not allowed to give an existing Query Name as a parameter to this command. But, in VBA, you can load the SQL Statement of a predefined query into a String Variable and use it as the parameter to the DoCmd.RUNSQL command.

Example-1:

Public Function DelRecs()
'Existing Delete Query’s SQL is used in this example
Dim db As Database, qryDef As QueryDef
Dim strSQL As String

'Read and save the SQL statement from 'Query48'
'and load into strSQL string variable
Set db = CurrentDb
Set qryDef = db.QueryDefs("Query48")
strSQL = qryDef.SQL

'Execute the SQL statement after
'disabling warning messages
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

'Using DoCmd.OpenQuery Command to run a Delete Query
DoCmd.SetWarnings False
DoCmd.OpenQuery "Query48", acViewNormal
DoCmd.SetWarnings True

End Function

The RunSQL action in a Macro allows you to modify or delete information across multiple records in one step. Before executing an Action Query, Microsoft Access displays a warning message appropriate to the action (such as update or delete) and waits for the user’s response to either proceed or cancel.

Once you have thoroughly tested and perfected the Action Query, you can instruct Microsoft Access to temporarily suppress warning messages during the execution of the RunSQL action. After the query completes, you should re-enable the warning messages so that Access can continue to alert you about unexpected errors or issues as they occur.

The SetWarnings action in a Macro (or the DoCmd.SetWarnings method in VBA) is used to control system warning messages. This is particularly useful when data processing for a report involves one or more Action Query steps within a Macro.

In the Macro design example shown below, the first action is SetWarnings, with its parameter set to No, which temporarily turns off warning messages while the RunSQL action executes in the next step. The third action is another SetWarnings, this time with its parameter set to Yes, which re-enables system warnings. This ensures that Microsoft Access can once again handle and report any unexpected errors as they occur.

Right-click to open Large Image in New Tab/Window.

Example-2:

Create a Table with the Data-definition SQL

Warning: Double-check that you are not using any existing table name for this sample run.

Public Function DataDefQuery()
Dim strSQL As String

strSQL = "CREATE TABLE Books  (Title TEXT(50) NOT NULL, Author TEXT(50) NOT NULL, PublishDate DATE, Price CURRENCY)"

DoCmd.RunSQL strSQL

End Function

The OpenQuery Action in Macro and DoCmd.OpenQuery Method of VBA uses the name of a predefined Query (of any type) to open it in any of the following Views:

Design View
Datasheet View
Print Preview
Pivot Table
Pivot Chart

Search for Record Macro Action Access2007

a.p.r. pillaiJuly 10, 2013Macros No comments

Introduction.

Searching for a record on the Form is normally done with the help of the Find (Ctrl+F) method of Microsoft Access. For the search operations in the Last Name field of the employee's record, on the Employees Form, it is absolutely necessary that the Last Name data field is present in the Form.

This is where the SearchForRecord Macro Action mainly makes the difference, besides other flexible features. You can search for the Last Name of an employee even when this field is not present in the Form. But the data field must be available on the Record Source (Table/Query) on the Form. The SearchForRecord Macro Action can accept logical comparisons like <, >, AND, OR, and BETWEEN to search and find the required record. But, the Find method accepts only one of the three options, viz. Whole Field, Any Part of Field & Start of Field to search for a record on any of the available fields on the Form.

Prepare for a Test Run.

Let us try out SearchForRecord Macro Action with the help of data from the Employees Table of the Northwind sample database.

Import the Employees Table from the Northwind sample database.
Design the Form frmEmployees in Columnar Format (see the sample image given below). You can do this quickly with the help of the Form Wizard option from the Forms Group under Create Menu.

Right-click to open Large Image in New Tab/Window.
Select the Last Name Textbox and Delete it.
We will try to search and find records using this field, without placing the field on the Form. We will also try the search method with the First Name field on the Form combined with the Last Name field (not on the form) and learn the usage of AND, OR Logical Operations in the search criterion in the Macro we are going to create.
Create two TextBoxes and a Command Button at the Footer of the Form, as shown in the image above.
Change the Child Label Caption value of the first Text Box to Last Name and the Textbox Name Property Value to lstName.
Similarly, change the second Child Label Caption of the second Text Box to First Name and the Textbox Name Property Value to fstName.
Before making changes to the Command Button Properties we must create a Macro with the SearchForRecord Action.
Save the Form with the name frmEmployees and close it.

Creating a Macro.

Select Macro from the Create Menu to open a new Macro design window.

The Sample Macro image is given below:
Right-click to open Large Image in New Tab/Window.
Select SearchForRecord from the drop-down list in the Action Column of the Macro.
Set Form in the Object Type control, in the property sheet below.
Select frmEmployees from the drop-down list of Object Name.
Select First in the Record control.
Type [Last Name] = "Kotasa" in the Where Condition control.

NB: You may open the Employees Table to view and select any record value from the Last Name field, preferably after a few records from the beginning of the records. Note down the first name of the employee also so that we can cross-check the correctness of the record found by the search operation when the record changes on the form. Remember, we have deleted the Last Name Field from the frmEmployees Form.

Note: Initially, we will try this method with simple constant criteria (easier to understand its usage) in the Where Condition control and search for the last name of an employee Kotasa in the Last Name field, not on the Form. After that, we will modify the macro to use the values typed on the TextBoxes, which we have created on the Footer of the frmEmployees, as search criteria. This way it will give us much-needed flexibility in search operations on the Form, by simply changing the search values in the text boxes.
Save the Macro with the macro name - macSearch and close.

The Form Design Change.

Open frmEmployees in Design View.
Click on the Command Button at the Footer of the Form to select it.
Display the Property Sheet (F4), if it is not visible.
Change the Name Property value to cmdRun and change the Caption value to Search For the Record.
Select On Click Event on the Event tab of the Property Sheet and type the macro name macSearch, or select it from the drop-down list.
Save the Form and open it in Normal View. You will see the first record on the form is active now.
Click on the Command Button to search the Last Name Kotasa (or whatever the last name you have inserted in the criteria) on the Form.
You will see the record changes on the Form. Check and confirm that the First Name on the form matches the name you noted down earlier. We will modify the Macro to make it more flexible.
Close the frmEmployees for now.

The Condition Control Settings
Now, we will modify the Where Condition control settings on the Macro to take the values we type on the TextBoxes (with the names: lstName and fstName) on the frmEmployees Form, rather than using constant values in the search criteria, as we did in the earlier example. We must create an expression to take the lstName and fstName TextBox values joined with the AND Logical operator to conduct a search operation in the Last Name and First Name fields on the Form. For this reason, we need to take some extra effort to build the expression correctly so that it works every time. We must join the Data Field names (Last Name, First Name) and Text Box (lstName, fstName) contents combined with the Logical Operator AND in the expression.

Open the macSearch Macro in Design View.
Copy and paste the following expression into the Where Condition control, replacing the existing one.
```
="[Last Name] = '" & [lstName] & "' AND [First Name] = '" & [fstName] & "'"
```
The Search Criteria Expressions.
The expression starts with an = sign, the field name Last Name has a space in the middle and is placed in square brackets, followed by an equal sign for an exact match of value, and the whole segment of the expression is placed in double quotes. Before closing the second double-quote an open single quote is placed because we have joined the text data from the lstName text box on the form.

The next segment of the expression is opening with a double-quote, and a closing single quote for the first text data, followed by a space and the AND logical operator followed by the First Name field name in square brackets, followed by a space, = sign, opening single quote for the fstName text value followed by the closing double-quote of the third segment of the expression. The fstName text box reference, from the Form, is joined with an ampersand followed by an ampersand to join the closing single quote within double quotes.
Save and close the macro.

Since we have used the AND Logical operator both the Last Name and First Name field values should match to find a record on the Form.

Open the Employees Table and note down the last name and first name of a few records on paper and close the Table.
Open the Form frmEmployees.
Type the last name and first name of the first record, you have noted down earlier, into their respective text boxes on the footer of the Form.
Click the Command Button to run the macSearch and find the record on the form that matches both the last name and first name. Remember, the last name field is not there on the form. You may repeat this method with the other record values you noted down earlier if any.
You may modify the macro to change the AND Logical Operator to OR. You may try the macro after entering the search value in any one of the text boxes (lstName or fstName) or values in both text boxes. If any one of the two or both values matches the record, then it will be returned.

The modified expression is given below for reference:
```
="[Last Name] = '" & [lstName] & "' OR [First Name] = '" & [fstName] & "'"
```

Macros and Temporary Variables

a.p.r. pillaiApril 03, 2011Macros, Variables 1 comment

Introduction.

If you are using Microsoft Access 2007 or a later version, there is a useful new feature: the SetTempVar action in Macros, which allows you to define global variables. Once defined, these variables can be used across other macros, event procedures, forms, or reports. The temporary variables remain in memory until you explicitly clear them using the RemoveTempVar action (for a single variable) or the RemoveAllTempVars action (to clear all). All temporary variables are automatically removed from memory when you close the database.

The TempVar Usage in Macros.

Let us try a quick example to understand the usage in macro:

Select Macros from the Create Menu.
Select SetTempVar Action in the first row.
Type myName in the Name argument.
Type the expression Inputbox(“Type your Name”) in the expression argument.
Save the Macro with a name (say macDefineVar).
Right-click on the macro and select Run from the shortcut menu (or Double-click) to execute the Macro. The InputBox Function will run and prompt for a value to type.
Type your name and click the OK Command Button.

Your name is stored in the Variable myName. We have used the Function, InputBox() in the expression argument. You can use constant values, functions, or expressions to assign values to the variable myName.
Open a new form in the design view.
Insert a Text Box in the details section of the Form.
Type the expression =Tempvars!myName in the Control Source property.
Change the form from Design view to Form View.
Your name will now appear in the text box. The above example demonstrates how to define a temporary variable and reference it in expressions on a form. Next, let us see how to remove this variable from memory.
Close the Form.
Select Macro from the Create menu to open up a new macro in the design view.
Select RemoveTempvar from the Action list.
Type myName in the Name parameter.
Save the macro with the name macRemoveVar.
Double-click on the macRemoveVar macro to execute it.
Open the form again to check whether your name still appears in the text box on the form or not.

The text box will be empty, indicating that the variable myName does not exist in memory. The RemoveTempvar action needs a variable name as a parameter.

TempVar Usage in Query.

Let us take this a step further and build something more practical for real-world scenarios. This time, we will calculate the order-wise percentage based on the total Order quantity. We explored a similar problem earlier in the blog post Percentage in Total_Query.

The key requirement here is to obtain the sum of all order quantities to calculate the percentage for each individual order. In the earlier example, we accomplished this by creating a separate query to calculate the total order quantity and then linking it with a second query, grouped by Order Number, to compute the percentages.

Here we will initialize a Temporary Variable with the sum of Quantity and use the Variable name in the percentage calculation expression.

Import the Order Details table from the Northwind sample database.
Select Query Design from the Create menu; don't select any table or query from the displayed list.

Change the Query in SQL view; copy and paste the following SQL string and save the Query with the name OrderPercentageQ:

SELECT [Order Details].OrderID, First([Order Details].UnitPrice) AS UnitPrice, Sum([Order Details].Quantity) AS Quantity
FROM [Order Details]
GROUP BY [Order Details].OrderID;

Open the macro macDefineVar in the design view.
Change the variable name, myName, to TotalQuantity (myName variable will remain in memory).
Change the expression InputBox(“Enter your Name”) to DSum(“Quantity”,”[Order Details]”). Do not add the = symbol at the beginning of the expression. Save the macro with the change.
Double-click on the macro to run and calculate the total quantity and store the value in the temporary variable TotalQuantity.

We will modify the OrderPercentageQ with the addition of a new column that calculates the order-wise percentage of total orders.
Open a new Query in SQL View.

Copy and paste the following SQL String into the SQL editing window of the new Query and save it with the name OrderPercentageQ2:

SELECT [Order Details].OrderID, First([Order Details].UnitPrice) AS UnitPrice, Sum([Order Details].Quantity) AS Qty, Sum([quantity])/[tempvars]![totalQuantity]*100 AS Percentage
FROM [Order Details]
GROUP BY [Order Details].OrderID;

Open the Query in Design View and check how we have written the expression in the last column to calculate the percentage using the temporary variable [tempvars]![totalQuantity].
Change the Query View into Datasheet View to display the Order-wise percentage of Total Quantity.

The TempVar Usage in VBA.

We can work with the Temporary Variable (Tempvars Object) in VBA.

With the Add method, we can define a Temporary Variable and assign an initial value to it.

Add() method of TempVars Object:

Syntax: TempVars.Add "Variable Name", "Initial Value"

Example-1: TempVars.Add "TotalQuantity", DSum("Quantity", "[Order Details]")

Example-2: TempVars!TotalQuantity = DSum("Quantity", "[Order Details]")

You can define a total of 255 temporary variables in this way.

Remove method of TempVars Object:

The Remove() method erases only one variable and frees the memory occupied by the variable.

Syntax: TempVars.Remove "Variable Name"

Example: TempVars.Remove "TotalQuantity"

RemoveAll method of TempVars Object:

The RemoveAll() method removes all the temporary variables defined with the Add() method.

Syntax: TempVars.RemoveAll

Example: TempVars.RemoveAll

The Count property gives the count of all temporary variables defined in memory:

Example: Debug.Print TempVars.Count returns the count of temporary variables defined in memory

TempVars Item Indexes.

Each temporary variable stored in memory is assigned an index number, starting from 0 up to (total number of variables – 1). A variable can be referenced by its Item index, which allows you to read its Name or Value, or even assign a new value to it. The Debug.Print TempVars.Item(0).Name prints the name of the variable.

SumofQuantity = TempVars.Item(0).Value

You can also use this reference to modify the existing value in the temporary variable.

TempVars.Item(0).Value = TempVars.Item(0).Value + 1

You should not use a subscript beyond the existing number of temporary variables in memory; otherwise, an error will occur. If you have defined 5 variables, then the valid index numbers are 0 to 4.