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

RUNSQL Action in Macros and VBA.

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 it is used—in a 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 Types.

**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 result in errors. In Macro, the length of an SQL statement is 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 records 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 the Action Query, you can instruct Microsoft Access to temporarily suppress warning messages during the RunSQL execution. 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 set or reset the 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 suppresses the warning messages when the RunSQL executes the Action Queries in the next step. The third action is another SetWarnings, this time with its parameter set to Yes, which enables System warning messages. This ensures that Microsoft Access can once again handle and report any unexpected errors as they occur.

Right-click to open the 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

Search for Record Macro Action in Access 2007.

Searching for a record in the Form is normally done using the Find (Ctrl+F) method in Microsoft Access. To search an employee's last name in the Employees Form, the Last Name data field must be present in the Form.

This is where the SearchForRecord Macro Action makes the difference, besides other flexible features. You can search for the Employee's Last Name 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 <, >, AND, OR, and BETWEEN for searching and finding 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 the SearchForRecord Macro Action using 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 Form Wizard option.

Right-click to open Large Image in New Tab/Window.
Select the Last Name Textbox and delete it.
We will search and find Employee records using this field without the Last Name 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 logical operators AND and OR, with the search criteria we create in the Macro.
Create two TextBoxes and a Command Button in the Form Footer 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 to fstName.
Before making changes to the Command Button Properties, we must create a Macro with the SearchForRecord Action.
Save the Form as 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 Macro Action Column.
Set Form in the Object Type control, in the property sheet below.
Select frmEmployees from the Object Name drop-down list.
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 from the Last Name field, preferably after skipping a few records at the beginning. Note down the employee first name so that we can cross-check the accuracy of the search operation. 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 value input in the TextBoxes we created on the Footer of the frmEmployees as search criteria. 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 as 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 the 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 the Command Button to search for the Last Name Kotasa (or whatever the last name you have inserted in the criteria) on the Form.
You will see the record change 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 in the Macro to use the values from the lstName and fstName TextBoxes 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 that combines the lstName and fstName TextBox values with the AND logical operator to perform the search on the Last Name and First Name fields on the Form. For this reason, we need to take extra care when constructing the expression to ensure that it works correctly every time. The expression must combine the data field names (Last Name and First Name), the corresponding TextBox values (lstName and fstName), and the AND logical operator.

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 begins with an = sign. The field name Last Name, which contains a space, is enclosed in square brackets followed by the '=' sign to specify an exact value match. The entire segment of the expression is enclosed in double quotation marks. Before the closing double quotation mark, an opening single quotation mark is included because the text value from the lstName text box on the form is concatenated with the expression.

The next segment begins with a double quotation mark, and contains the closing single quotation mark for the first text value, followed by a space and the AND logical operator. This is followed by the First Name field name enclosed in square brackets, a space, an equal sign (=), and an opening single quotation mark for the fstName text value. The segment ends with the closing double quotation mark. The fstName text box reference from the form is concatenated with an ampersand (&), followed by another ampersand to join the closing single quotation mark enclosed within double quotation marks.
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 Unbound TextBoxes in the Footer of the Form.
Click the Command Button to run the macSearch Macro 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 only one of the text boxes (lstName or fstName) or values in both text boxes. If any or both values match 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

Macros and Temporary Variables.

If you are using Microsoft Access 2007 or later, 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 a 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.
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 example demonstrates how to define a temporary variable and reference it in expressions in 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 named macRemoveVar.
Double-click on the macRemoveVar macro to execute it.
Open the Form again to check whether your name still appears in the TextBox.

The text box will be empty, indicating that the variable myName does not exist in memory. The Remove Tempvar 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 requirement here is to find the quantity summary to calculate the percentage of 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 the macro to run, and calculate the total quantity and store the value in the temporary variable TotalQuantity.

We will add a new column in the OrderPercentageQ to calculate 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.

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 up to 255 TempVars variables.

Remove method of TempVars Object:

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

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 variable Name.

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.