VBA Developer's Guide: Microsoft Dynamics GP 2010
VBA Developer's Guide: Microsoft Dynamics GP 2010
VBA Developer's Guide: Microsoft Dynamics GP 2010
Limitation of liability This document is provided as-is. Information and views expressed in this document, including
URL and other Internet Web site references, may change without notice. You bear the risk of using
it.
Some examples depicted herein are provided for illustration only and are fictitious. No real
association or connection is intended or should be inferred.
Intellectual property This document does not provide you with any legal rights to any intellectual property in any
Microsoft product.
You may copy and use this document for your internal, reference purposes.
Trademarks Microsoft, Excel, Microsoft Dynamics, Outlook, Visual Basic, Windows, and Windows Vista are
trademarks of the Microsoft group of companies. FairCom and c-tree Plus are trademarks of
FairCom Corporation and are registered in the United States and other countries.
Warranty disclaimer Microsoft Corporation disclaims any warranty regarding the sample code contained in this
documentation, including the warranties of merchantability and fitness for a particular purpose.
License agreement Use of this product is covered by a license agreement provided with the software product. If you
have any questions, please call the Microsoft Dyamics GPCustomer Assistance Department at 800-
456-0025 (in the U.S. or Canada) or +1-701-281-6500.
Changed event............................................................................................................................ 40
Lost focus events ........................................................................................................................ 41
Using window field properties and methods ........................................................................ 43
Working with field values......................................................................................................... 47
Verifying field values................................................................................................................. 51
Window field type reference .................................................................................................... 54
ii V B A D E V E L O P E R S G U I D E
C O N T E N T S
iv V B A D E V E L O P E R S G U I D E
C O N T E N T S
Index............................................................................................................................................... 275
Prerequisites
Keep in mind that VBA is a powerful programming language, and a basic
knowledge of VBA programming concepts is required. If you havent
worked with VBA before, we strongly recommend you review one of the
many books available that discuss programming with VBA.
The remainder of the manual assumes that you are already familiar with
VBAs programming concepts and features, as well as Microsoft Dynamics
GP. It explains how to use your existing VBA knowledge to work with the
VBA capabilities available with the accounting system.
2 V B A D E V E L O P E R S G U I D E
IN TRO DUCT IO N
Product support
Technical support for VBA can be accessed by the following methods:
Symbol Description
The light bulb symbol indicates helpful tips,
shortcuts and suggestions.
Convention Description
Part 1, Using VBA Bold type indicates a part name.
Chapter 1, VBA Quotation marks indicate a chapter name.
Overview
Setting window fields Italicized type indicates a section name.
set 'l_Item' to 1. This font is used for VBA code examples.
RUNTIME.EXE Words in uppercase indicate a file name.
Visual Basic for Acronyms are spelled out the first time theyre
Applications (VBA) used.
TAB or ALT+M Small capital letters indicate a key or a key
sequence.
Whats next
Before you can begin using VBA, be sure to complete the following tasks:
4 V B A D E V E L O P E R S G U I D E
PART 1: USING VBA
Part 1: Using VBA
Use the information in this portion of the documentation to understand
how to begin using VBA with Microsoft Dynamics GP. The following is a
list of the topics discussed:
6 V B A D E V E L O P E R S G U I D E
Chapter 1: VBA Overview
Before you start using VBA, its important that youre familiar with the
VBA components specific to Microsoft Dynamics GP. The following
sections explain each of these in detail:
To open the Visual Basic Editor, point to Customize on the Tools submenu,
and then choose Visual Basic Editor.
To create or edit VBA code, you must have appropriate user privileges. Typically,
this means being part of the Administrators group or the Power Users group on
Windows XP. On Windows Vista, it means launching Microsoft Dynamics GP
with Administrative privileges.
The Visual Basic Editor is similar for each host application, so if youve
used VBA with Microsoft Excel or Word, the Visual Basic Editor in
Microsoft Dynamics GP will be familiar.
8 V B A D E V E L O P E R S G U I D E
C H A P T E R 1 V B A O V E R V I E W
You can view a project using the Visual Basic Project Explorer:
Windows, reports and fields are not automatically part of the project. When
you begin working with VBA, youll selectively add to your project the
window, field and report objects you want to work with. Once added to
your project, you can reference these objects in VBA. In addition, the Visual
Basic Editor lets you add VBA user forms, user-defined procedures and
user-defined class modules to your VBA project.
The VBA Editor lets you develop and maintain a single project at a time. To
work with several different projects, store each project file in a separate
location. When you need to work on a project, copy it to the same location
of the runtime engine, then rename the file name.VBA, where name is the
name of the dictionary the project is based on.
After youve completed a VBA project file, youll distribute it to users. This
file contains your VBA code, the objects youve referenced in your VBA
code, and any VBA user forms you choose to use.
Objects
Applications expose their functionality to VBA through a set of
programmable objects. These objects include windows, reports, grids and
Dynamic User Object Store (DUOS) objects. The relationship each object has
to another object is expressed in a hierarchical object model, shown in the
following illustration:
Window
Window fields
Report
Report fields
Grid
Window fields
UserInfo
DUOSObjects
DUOSObject
DUOSProperties
DUOSproperty
Object
Collection Object
10 V B A D E V E L O P E R S G U I D E
C H A P T E R 1 V B A O V E R V I E W
Windows
Most of the VBA programming youll complete for windows will involve
the following objects.
Object Description
Window Represents an open window.
Window field Represents a field in a window.
Grids
Grids are VBAs definition of a scrolling window. When working with
grids, youll use the following objects:
Object Description
Grid Represents a grid (scrolling window) in an open
window.
Window field Represents a field in a grid.
Reports
When working with reports, youll use the following objects:
Object Description
Report Represents a report.
Report field Represents a field in a report.
User information
When implementing a VBA integration, you may need to retrieve
information about the user currently logged into Microsoft Dynamics GP.
The UserInfo object provides access to this information. Refer to Chapter 17,
UserInfo Object, for more information about retrieving user information.
Object/Collection Description
DUOSObjects collection Contains all user-definable data objects.
DUOSObject object Represents a single data object.
DUOSProperties collection Contains all properties for a data object.
DUOSProperty object Represents a single property for a data object.
Programming model
Each object in the object model has defined methods and properties you can
use in VBA code to manipulate the behavior of the object. Methods and
properties for objects are declared using standard VBA object.method and
object.property syntax. Most objects also have specific events that specify
when associated VBA code executes for the object.
Methods
Methods are actions you can perform for a given object. Methods include
opening and closing a window or moving a field in a window. For example,
the field object uses the Focus method to move the focus to a different field
in the window:
Properties
Properties are characteristics you can retrieve or set for a given object.
Properties include the title of a window, or the value of a field. For example,
you can use the Caption property to change the name of a window or field:
12 V B A D E V E L O P E R S G U I D E
C H A P T E R 1 V B A O V E R V I E W
Events
Youll write the majority of your VBA code within individual event
procedures. Each event procedure executes VBA code at certain times for a
specific object, such as when the user changes the value of a field, clicks a
button, prints a report, or opens a window. You can see these predefined
event procedures in the VBA Code window.
Object list.
Add new data entry fields that store additional data in the Dynamic
User Object Store (DUOS). Refer to Chapter 6, Storing Additional
Data, for more information about using the DUOS.
Online help
After you install and register the Modifier, youll have access to online help
for the Visual Basic Editor, the VBA programming language, and for
objects, methods and properties in Microsoft Dynamics GP. Use either of
the following methods to display specific help topics:
Open the help for Microsoft Dynamics GP. A link to the VBA help can
be found there.
When creating references to other dictionaries, you cant have circular references.
For example, if your Microsoft Dynamics GP VBA project references the SmartList
dictionary, the VBA project for the SmartList dictionary cannot have a reference
back to the Microsoft Dynamics GP project.
14 V B A D E V E L O P E R S G U I D E
Chapter 2: Programming Windows
Programming windows using VBA involves adding a window to your
project, specifying the window events youll use to execute VBA code, and
using window methods and properties to customize window display.
Youll also likely want to customize a window using the windows fields
and scrolling windows (grids). Refer to Chapter 3, Programming Window
Fields, and Chapter 4, Programming Grids, for detailed information
about working with window fields and grids.
Switch to the Visual Basic Editor and display the Project Explorer. It should
look similar to the following:
16 V B A D E V E L O P E R S G U I D E
C H A P T E R 2 P R O G R A M M I N G W I N D O W S
You can continue to switch between the Visual Basic Editor and the main
application to add windows as needed throughout your projects
development. When viewed in the accounting system, each window youve
added to your project will appear with a period (.) at the end of the
windows title. This provides technical support and VBA developers with a
visual cue that the window is part of the VBA project.
Be sure you remove from your project all references to a window after youve
removed it.
The following section, Using window events, explains each type of window
event you can use to execute VBA code. Windows also contain window
fields that you can reference and manipulate through VBA. Refer to
Chapter 3, Programming Window Fields, for more information about
using window fields.
The section titled Using window properties and methods explains the methods
and properties available to procedures in your VBA project.
18 V B A D E V E L O P E R S G U I D E
C H A P T E R 2 P R O G R A M M I N G W I N D O W S
If you use VBA with a window youve modified using the Modifier, you
must set the EventMode property to emModified if you want events to
occur for the modified window. To change the EventMode property for the
window object, use the Visual Basic Properties window.
If you delete a modified window from your forms dictionary (or delete the forms
dictionary altogether), all VBA references to new fields you add to the modified
window will be invalid. If you re-create the modified window, then re-add the new
fields using the Modifier, youll need to re-add those same fields to your VBA
project and re-create any VBA customizations that reference the new fields.
BeforeOpen event
The BeforeOpen event occurs before the Microsoft Dynamics GP code runs
for the window open event. Its useful when you want to set default field
values in the window, while still allowing any Microsoft Dynamics GP
application code to override your defaults. In the following example, the
event procedure sets the Checkbook ID field when the Invoice Batch Entry
window opens. If this window displays an existing record when opened
(such as when opened from a zoom field), Microsoft Dynamics GP
application code overrides this value with the value stored with the record:
This event also includes the OpenVisible parameter. When set to False, this
specifies that the window should open invisibly.
AfterOpen event
The AfterOpen event occurs after the Microsoft Dynamics GP code runs for
the window open event. Its useful for overriding any default values that
were set for window fields. In the following example, the event procedure
marks both check box controls when the Sales Territory Maintenance
window opens. Since Microsoft Dynamics GP application code sets defaults
for these fields when the window opens, using the AfterOpen event
overrides the defaults:
BeforeClose event
The BeforeClose event occurs before Microsoft Dynamics GP application
code runs for the window close event. The primary use for this event is to
cancel the window close if the contents in the window dont meet certain
criteria. The following event procedure for the Invoice Entry window
checks whether the user entered a comment ID for the transaction, and
displays a message dialog. If the user clicks Yes, the procedure cancels the
close using the AbortClose parameter, and the user can enter a comment ID:
20 V B A D E V E L O P E R S G U I D E
C H A P T E R 2 P R O G R A M M I N G W I N D O W S
AfterClose event
The AfterClose event occurs after the Microsoft Dynamics GP application
code runs for the window close event. The AfterClose event is a general-
purpose event you can use to perform any clean-up tasks, such as closing
other windows. For example, the following event procedure runs when the
Customer Maintenance window closes. It closes the Customers And
Prospects lookup window if its open (its IsLoaded property is True):
PromptString
Control1String
Control2String Control3String
You apply a modal dialog event to a window in the same manner as other
window events. However, instead of occurring when the window opens,
closes or activates, the modal dialog event occurs for the window whenever
a modal dialog opens.
BeforeModalDialog event
The VBA BeforeModalDialog event occurs when Microsoft Dynamics GP
opens a modal dialog, but before its displayed. Since the dialog is open, but
not visible, this event allows you to dismiss the dialog by programmatically
answering it for the user. In the following example, if a user enters a non-
existent shipping method in the Customer Maintenance window, Microsoft
Dynamics GP displays an ask dialog asking whether the user wants to add
the shipping method. Normally the user must dismiss the dialog manually,
by clicking Add or Cancel. Instead, the event procedure automatically
answers this dialog and removes a step in the data entry process:
22 V B A D E V E L O P E R S G U I D E
C H A P T E R 2 P R O G R A M M I N G W I N D O W S
The PromptString parameter is the message text, which you can use to filter
which modal dialog you want to respond to. The Answer parameter uses
the constants dcButton1, dcButton2 and dcButton3. When indicated, these
click the first, second or third button in the dialog.
The BeforeModalDialog event is also useful for altering the contents of the
dialog before its displayed, including the modal dialogs message text and
button text. The following event procedure changes the message and button
text:
AfterModalDialog event
The VBA AfterModalDialog event occurs when Microsoft Dynamics GP
opens a modal dialog, but after the user dismisses it. This event allows you
to ascertain how the user responded to the dialog and perform any
additional tasks based on that response.
Refer to Chapter 6, The primary use for the AfterModalDialog event is to keep data in the
Storing Additional accounting system synchronized with data youve stored using the
Data, for more Dynamic User Object Store (DUOS). For instance, if youve created a DUOS
information about object that stores Internet address information for a customer, youll likely
using the Dynamic want to save it when the user saves the corresponding customer record.
User Object Store
(DUOS). In the following example, the user attempts to close a window without
saving a customer record. In this case, Microsoft Dynamics GP will display
a modal dialog asking if the user wants to save the record. If the user clicks
the modal dialogs Save button, the event procedure saves the
corresponding DUOS object:
24 V B A D E V E L O P E R S G U I D E
C H A P T E R 2 P R O G R A M M I N G W I N D O W S
Property/Method Description
Name property Specifies the internal name VBA uses for the window.
Open method Opens a window.
PullFocus method Removes the focus from the window.
Show method Shows a window thats hidden using the Hide method.
Top property Specifies the vertical position of the window (in
pixels).
Visible property Specifies whether the window is visible.Width
propertySpecifies the width of the window (in pixels).
Required property Specifies whether the user entered data in all fields
whose Required property is True.
For instance, when you open the Customer Maintenance window and
display a customer record, you can reference fields in the Customer
Maintenance Options window for that customer. In the following example,
the event procedure stops the user from saving a customer record in the
Customer Maintenance window if the user hasnt entered a currency ID in
the Customer Maintenance Options window:
Use the Modifiers Form Definition window to find out which windows are
part of a form.
26 V B A D E V E L O P E R S G U I D E
C H A P T E R 2 P R O G R A M M I N G W I N D O W S
Activating a window
The VBA Activate method activates a visible, open window, making it the
frontmost window, or expands it if its minimized. The Activate method
also causes the windows activate event to occur. In the following example,
the event procedure activates the Invoice Entry window after closing the
Invoice Batch Entry window:
Private Sub Window_AfterClose()
If InvoiceEntry.Visible = True Then
InvoiceEntry.Activate
End If
End Sub
Hiding windows
The VBA Hide method hides a window youve opened, making it invisible.
The Visible property, when set to False, also makes a window invisible.
While invisible, the window is open (its IsLoaded property is True) and
data in the window is accessible.
28 V B A D E V E L O P E R S G U I D E
C H A P T E R 2 P R O G R A M M I N G W I N D O W S
If you make no modifications to the window using the Modifier, you can set
the EventMode property to emOriginalOnly. This allows VBA events to
occur for the original window only.
Renaming a window
The Name property allows you to change VBAs internal reference to a
window object. Note that this is not the same as the Caption property,
which allows you to change the windows title. If you change the windows
Name property using the Visual Basic Properties window, the name will
change in your project. You should then recompile your project to replace
any references to that window with the new name. Youll find it necessary
to rename a window if its name conflicts with other object names in your
project, such as reserved words in VBA.
Using the mouse, click on each data entry field, push button or list field
youll use in your project. Be sure to click on the field itself, and not the
fields prompt. When youve finished adding fields, point to Customize on
the Tools menu, and choose Add Fields to Visual Basic to deactivate field
selection mode.
Field selection mode also deactivates when you switch to the Visual Basic Editor.
Window fields youve added to a project wont appear in the Visual Basic
Project Explorer like a window, report or grid object. Instead, they will
appear in the Visual Basic Code window for a window object.
32 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
The following illustration shows window field objects for the Receivables
Customer Maintenance window object:
At this point, you can write VBA code for the window field object using a
window field event, or you can reference the window field from other VBA
procedures in your project.
If the field youre referencing is not in the current window, you must
reference the field using a qualified field name. A qualified name explicitly
specifies the location of the field youre referencing. In the following
example, an event procedure for a VBA user form opens the Invoice Batch
Entry window, then clicks a lookup button using the lookup button fields
qualified name:
34 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
When you choose an event, VBA automatically adds the event procedure
syntax to the Code window. You can then write the event procedure using
that event syntax.
There are four basic kinds of field events youll work with in VBA: got focus,
user changed, changed and lost focus. Each event occurs relative to when the
user moves the focus into a field (a got focus event), when the value of a field
changes (a user changed or changed event) or when the user moves the focus
out of a field (a lost focus event).
These events are useful when you want to respond to data the user enters in
a field by performing other operations in the window. For example, the
following AfterUserChanged event procedure runs when the user changes
information in the State field:
Private Sub State_AfterUserChanged()
If State = "NJ" Then
'Default the salesperson and territory
SalespersonID = "MARILYN H."
TerritoryID = "TERRITORY 4"
End If
End Sub
Field events also respond to actions performed by the user, such as clicking
a button. In the following example, a new button (OpenMSWord) added to
a window using the Modifier uses the AfterUserChanged event to open
Microsoft Word:
Each window field event you define occurs either before or after the
Microsoft Dynamics GP code for the field event runs. Therefore, field
events have names like BeforeUserChanged to indicate that the event
runs when the user changed the contents, but before the Microsoft Dynamics
GP application code runs.
For information about The BeforeGotFocus, AfterGotFocus and BeforeUserChanged events are the
setting fields from a only three window field events where VBA allows you to set the value of the
field event procedure, current field (the field whose events are running).
refer to Setting field
values on page 50. The following illustration shows the progression of got focus events:
36 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
If Microsoft Dynamics GP runs application code during the got focus event,
it typically checks the value of the field thats gaining focus, and performs
calculations or sets other field values based on that value. However,
Microsoft Dynamics GP uses this event infrequently to do such operations.
BeforeGotFocus event
The BeforeGotFocus event occurs before the Microsoft Dynamics GP code
for the got focus event. Use this event to set or evaluate the contents of the
field gaining focus, and cancel any further got focus events from running
(the Microsoft Dynamics GP got focus event and the VBA AfterGotFocus
event). When set to True, the CancelLogic parameter will cancel the fields
other got focus events. In this example, the BeforeGotFocus event
procedure cancels the AfterGotFocus event for the Batch ID field (shown in
the description of the AfterGotFocus event below):
AfterGotFocus
The AfterGotFocus event occurs after the Microsoft Dynamics GP code runs
for the got focus event. Use this event to set or evaluate the contents of a
field gaining focus. In the following example, the AfterGotFocus event for
the Batch ID field checks whether the field is empty when the user moves to
it. If it is, the event procedure opens the batch lookup window:
For information about The BeforeGotFocus, AfterGotFocus and BeforeUserChanged events are the
setting fields from a only three window field events where VBA allows you to set the value of the
field event procedure, current field (the field whose events are running).
refer to Setting field
values on page 50. The following illustration shows the progression of user changed events in
an editable field:
User enters information in the Name field and presses the TAB key:
1. VBA BeforeUserChanged event occurs for the Name field.
2. Microsoft Dynamics GP user changed event occurs for the Name field.
3. VBA AfterUserChanged event occurs for the Name field.
Its common for Microsoft Dynamics GP to use the user changed event to
verify the contents of a field when the field changes; however, this isnt
done for all fields.
In fields that toggle, such as push buttons and visual switches, the user
changed event occurs when the user clicks the field. Microsoft Dynamics
GP always executes application code during the user changed event for
these fields.
38 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
In list fields, such as button drop lists and list boxes, user changed events
occur when the user selects an item from the list, or presses the up arrow or
down arrow keys to move to a different list item.
BeforeUserChanged
The BeforeUserChanged event occurs before the Microsoft Dynamics GP
code runs for the user changed event. Use this event to evaluate the value of
the field and bypass, or cancel, any Microsoft Dynamics GP processing that
occurs for the same field.
Use caution when canceling Microsoft Dynamics GP processing for a fields user
changed event. Canceling the user changed event processing inhibits the
accounting systems ability to verify the contents of a field.
AfterUserChanged
The AfterUserChanged event occurs after the Microsoft Dynamics GP code
for the user changed event runs. Use this event to evaluate information the
user entered in a field. The following example uses the AfterUserChanged
event to check the value of the Customer ID field in the Invoice Entry
window, then disable the Trade Discount field:
Changed event
The VBA Changed field event always occurs for a field when its contents
change. This includes:
The most common situation to use the Changed event is when you want to
execute VBA code for window fields that Microsoft Dynamics GP updates.
This occurs most often when the accounting system retrieves a record from
a table, then displays the records contents in window fields. The
AfterUserChanged and BeforeUserChanged events wont occur for these
fields, since the application, not the user, changes the contents of the field.
Exercise caution when using the Changed event. In many cases, Microsoft
Dynamics GP may cause the Changed event to occur numerous times for the same
field even though the value of the field may not visibly change. This will execute the
associated VBA event procedure each time the event occurs.
40 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
Microsoft Dynamics GP rarely uses the lost focus event. When it is used, it
typically locks control fields (fields that control the display of a record).
BeforeLostFocus
The BeforeLostFocus event occurs before the Microsoft Dynamics GP code
for the lost focus event. You can use the BeforeLostFocus event to cancel
any subsequent lost focus events for the field. When set to True, the
CancelLogic parameter cancels the Microsoft Dynamics GP lost focus and
VBA AfterLostFocus events.
When set to True, the KeepFocus parameter allows you keep the focus in the
current field.
AfterLostFocus
The AfterLostFocus event occurs after the Microsoft Dynamics GP code
runs for the lost focus event. You can use the AfterLostFocus event to
evaluate the value of the field losing focus, and perform any additional
operations in the window. The following AfterLostFocus event procedure
checks whether the Payment Terms field is empty. If it is, the procedure
displays a VBA dialog that asks the if the user wants to enter a payment
term. If the user clicks Yes, the procedure opens the Payment Terms Lookup
window:
42 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
In this case, the user can browse information in the window, but not create
new account records or delete existing ones.
You can use VBA to set the value of a field youve locked or disabled using
VBA. However, you cannot set the value of a field locked or disabled by
Microsoft Dynamics GP, nor can you unlock or enable these fields.
Microsoft Dynamics GP disables fields when they dont apply in a given
situation, and lock fields to preserve the integrity of accounting data (such
as document totals).
The Focus method moves the focus to a specified field. This will also
activate the fields BeforeGotFocus and AfterGotFocus events. In the
following example, the Focus method moves the focus to the Check field if
the current customer uses Check as the payment term:
44 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
When working with windows, you cannot move the focus using the
windows BeforeOpen event. Since the window isnt open yet, theres no
place for the focus to be placed. You can, however, move the focus using the
windows AfterOpen event.
The following example hides sales fields when a given user opens the
Salesperson Maintenance window:
You can still return or set the values of fields you make invisible with VBA.
However, you cannot set the value of fields hidden by Microsoft Dynamics
GP, nor can you show these fields.
Renaming fields
The Name property allows you to change the name you use in VBA code to
reference the field. Note that this is not the same as the Caption property,
which allows you to change the fields caption. You can change a fields
name using the Visual Basic Properties window, and the name will change
in your project. You should then recompile your project to replace any
references to that field with the new name.
Youll find it necessary to rename fields if the fields name conflicts with a
reserved word in VBA. For example, the Date field has the same name as a
reserved word in VBA. You may also want to rename fields to make code
more readable.
You cannot set the Required property to False for fields already marked as
required by Microsoft Dynamics GP. These are fields necessary for the
accounting system to store the record properly.
46 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
The same code can also omit the Value property and have the same effect:
BatchID = "DEBITMEMOS"
The majority of the examples in this manual use the abbreviated form of
this syntax. You can choose to use or ignore the Value property in your
own project.
In the following example, if 600 is entered as the first segment in the Cash
Account field, the event procedure locks the maximum amount for checks
with this account:
You can also use the ValueSeg property to set individual segments in a
composite. However, theres little difference in using the Value and
ValueSeg properties to do this. In the following example, the ValueSeg
property is used to set the first and second segments in the Account field.
The following event procedure has the same effect, but uses the Value
property to set the composites value:
48 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
VBA returns all field values as strings. For instance, VBA returns the values
of a currency field as $30.75, a date field as 5/1/00, and an integer field
as 100.
For example, if you compare two integer fields, Quantity1 (value of 70), and
Quantity2 (value of 100), VBA compares the string values of those fields,
not their integer values. VBA uses alphanumeric precedence in comparing
strings, and compares the 7 in 70 to the 1 in 100. Since 7 is greater
than 1, the expression results in 70 being the larger value, which is
incorrect:
If Quantity1 > Quantity2 Then
'Quantity1 (70) in this case is greater than Quantity2 (100)
To avoid this problem, you must convert these values using VBAs numeric
conversion functions prior to performing the comparison. In the following
example, VBAs CInt() function converts the ItemQuantity1 and
ItemQuantity2 fields, resulting in a correct comparison:
The following table shows the VBA functions available for converting
string values to a specific data type:
Function Description
CInt() Converts a Microsoft Dynamics GP string value to an integer.
CDate() Converts a Microsoft Dynamics GP string value to a date.
CCur() Converts a Microsoft Dynamics GP string value to a currency.
CLng() Converts a Microsoft Dynamics GP string value to a long integer.
Each type of field accepts specific values from VBA. For setting data entry
fields, such as string, date and currency fields, you typically use a string
value from VBA:
Other fields, such as list fields, check boxes, push buttons and radio
buttons, accept numeric values:
50 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
When you set the value of a field using VBA, this verification will still
occur, but has different results depending on whether you set the field from
inside the field, or outside the field. The following sections describe
data verification issues in more detail.
When the user moves out of the Shipping Method field, the Microsoft
Dynamics GP code for the user changed event checks whether the fields
data is valid. In this case, it will make sure NEW is an existing shipping
method. Since this is a new shipping method, a dialog will appear asking
whether the user wants to add it.
You cannot set the fields value using events that follow the Microsoft Dynamics
GP user changed event (AfterUserChanged, AfterLostFocus or
BeforeLostFocus events). Microsoft Dynamics GP doesnt have the opportunity
to verify values set in these events, and a VBA error will occur. You can use these
events to read the value of the field.
If you set a field value from outside the field using VBA, and Microsoft
Dynamics GP determines that its value is invalid, the accounting system
will first display its alert dialog. This will be followed by a VBA error
dialog, shown in the following illustration.
52 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
Refer to Appendix A, The VBA error occurs because the accounting system attempts to place the
VBA Errors, for an focus in the field and restore the fields previous value. Since the current
explanation of each field does not have focus, Microsoft Dynamics GP cannot place the focus
VBA error you may and the VBA error results.
encounter.
This type of error will commonly occur when you set an add-on-the-fly
field to a new value from outside the field. The following AfterOpen
event procedure for the Receivables Transaction Entry window sets the
value of an add-on-the-fly field (Shipping Method):
In this case, the accounting system will display a dialog asking whether you
want to add the new shipping method. When you dismiss the dialog, VBA
generates an error, since the system cannot place the focus in the Shipping
Method field.
Whenever possible, set a fields value from inside the field, after its
gained focus, using the fields AfterGotFocus or BeforeGotFocus field
event. If your event procedure sets an invalid value, the accounting sys-
tem will still display a dialog, but VBA wont generate the error:
If you set the value outside the field, before the field has gained
focus, use the Focus method with the setvalue parameter in your event
procedure. The Focus method moves the focus to the field, then sets its
value to the setvalue:
This information can help you understand the type of field youre working
with (string, integer, list box) as well as the fields keyable length, the
format applied to the field, and any static values applied to the field.
Field list
The fields described are listed below. Detailed explanations of each follow.
54 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
Description A button drop list allows a user to select one item from the list. The
following illustration shows a button drop list.
List items
VBA usage VBA uses a numeric value (1 to n) when referencing an item in a button
drop list. In most cases, the value corresponds to the order of the selected
item in the list. You cannot reference the name of an item in the list, nor can
you rearrange, remove or add items to a button drop list using VBA.
Comments In certain instances, the position of an item in a button drop list doesnt
necessarily to its numeric value. This is especially true if the list is sorted in
alphanumeric order. To determine the correct value, select the item in the
list, then view the fields Value property using the Visual Basic Editors
Property sheet.
Example The following event procedure chooses the first item in the Write Letters
button drop list in the Customer Maintenance window.
Check box
Description A check box allows the user to mark or unmark an item. The following
illustration shows a check box.
Events All VBA field events function for a focusable check box field.
VBA usage VBA uses numeric values when referencing a check box field. If 1, the check
box is marked; if 0, the check box is unmarked.
Comments You can change a check box caption using the Caption property. For two or
more check boxes linked to a single caption (such as the Calendar Year and
Fiscal Year check boxes linked to the Maintain History caption), you can
change only the check box group caption, not the captions for individual
check boxes.
Example The following event procedure runs when the Sales Territory Maintenance
window opens. It marks the first check box (Calendar Year) in the Maintain
History group, and unmarks the second check box (Fiscal Year):
56 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
Combo box
Description A combo box allows the user to enter a text item or choose one from the
combo box list. The following illustration shows a combo box.
Caption
Events The BeforeUserChanged and AfterUserChanged events run each time the
user selects an item in the combo box list, or presses the up arrow or down
arrow key to move to a different list item. All other VBA field events
function normally for a focusable combo box field.
VBA usage VBA uses string values for each item in a combo box. When you set the
value of a combo box, VBA sets the value to the combo box field. If the
value doesnt exist in the item list, VBA wont add it to the list. However,
Microsoft Dynamics GP application code typically asks the user if they
want to save a new item in the combo box field when the user dismisses the
window.
Example The following event procedure checks the value of the Budget Year combo
box field in the Budget Maintenance window, then enables or disables the
Display radio group:
Composite
Composite field
Segment
VBA usage You can return the value of a composite using either the Value property or
the ValueSeg property. If you use the Value property, VBA returns a
formatted string value. If you use the ValueSeg property, VBA returns the
unformatted value of the segment you specify.
You can also set the value of a composite using either the Value property or
the ValueSeg property. Use the Value property to set the value of the entire
composite, including any formatting characters, such as dashes. Use the
ValueSeg property to set the value of individual segments of the composite,
without including any formatting. Use the FocusSeg method to move the
focus between segments of a composite field.
Examples The following example uses the Value property to set the Cash Account
composite field to an existing account number:
58 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
The following event procedure uses the ValueSeg property to set the
default value of the first segment in the Cash Account composite field. It
then uses the FocusSeg method to place the focus on the second segment in
the composite:
Currency
Caption
Currency field
Events All VBA field events function for a focusable currency field.
VBA usage VBA uses a formatted string value when referencing a currency field. The
value can have a maximum of 14 characters to the left of a decimal
separator and 5 to the right.
When you set a currency value, specify the location of the decimal
separator; the accounting system automatically provides all other
formatting. When you return a currency value, VBA returns the formatted
string value that appears in the window, including the currency symbol,
thousands separator and the decimal separator.
Example The following VBA code defaults the value of a currency field to a fixed
amount in the Checkbook Maintenance window:
60 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
Date
Description A date field displays a date, in a format specified by your operating system
settings for date and time. The following illustration shows a date field.
Caption
Date field
Events All VBA field events function for a focusable date field.
VBA usage VBA uses a formatted string value when referencing a date field. When you
set a date value, the text value must match the operating system settings for
date and time, such as 12/31/96 (using MM/DD/YY format) or
31-12-96 (using DD-MM-YY format).
Examples In the following example, VBA code sets the value of a date field to a fixed
string value:
In the following example, VBA code combines a string with a date field to
construct a new batch ID in the Invoice Entry window:
Drop-down list
Description A drop-down list field expands to show a list of items, then collapses to
show only the selected item. The following illustration shows a drop-down
list.
Caption
Events The BeforeUserChanged and AfterUserChanged events run each time the
user selects an item in the drop-down list, or presses the up arrow or down
arrow key to move to a different list item. All other VBA field events
function normally for a focusable drop-down list field.
VBA usage VBA uses a numeric value (1 to n) when referencing items in a drop-down
list. In most cases, the value corresponds to the position of the item in the
list. You cannot reference the name of the item in the list, nor can you
rearrange, remove or add items to a drop-down list using VBA.
Example In the following example, an event procedure sets the default position of
the sort list and the Document Type drop-down list fields in the Receivables
Transaction Entry window:
62 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
Integer
Description An integer field displays a number between -32,768 and 32,767. The
following illustration shows an integer field.
Caption
Integer field
VBA usage VBA uses a string value when referencing an integer field. If the integer
field includes a format (such as a percent field), VBA returns the formatted
string value, including the decimal separator and percent sign (such as
3.15%). To set the value of a formatted integer field, only include the
decimal separator (3.15). The accounting system adds any other
formatting characteristics.
Examples In the following example, VBA code sets the Transaction Total integer field
to 50 when the user enters a new batch in the Sales Batch Entry window:
This example sets an integer field (Percent) thats formatted with two
decimal places. It calculates a new commission percent based on
commissioned sales:
List box
Description A list box displays up to 32,767 items in a list, one of which the user can
select. The following illustration shows a list box.
Caption
Events The BeforeUserChanged and AfterUserChanged events run each time the
user selects an item in the list box, or presses the up arrow or down arrow
key to move to a different list item. All other VBA field events function
normally for a focusable list box field.
VBA usage VBA uses a numeric value (1 to n) when referencing list box fields. In most
cases, the value corresponds to the position of an item in the list. You cannot
reference the name of the item in the list, nor can you rearrange, remove or
add items to a list box using VBA.
Comments In certain instances, the position of an item in a list box doesnt necessarily
correspond to its numeric value. This is especially true if the list is sorted in
alphanumeric order. To determine the correct value, select the item in the
list, then view the fields Value property using the Visual Basic Editors
Property sheet.
Example The following example sets the default selection in the Currencies list box
when the Multicurrency Access Setup window opens:
64 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
Description A multi-select list box displays up to 32 items in a list, from which the user
can choose multiple items. The following illustration shows a multi-select
list box.
Caption
Events The BeforeUserChanged and AfterUserChanged events run each time the
user selects an item in the multi-select list box, or presses the up arrow or
down arrow keys to move to a different list item. All other VBA field events
function normally for a focusable multi-select list box field.
VBA usage VBA uses a 32-bit numeric value when referencing the items selected in a
multi-select list box. The numeric value takes into account both the number
of items selected and their position in the list. The following table shows the
position of an item in a multi-select list box, the items corresponding
numeric value, and the calculation that derives the value:
31 1,073,741,824 230
The value of the list is the sum of the selected items numeric values. If you
select items 1, 3 and 4, the value of the multi-select list box is 13 (total = 1 +
4 + 8). An easier way to determine the value of the list is to select multiple
items, then view the fields Value property in the Visual Basic Properties
window.
Examples To select a single item in a multi-select list box, set the multi-select list boxs
value to the items numeric value (noted in the previous table). The
following example selects list item 4 by setting the list boxs value to 8:
To select multiple items in a multi-select list box, add the list items numeric
values together. The following example selects list items 1, 3 and 4 by
setting the field to the sum of the items integer values (1 + 4 + 8 = 13):
66 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
Push button
Push button
VBA usage VBA uses numeric values when referencing push buttons. The value
indicates the state of the push button. The up, or normal state of the
button is 0; the down, or pushed state is 1. Setting the buttons value to 1
in effect pushes the button, and performs the same operation as if the
user clicked the button.
Comments You can change a text push buttons caption using the Caption property.
Examples The following example displays a lookup window when the user moves to
the Batch ID field and the field is empty. To display the lookup window, the
procedure sets the lookup button field to 1:
Radio button
Description A radio button field appears within a radio button group. You can select
only one button in the group. The following illustration shows a radio
button group.
Events When you select a radio button in a radio button group, the
BeforeUserChanged and AfterUserChanged events occur for the entire
group. All other VBA field events occur for the entire radio button group, as
you move the focus to and from the group.
VBA usage VBA uses numeric values when referencing a radio button group. The
numeric value indicates the selected radio button in the group and
corresponds to the order each radio button appears in the tab sequence,
starting with 0 for the first field in the sequence. In the Typical Balance
radio button group pictured above, the Debit field is 0 and the Credit field
is 1.
Comments You can change the radio button groups caption using the
Caption property. You cannot change the captions for individual radio
buttons.
Example The following example selects the Code radio button control in the Method
Of Entry radio button group when the window opens:
68 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
String
Description A string field allows you to enter and view up to 255 text characters. The
following illustration shows a string field.
Caption
String field
Events All VBA field events function for a focusable string field.
VBA usage VBA uses a string value when referencing string fields. If you set the value
of a string field that uses a format (such as a phone number field), include
all formatting characters, including parentheses, static text, dashes and
spaces. When you return the value of a formatted string value, VBA returns
the formatted string value.
Example The following example sets the value of the Batch ID string field when the
user chooses a document type from the Document Type drop-down list in
the Receivables Transaction Entry window:
In the following example, the procedure sets the value of the Phone 1 field
using a formatted string:
Text
Description A text field displays up to 32,000 text characters. The following illustration
shows a text field.
Text field
Events All VBA field events function for a focusable text field.
VBA usage VBA uses a string value when referencing a text field. The string value
cannot exceed 32,000 characters.
Example The following example adds a note to an Invoice record when the customer
purchases over $2,000. The message added to the text field in the Note
window explains that the customer received a 4% trade discount:
70 V B A D E V E L O P E R S G U I D E
C H A P T E R 3 P R O G R A M M I N G W I N D O W F I E L D S
Visual switch
Description A visual switch displays a series of text or picture items. Clicking the field
displays the next item in the series. The following illustration shows a
visual switch field.
Events The BeforeUserChanged and AfterUserChanged events run each time the
user selects an item a visual switch. All other VBA field events function
normally for a focusable visual switch field.
VBA usage VBA uses numeric values when referencing visual switch fields. The
numeric value corresponds to the currently-displayed item in the series,
starting with 1 and incremented by 1. For a visual switch with two pictures
in the series, the first picture in the list has a value of 1 and the second has a
value of 2.
Example The following example expands the scrolling window in the Invoice Entry
window when the window initially opens:
When you expand a grid, each line displays two or more rows of data for
each record. You expand and shrink a grid using the expand and shrink
buttons.
74 V B A D E V E L O P E R S G U I D E
C H A P T E R 4 P R O G R A M M I N G G R I D S
When you switch back to Visual Basic, the Project Explorer will display
both the grid and window object.
Be sure you remove all references to a grid after youve removed it from the project.
Adding fields
Refer to Chapter 3, Fields that appear within a grid use the same properties, methods and
Programming events as fields that appear in a window. The only difference is that the
Window Fields, for VBA references the grids fields within the context of the grid, and
more information references the windows fields within the context of the window.
about working with
window fields.
Click the Expand button if you want to add fields from a line when its expanded.
The expanded line will show two or more rows, each containing multiple fields.
Be sure to select a line in the grid. On the Tools menu, point to Customize,
then choose Add Field To Visual Basic. This will activate field selection
mode, and a selection pointer will appear. The following illustration shows
the selection pointer.
Position the
selection pointer on
the field and click.
Using the mouse, click on the fields youll use in your project. You only
need to add fields from one line to your project. References you make to the
field in your VBA project will be for the line currently focused by the user,
so its not necessary to add the same field from multiple lines in the grid.
When youve finished adding fields, point to Customize on the Tools menu
and choose Add Fields to Visual Basic to deactivate field selection mode.
Field selection mode also deactivates when you switch to the Visual Basic Editor.
76 V B A D E V E L O P E R S G U I D E
C H A P T E R 4 P R O G R A M M I N G G R I D S
Types of grids
Three types of grids can be found in Microsoft Dynamics GP: browse-only,
editable and adds-allowed. Each type has unique characteristics.
Browse-only grids
Browse-only grids allow the user to view information but not change it.
Microsoft Dynamics GP uses browse-only grids in lookup windows.
A browse-only grid
contains non-editable
lines.
Editable grids
Editable grids allow the user to change the contents of the selected line.
Microsoft Dynamics GP uses these types of grids to change multiple
options for one or more lines.
Adds-allowed grids
Adds-allowed grids let the user add additional lines of information to the
grid. The grid stores the new information in a table. Microsoft Dynamics GP
commonly uses adds-allowed grids in transaction entry windows, where
the user can add multiple line items for a transaction.
78 V B A D E V E L O P E R S G U I D E
C H A P T E R 4 P R O G R A M M I N G G R I D S
There are four basic kinds of grid events youll work with in VBA: line got
focus, line changed, line lost focus and line populate events. Events occur when
the user displays a new line in a grid (a line populate event) moves the focus
into a grid line (a line got focus event), when the value of a line changes (a
line changed event), or when the user moves the focus out of a line (a line lost
focus event).
Line got focus events are useful for checking the value of a field in the line
gaining focus. In the following example, the BeforeLineGotFocus event
checks the value of the Item Number field in the Invoice Entry grid. If the
field is empty, the event procedure opens the Items lookup window:
Microsoft Dynamics GP uses the line change event to save data in the line to
a table when the user leaves the line. An example of this is the Invoice Entry
window, where each grid line represents a separate transaction record in
the invoice. As the user moves to a new line, the line change event saves the
invoice line item to a table.
BeforeLineChange
The BeforeLineChange event occurs before Microsoft Dynamics GP code
runs for the line change event. Use this event when you want to check the
value of fields in a line, then cancel the Microsoft Dynamics GP line change
event if specified criteria arent met. The KeepFocus parameter cancels the
line change event that normally saves the contents of the line, and moves
the focus to the field that last had focus in the line.
AfterLineChange
The AfterLineChange event occurs after Microsoft Dynamics GP code runs
for the line change event. Use this event to perform other actions in the
window after the accounting system saves data in the line. For instance,
when the user enters line item information in the Invoice Entry grid, then
moves to the next line item, the following AfterLineChange event
procedure checks the Subtotal field. If the invoice subtotal is greater than
$1000, it assigns a specific batch ID to the transaction:
Private Sub Grid_AfterLineChange()
If CCur(InvoiceEntry.Subtotal) > 1000 Then
'Set the batch ID to an existing batch
InvoiceEntry.BatchID = "BIGSALES"
InvoiceEntry.BatchID.Locked = True
End If
End Sub
80 V B A D E V E L O P E R S G U I D E
C H A P T E R 4 P R O G R A M M I N G G R I D S
The primary reason to use the BeforeLinePopulate event is to filter out any
lines you dont want to display in a grid. For instance, the following event
procedure displays only Illinois customers in the Customers and Prospects
lookup window:
The RejectLine parameter lets you selectively filter records from the grid
when its filled. Limit use of the RejectLine parameter, since it can have an
impact on application performance.
82 V B A D E V E L O P E R S G U I D E
Chapter 5: Programming Reports
Refer to the Report Programming reports using VBA involves adding the report and report
Writer documentation fields to your project, choosing the report events youll use to execute VBA
for additional code, and working with methods and properties to customize report
information about display. Information about programming reports is divided into the
customizing reports. following sections:
Once youve added a report, switch to the Visual Basic Editor and display
the Project Explorer. It should look similar to the following:
Although you created a modified report using Report Writer, this was only
necessary to access the reports layout and create the report object in VBA.
You can determine whether you want VBA code to run for either the
modified or the original version of the report using the reports EventMode
property. See the section titled Using modified or original reports on page 85
for more information about the EventMode property.
If the field is not a table field or a calculated field, the Add Fields To Visual
Basic menu item will appear disabled. To add several fields at once, hold
down the SHIFT key and select the fields, then choose Add Fields To Visual
Basic.
84 V B A D E V E L O P E R S G U I D E
C H A P T E R 5 P R O G R AM M I N G R E P O R T S
After youve added a report field to a project, you can reference it only from
within a report event procedure. Report fields, unlike window fields, have
no associated field events. Therefore, you cannot write VBA code
specifically for a report field.
Report events
appear in the
Procedure list.
There are two kinds of report events youll work with: start/end events and
band events. A Start event always occurs at the beginning of a report and an
End event always occurs at the end of a report. Band events occur when a
particular section of a report, or a band, prints.
Start/End events
Start/end events occur when the report starts and the report ends. The only
report data you have access to from start/end events are report legends,
which you can set or return in the Start event. To access report fields, you
must use band events.
Start event
For more information The Start event occurs just before the report starts to print, before any data
about using VBA with actually prints. The primary use of the Start event is to set report legends
legends, refer to using the Legend property. Legends are fields whose data is persistent
Working with legend throughout the report, and which must be passed to the report as it starts.
fields on page 92. The following example sets the value of a legend field from the Start event:
You can also use the reports Start event to initialize any module-level
variables youll use for the report. The following examples use a module-
level variable named Count to restrict a report to 10 customer records. The
Start event sets the counter to 0, then the BeforeAH event increments the
counter. After 10 records print, the BeforeAH event cancels the report using
the Cancel method.
86 V B A D E V E L O P E R S G U I D E
C H A P T E R 5 P R O G R AM M I N G R E P O R T S
End event
The End event occurs after a report prints. Use this event to perform any
clean-up activities for the report, launch other applications, or open and
close other windows. For example, the following End event procedure
launches Microsoft Outlook after the RM Customer Report prints:
Private Sub Report_End()
Dim Response As Integer
Dim RetVal As Variant
Response = MsgBox("Do you want to launch MS Outlook?", vbYesNo)
If Response = vbYes Then
RetVal = Shell("C:\Program Files\Microsoft Office\ + _
"Office\Outlook.exe", vbNormalFocus)
End If
End Sub
Band events
The report band A report is composed of several sections called bands. In VBA, each report
explained in this band has a corresponding band event. As a report prints, each band event
section is the same as occurs just before the corresponding data within the band prints. The
the report section following illustration shows the different bands that compose a report:
thats explained in
Report Writer
documentation.
Page header
(BeforePH event)
Report header
(BeforeRH event)
Additional headers
(BeforeAH event)
Body
(BeforeBody event)
Additional footers
(BeforeAF event)
Report
Report footer
footer
(BeforeRF
(BeforeRF event)
event)
Page footer
(BeforePF event)
For more information The primary use of a band event is to return or set the value of a field in a
about setting and corresponding band using the Value property. You can use a band event to
returning report fields, return a value for any report table field or calculated field. You can set a
refer to Working with value only for a user-defined calculated field youve added using the
report fields on Report Writer.
page 92.
In the following illustration, the Customer Name table field and
EMailAddress user-defined calculated field appear within the RM
Customer Reports additional header. In this case, only the BeforeAH event
can reference these fields:
The BeforeAH event procedure can return the value of the Customer Name
table field, and set EMailAddress user-defined calculated field:
88 V B A D E V E L O P E R S G U I D E
C H A P T E R 5 P R O G R AM M I N G R E P O R T S
Each band event includes a SuppressBand parameter. When set to True, the
SuppressBand parameter stops the current instance of the corresponding
band from printing. If a band prints multiple times in a report (such as the
report body), you can conditionally suppress a given instance of the band.
For example, the following BeforeBody procedure for the RM Salesperson
Report suppresses the current instance of the report body if the value of the
Commissioned Sales To Date field is less then $200,000:
If the report uses an accumulator field to calculate report totals or count the
number of bodies in a report, the accumulator field will still sum or count data from
fields in a suppressed band.
BeforePH event
The BeforePH event occurs before items in the page header print. Items in
the page header are placed at the top of every report page. Page number,
date and time fields are commonly placed in this section of a report.
BeforeRH event
The BeforeRH event occurs before items in the report header print. Items in
the report header appear only on the first page of a report. The title of the
report and introductory information are often included in this section. If a
page header is also included on the first page, the report header will appear
after the page header.
BeforeAH event
The BeforeAH event occurs before the reports additional header prints. A
report may have none, one or several additional headers, each indicated on
the report layout by H1, H2 and so on. Each prints when the data in the
field it is based on changes.
If a report uses multiple additional headers, use the BeforeAH events Level
parameter to specify an index corresponding to the number of the
additional header the event occurs for. In the following example, the
BeforeAH event procedure runs for the second additional header on a
report. The procedure also returns the value of the Checkbook ID field in
the second additional header:
If the report has only one additional header, the BeforeAH event occurs
only for that additional header, and you dont need to specify a value for
the Level parameter.
BeforeBody event
The BeforeBody event occurs before each instance of the reports body
prints. Microsoft Dynamics GP uses the report body for the bulk of a report,
which typically is made up of table fields. A report prints the body
repeatedly until all records in the report have printed.
BeforeRF event
The BeforeRF event occurs before the report footer prints. Items in the
report footer appear only on the last page of a report. Summary information
is often included the report footer. If a page footer is also included on the
last page, the report footer will print before the page footer.
BeforePF event
The BeforePF event occurs before the reports page footer prints. Items in
the page footer are placed at the bottom of every report page. The page
footer often includes administrative information, such as the name of the
person running the report.
BeforeAF event
The BeforeAF event occurs before the reports additional footer prints. A
report may have none, one or several additional footers, each indicated on
the report layout by F1, F2 and so on. Each prints when the data in the field
it is based on changes. Microsoft Dynamics GP uses the additional footer to
display summary data, such as a total of all records in the reports body.
If a report uses more than one additional footer, use the BeforeAF events
Level parameter to specify an index corresponding to the number of the
additional header you want the event to occur for. In the following
example, the BeforeAF event procedure runs for the second additional
footer that appears on the report:
90 V B A D E V E L O P E R S G U I D E
C H A P T E R 5 P R O G R AM M I N G R E P O R T S
If the report has only one additional footer, the BeforeAF event occurs only
for that additional footer, and you dont need to specify a value for the Level
parameter.
Canceling a report
You cancel a report using the Cancel method. The following example
cancels a report from the reports BeforeBody event:
If you cancel a report from a reports band event, the report stops at that
band. If you cancel a report from a reports Start event, no data prints for
the report. In all cases, the reports End event will occur for a canceled
report. If the report is printed to the screen, the Cancel method closes the
Screen Output window automatically.
A legend field contains data thats passed to the report at runtime, before it
prints. These fields typically include information about the report, such as
the range of records the user chose for the report. The following illustration
shows legend fields used to show the range of customer records that print
for the RM Customer Report:
For example, to return the value of the Customer Class legend field, the
following Start event procedure includes a reference to the fields index:
Table fields
Table fields are fields whose values derive directly from a Microsoft
Dynamics GP table. You can return only the value of table fields.
92 V B A D E V E L O P E R S G U I D E
C H A P T E R 5 P R O G R AM M I N G R E P O R T S
Accumulator fields
You can return the values of accumulator fields in a report. You can also
set the value of calculated fields you add to the report that function as
accumulator fields. An accumulator field is a table field or calculated field
that performs a specific function in the report, and whose field type is not
DATA. The field type for an accumulator field determines its function. For
instance, an accumulator field can count the number of occurrences of a
field (a COUNT field type), or calculate the sum total of each occurrence of
a numeric field (a SUM field type). Use the Report Writers Report Field
Options window to determine the field type for a selected field.
Field formats
With the exception of composite fields, VBA returns report field values
without any formatting. For instance, VBA returns the phone number (701)
555-0100 Ext. 1772 as 70155501001772. Likewise, VBA returns the currency
value $10.56 as 10.56000.
To format a report field value, use VBAs Format function. The Format
function allows you to define a format string for a specific field type (string
or numeric). The following example uses the Format function to format the
fax number string field:
MsgBox Fax
'Displays 70155501001772
'Use the format function to format the string
Format(Fax, "(@@@) @@@-@@@@ Ext. @@@@")
MsgBox Fax
'Displays (701) 555-0100 Ext. 1772
The following example uses the Visible property to hide the Total Sales
YTD field in the RM Customer Report if the Territory ID field is
TERRITORY 1:
The following example exports data from the Microsoft Dynamics GP Item
Price List to a text file. The procedure declares several module-level
variables to store report data. It then uses VBA events (the BeforeAH and
BeforeBody events) to return field values from their respective bands and
set module-level variables:
94 V B A D E V E L O P E R S G U I D E
C H A P T E R 5 P R O G R AM M I N G R E P O R T S
Before you can work with another applications objects, you must set a reference to
that applications object library using the References dialog box in the Visual Basic
Editor.
96 V B A D E V E L O P E R S G U I D E
Chapter 6: Storing Additional Data
The Dynamic User Object Store (DUOS) lets you use VBA to create, store
and retrieve user-definable data objects. You can use the DUOS to store data
entered in existing Microsoft Dynamics GP fields, new fields added using
the Modifier, or fields entered in a VBA user form. You can then retrieve a
data object from the DUOS and display it in a VBA user form or in a
Microsoft Dynamics GP window. Information about the DUOS is divided
into the following sections:
DUOSObjects
DUOSObject
DUOSProperties
DUOSproperty
Object
Collection Object
The Object ID is a unique value for a data object within its collection.
This ID cannot exceed 60 characters. You can specify the object ID using
the Item property from the DUOSObjects collection.
The Property name is a string that specifies the name of the property for
the object. The property name cannot exceed 30 characters. You can
define several property names for a data object. Each object in a collec-
tion may contain different property names. You can specify the prop-
erty name using the Item property in the DUOSProperties collection.
A data object must have at least one property name to remain persistent in the
DUOS.
98 V B A D E V E L O P E R S G U I D E
C H A P T E R 6 S T O R I N G A DD IT IO N A L DA T A
The Property value is a string that specifies the value of the property.
This value cannot exceed 132 characters. The DUOS stores all property
values as strings. You can specify a property value using the
Value property from the DUOSProperty object.
If two additional properties and property values are added to the ITM001
data object, the accounting system adds two records to this table:
Since Microsoft Dynamics GP stores DUOS data objects in a table, you can
use table maintenance windows to perform routine table maintenance.
The example then uses the collections Item property to assign a unique ID
to the data object (ITM002). If an object with that ID already exists, the Item
property specifies an existing object in the collection with that ID. Finally,
this example creates two new properties for the data object using the
DUOSObjects collections Item property, and assigns values to each:
100 V B A D E V E L O P E R S G U I D E
C H A P T E R 6 S T O R I N G A DD IT IO N A L DA T A
You can also globally update DUOS data objects by looping through each
object in the collection. In this example, the For...Next statement in VBA
checks the Item Color property for each item in the ItemCollection. If the
value is Red, the collections Remove method deletes the object:
102 V B A D E V E L O P E R S G U I D E
C H A P T E R 6 S T O R I N G A DD IT IO N A L DA T A
A URL Address field and Contact E-mail Address field were added to the
Customer Maintenance window with the Modifier. The examples in the
remainder of this section will create a DUOS customer collection and data
objects to store and display information in these fields.
To ensure the object has a unique ID, the ObjectID parameter of the
Item property uses the value of the Customer ID field. The procedure
defines the DUOS data objects property names (URL Address and
Contact E-Mail Address) and sets its property values to the values
entered in the new fields:
104 V B A D E V E L O P E R S G U I D E
C H A P T E R 6 S T O R I N G A DD IT IO N A L DA T A
The user can click the Save button to save the changes, the Discard button
to ignore the changes, or the Cancel button to close the dialog and cancel
the save operation. In this situation, youll need to know when the user
clicks the Save button, and save any changes to the corresponding DUOS
data object. To do this, use a window AfterModalDialog event.
106 V B A D E V E L O P E R S G U I D E
C H A P T E R 6 S T O R I N G A DD IT IO N A L DA T A
Instead of writing VBA code that deletes a DUOS data object when the user
clicks the Delete button, you delete a DUOS data object based on how the
user responds to the delete dialog. This modal dialog appears after the user
clicks the Delete button.
The user can either click Delete to delete the record, or click Cancel and
cancel the operation. Youll want to delete a DUOS object only if the user
clicks Delete in response to the dialog. Similar to saving DUOS objects, you
can accomplish this by using the AfterModalDialog event. The code that is
added to this event will then track whether the user selected the delete
button.
In some situations, theres one other condition youll need to address when
deleting a DUOS object. A second message dialog may appear if the
accounting system was unable to delete the record. For example, an
additional message will be displayed if a customer record has any
outstanding transactions corresponding to it.
The second message dialog needs to be handled differently than the delete
dialog. This is because theres no AfterModalDialog event available for the
secondary message. Instead, youll need to handle the message dialog as a
separate Microsoft Dynamics GP window and add it to your VBA project.
Once added, you use VBA code in the windows AfterOpen event to
ascertain whether the message prevented the record from being deleted.
The following series of examples show how to do this for DUOS data
objects stored with customer information.
Notice that there are three different variables (WanttoDelete, OktoDelete and
CustIDtoDelete) used throughout these examples. These are declared as
public variables and then initialized and set in the appropriate events:
108 V B A D E V E L O P E R S G U I D E
C H A P T E R 6 S T O R I N G A DD IT IO N A L DA T A
In the following example, the event procedure runs when the user prints
the RM Customer Report. It updates two calculated string fields on the
report (DUOSCustomerWebAddress and DUOSCustomerEMailAddress)
with the data object in the customer collection. Since the new calculated
fields are in the reports additional header, this procedure uses the
BeforeAH event to set the field values before data in the additional header
prints.
110 V B A D E V E L O P E R S G U I D E
C H A P T E R 6 S T O R I N G A DD IT IO N A L DA T A
Note that the RM Customer Report uses a field named Customer Number.
This is the same field as the Customer ID field referenced in the Customer
Maintenance window:
Enabling VBA
References to external components
Distributing project files
Package files
Package file import/export issues
Enabling VBA
To use VBA, an installation of Microsoft Dynamics GP must have the
appropriate registration keys. These registration keys must provide access
to one of the following:
If you will be distributing other components with your VBA customization, be sure
you arent violating the license agreement by distributing those components.
Watch for hidden references, such as those created when you add a
control to the Visual Basic toolbox.
Also, distributing an entire VBA project doesnt allow you to package any
modified forms or modified reports that may be part of your customization.
For all these reasons, we recommend that you use package files to distribute
your VBA customizations.
114 V B A D E V E L O P E R S G U I D E
C H A P T E R 7 D I S T R I B U T I N G A P R O J E C T
Package files
Package files are special text files used to deliver customizations made with
the Modifier, VBA, and the Report Writer. A developer can create a package
file ontaining their customizations, move the package file to the destination
workstation, then import the customizations into the installation.
Modified forms Forms that have been customized with the Modifier.
To select non-contiguous items in the list, hold down the CTRL key and click the
items.
Click Export. A file dialog will appear, allowing you to specify the
name of the package file. Be sure the file has the .package extension.
The results of the export operation will be displayed in the status area
at the bottom of the window.
Note that some global changes you make with the Modifier and Report Writer
cant be included in a package file. For instance, changes to picture resources or
global data types wont be included in package files.
116 V B A D E V E L O P E R S G U I D E
C H A P T E R 7 D I S T R I B U T I N G A P R O J E C T
If any of the package file items contain VBA code, the user importing them
must have appropriate user privileges to update the .vba files in the Microsoft
Dynamics GP installation. Typically, this means being part of the
Administrators group or the Power Users group on Windows XP. On
Windows Vista, it means launching Microsoft Dynamics GP with
Administrative privileges.
Click OK to start the import process. If any errors occur during the
import process, the Errors window will be displayed. The results of the
import operation will also be displayed in the status bar of the
Customization Maintenance window. To view the list of the last errors
that occurred, click the Errors button in the status bar.
This fact is important to keep in mind when you import a package file that
contains customized forms. If a customized version of the form already
exists in the system and you import another set of customizations for that
same form, the original customizations will be overwritten. This occurs
even if the customizations are made for different windows in the form.
118 V B A D E V E L O P E R S G U I D E
PART 2: VBA OBJECT LIBRARY
Part 2: VBA Object Library
This part describes the objects that Microsoft Dynamics GP makes available
to VBA. It provides detailed descriptions of the events, properties and
methods for each object. The following topics are discussed:
Chapter 10, Grid Object, describes the events and properties for grids
(scrolling windows).
Chapter 11, Report Object, describes the events, properties and meth-
ods for reports.
Chapter 17, UserInfo Object, describes the properties and method for
retrieving information about the current user.
The syntax descriptions and programming style used in this part are the
same as those used in VBAs documentation.
120 V B A D E V E L O P E R S G U I D E
Chapter 8: Window Object
Your project uses the window object to perform several tasks related to
windows. The events, methods and properties that apply to the window
object are listed below. A detailed explanation of each follows.
Activate method
AfterActivate event
AfterClose event
AfterModalDialog event
AfterOpen event
BeforeActivate event
BeforeClose event
BeforeModalDialog event
BeforeOpen event
Caption property
Changed property
Close method
EventMode property
Height property
Hide method
IsLoaded property
Left property
Move method
Name property
Open method
PullFocus method
Required property
Show method
Top property
Visible property
Width property
Activate method
Description The Activate method activates a window, making it the frontmost.
Syntax window.Activate
Comments If the window is minimized, the Activate method expands the window.
Examples This example uses the Activate method to activate the Customer
Maintenance window:
CustomerMaintenance.Activate
122 V B A D E V E L O P E R S G U I D E
A F T E R A C T I V A T E E V E N T
AfterActivate event
Description The AfterActivate event occurs when a window is activated. It occurs after
the Microsoft Dynamics GP window activate event.
Syntax window_AfterActivate()
Comments A window is activated when its moved from the background to the
foreground in an application, becoming the frontmost window.
Microsoft Dynamics GP rarely uses its window activate event. Whether you choose
to use the AfterActivate event or BeforeActivate event is of little consequence.
Examples The following AfterActivate event procedure runs when the Customers
And Prospects lookup window is activated. As the user browses through
records in the Customer Maintenance window, the lookup window remains
open, but deactivated. When the user activates the lookup window, this
procedure places the focus on the same record in the lookup window as
was displayed in the Customer Maintenance window:
'Set the sort by list and locate the customer record displayed in
'the Customer Maintenance window
FindDialog.CustomerSortBy = 2
FindDialog.SearchText = CustomerMaintenance.Name1
AfterClose event
Description The AfterClose event occurs when the window closes. It occurs after the
Microsoft Dynamics GP window close event.
Syntax window_AfterClose()
Comments Microsoft Dynamics GP typically uses the window close event to check for
any unsaved changes, or whether the user entered all required fields in the
window, and display the appropriate dialog.
Examples In this example, the AfterClose event closes a lookup window after the
Customer Maintenance window closes:
124 V B A D E V E L O P E R S G U I D E
A F T E R M O D A L D I A L O G E V E N T
AfterModalDialog event
Description The AfterModalDialog event occurs after the user dismisses a modal
dialog.
Constant Description
dtModalAskDialog An ask dialog. It contains one or more buttons, and
displays message text.
dtModalGetStringDialog A getstring dialog. It contains a data entry field and OK
and Cancel buttons.
Constant Description
dcButton1 The first button in an ask dialog.
dcButton2 The second button in an ask dialog.
dcButton3 The third button in an ask dialog.
dcCancel The Cancel button in a getstring dialog.
dcOK The OK button in a getstring dialog.
Comments The most common use of the AfterModalDialog event is when saving or
deleting data objects from the Dynamic User Object Store (DUOS). For
instance, if the user clicks the Delete button in the Customer Maintenance
window, a modal dialog will appear, asking if the user wants to delete the
customer record. Using the AfterModalDialog event, you can find out
whether the user clicked the dialogs Delete button. If so, you can delete the
corresponding DUOS data object for that customer record.
The AfterModalDialog event wont occur for all dialogs displayed in Microsoft
Dynamics GP. Dialogs that display a help button and whose title is Microsoft
Dynamics GP will function like standard window objects in your project, and will
not invoke modal dialog events when displayed.
Ask dialogs
The dtModalAskDialog constant of the DlgType parameter specifies an ask
dialog. An ask dialog displays one or more buttons and message text. The
following illustration shows an ask dialog and the corresponding
parameters in the AfterModalDialog event:
PromptString
Control1String
Control2String Control3String
126 V B A D E V E L O P E R S G U I D E
A F T E R M O D A L D I A L O G E V E N T
Getstring dialogs
The dtModalGetStringDialog constant of the DlgType parameter specifies a
getstring dialog. Getstring dialogs typically contain a message, a data entry
field, and OK and Cancel buttons:
PromptString
Control3String
Control1String
Control2String
Examples This procedure runs when the accounting system displays an ask dialog
that prompts the user to save changes to a customer record in the Customer
Maintenance window. If the user clicks the Save button in the save dialog,
this procedure saves the corresponding DUOS object:
AfterOpen event
Description The AfterOpen event occurs when a window opens. It occurs after the
Microsoft Dynamics GP window open event.
Syntax window_AfterOpen()
Comments Microsoft Dynamics GP uses the window open event to set default field
values in the window, such as a default document date or a sort list
selection.
Since the AfterOpen event occurs after the Microsoft Dynamics GP open
event, use this event to override any default values that the accounting
system sets for window fields.
Examples In this example, the AfterOpen event procedure for the Sales Territory
Maintenance window marks two check box controls when the window
opens, overriding the default values that the accounting system sets in its
open event:
128 V B A D E V E L O P E R S G U I D E
B E F O R E A C T I V A T E E V E N T
BeforeActivate event
Description The BeforeActivate event occurs when a window is activated. It occurs
before the Microsoft Dynamics GP window activate event.
Syntax window_BeforeActivate()
Comments A window is activated when its moved from the background to the
foreground in an application, becoming the frontmost window.
Microsoft Dynamics GP rarely uses the window activate event. Whether you
choose to use the AfterActivate event or BeforeActivate event is of little
consequence.
BeforeClose event
Description The BeforeClose event occurs when the window closes. It occurs before the
Microsoft Dynamics GP window close event.
Comments Use this event is to cancel the window close if the contents in the window
dont meet certain criteria youve defined.
Examples In the following example, the BeforeClose event procedure for the Invoice
Entry window checks whether the user entered a comment ID. If not, it
displays a message dialog using VBAs MsgBox() function. If the user clicks
Yes, the procedure cancels the close using the AbortClose parameter, and the
user can enter a comment ID:
130 V B A D E V E L O P E R S G U I D E
B E F O R E M O D A L D I A L O G E V E N T
BeforeModalDialog event
Description The BeforeModalDialog event occurs when a modal dialog opens, but
before its displayed.
ByVal DlgType As Long Returns the dialog type. The following constants
specify the dialog type:
Constant Description
dtModalAskDialog An ask dialog. It contains one or more buttons, and
displays message text.
dtModalGetStringDialog A getstring dialog. It contains a data entry field and OK
and Cancel buttons.
Constant Description
dcButton1 The first button in an ask dialog.
dcButton2 The second button in an ask dialog.
dcButton3 The third button in an ask dialog.
dcCancel The Cancel button in a getstring dialog.
dcOK The OK button in a getstring dialog.
The AfterModalDialog event wont occur for all dialogs displayed in Microsoft
Dynamics GP. Dialogs that display a help button and whose title is Microsoft
Dynamics GP will function like standard window objects in your project, and will
not invoke modal dialog events when displayed.
Ask dialogs
The dtModalAskDialog constant of the DlgType parameter specifies an ask
dialog. Ask dialogs display one or more buttons and message text. The
following illustration shows an ask dialog and the corresponding
parameters in the BeforeModalDialog event:
PromptString
Control1String
Control2String Control3String
132 V B A D E V E L O P E R S G U I D E
B E F O R E M O D A L D I A L O G E V E N T
Getstring dialogs
The dtModalGetStringDialog constant of the DlgType parameter specifies a
getstring dialog. Getstring dialogs typically contain a message, a data entry
field, and OK and Cancel buttons:
PromptString
Control3String
Control1String
Control2String
BeforeOpen event
Description The BeforeOpen event occurs when a window opens. It occurs before the
Microsoft Dynamics GP window open event.
Comments Microsoft Dynamics GP typically uses the window open event to set default
field values in the window, such as a default document date or a sort list
selection. You can use this event to set default field values in the window,
while still allowing default values set by the accounting system to override
your defaults.
Examples The following event procedure for the Customer Maintenance window
checks the IsLoaded property to ascertain whether the Reminders window
is open. If it is, the procedure sets the opening position of the Customer
Maintenance window so it appears to the right of the Reminders window:
134 V B A D E V E L O P E R S G U I D E
C A P T I O N P R O P E R T Y
Caption property
Description The Caption property specifies the windows title thats displayed to the
user.
Comments Captions for data entry windows cannot exceed 80 characters. You cannot
change the caption for modal dialogs.
Windows modified using the Modifier include a period (.) at the beginning
of the windows caption. Windows that invoke VBA events include a
period at the end of the windows caption. These are visual indicators that
help technical support ascertain which windows youve modified. We
recommend you leave the period in the windows title.
Examples The following example sets the title when the window opens:
Changed property
Description The Changed property indicates whether any non-button field values in
the window have changed.
Comments A fields value changes when the user makes an entry in a field, or when
the fields value changes using VBA. In both of these instances, the
Changed property will return True for the current window.
The Changed event for a field always runs whenever a new record is
displayed in a window (such as when the user is browsing records using
browse buttons). However, only the windows Changed property
ascertains whether the user actually modified the contents displayed in the
window.
Examples The following example displays a message containing the date and time
when a customer record was changed. This information could also be
added to the DUOS or to another database (such as Microsoft Access) and
stored with the customer record.
136 V B A D E V E L O P E R S G U I D E
C L O S E M E T H O D
Close method
Description The Close method closes an open window.
Syntax window.Close
Comments Use the Close method to close any open window in Microsoft Dynamics
GP (a window whose IsLoaded property is True). An open window may
not necessarily be visible.
The Close method invokes the VBA BeforeClose event and AfterClose
event, and the Microsoft Dynamics GP close event for the window.
Examples The following example uses the BeforeClose event to close the Customers
and Prospects lookup window when the Customer Maintenance window
closes:
EventMode property
Description The EventMode property specifies whether VBA window events occur for
the original or modified version of the window.
Constant Description
emOriginalOnly Window events occur only for the original version of
the window.
emModifiedOnly Window events occur only for the modified version of
the window.
emNever Window events dont occur for the window.
Comments If you modify a window using the Modifier, VBA window events will occur
for the modified window only if you set the EventMode property to
emModified. To change the EventMode property for the window object,
use the Visual Basic Properties window.
Windows modified using the Modifier include a period (.) at the beginning
of the windows caption. Windows that invoke VBA events include a
period at the end of the windows caption.
Examples Typically, you set the windows EventMode property using the Visual Basic
Properties window. You can also set it through an event procedure, as
shown in the following example:
138 V B A D E V E L O P E R S G U I D E
E V E N T M O D E P R O P E R T Y
Height property
Description The Height property specifies the height of the window (in pixels).
Comments The Height property specifies only the space within the windows client
area. The client area is the window less the windows title bar.
Examples This example uses the Height property to set the height of the Invoice Entry
window:
140 V B A D E V E L O P E R S G U I D E
H I D E M E T H O D
Hide method
Description The Hide method makes an open window invisible.
Syntax window.Hide
Comments Use the Hide method to make any open window invisible. While invisible,
the window is open (its IsLoaded property is True) and data in the window
is still accessible. Use this method when you want to reference data
displayed in a window without making the window visible. You can also
make a window invisible using its Visible property, or by using the
OpenVisible parameter of the windows BeforeOpen event.
Examples The following example compares a transaction amount a user enters in the
Receivables Transaction Entry window with the batch limit displayed in an
invisible Receivables Batch Entry window:
IsLoaded property
Description The IsLoaded property returns whether the window is loaded (opened). A
window can be loaded, but not visible.
Comments Use the IsLoaded property when you want to be sure a window is
available, either visibly or invisibly. If you want to check only for the visible
version of a window, use the windows Visible property.
When you open a window, Microsoft Dynamics GP implicitly open all the
windows in the form invisibly, then displays the first window in the form
(the parent window). The child windows in the form remain invisible, but
loaded. You can use the Show method to selectively display these child
windows without explicitly opening them.
Examples The following event procedure runs when the Customer Maintenance
window closes. It closes the Customers And Prospects lookup window if
its loaded:
142 V B A D E V E L O P E R S G U I D E
L E F T P R O P E R T Y
Left property
Description The Left property specifies the horizontal position of the window (in
pixels).
integer The distance the windows left border is from the left border of the
main Microsoft Dynamics GP window, measured in pixels.
Examples In the following example, the event procedure uses the Left property to
open the Customer Maintenance window at a specified position:
Move method
Description The Move method changes the position of a window.
left An integer specifying the distance (in pixels) from the left edge of the
window to the left edge of the application border.
top An integer specifying the distance (in pixels) from the top edge of the
main Dynamics GP windows client area. The client area is the window area
less the windows title bar.
height An integer specifying the height (in pixels) of the windows client
area.
Comments You can use named arguments for the Move method, or specify arguments
by position. If you use positional arguments, enter each in the order shown,
using commas and empty string values ("") to indicate the relative position
of arguments you dont specify.
Examples The following example uses named arguments to move the left position of
and the top position of the window:
144 V B A D E V E L O P E R S G U I D E
N A M E P R O P E R T Y
Name property
Description The Name property specifies the internal name VBA uses to reference a
window.
Syntax window.Name
Comments You cannot set the Name property at runtime. We recommend that you
change a windows Name property using the Visual Basic Properties
window. Be sure to replace all referenced occurrences of the window with
the new name.
Use the Name property to change the window name when you need to
resolve naming conflicts between windows and other objects in your
application. Naming conflicts typically arise between names in your project
and reserved words in VBA.
You can also use the Name property when you want to make an windows
name more readable in VBA code.
Examples To change a windows Name property, use the Visual Basic Properties
window.
Open method
Description The Open method opens a window.
Syntax window.Open
Comments There are two ways of opening Microsoft Dynamics GP windows in VBA:
Use the Open method to open windows that initially appear empty,
with no existing record displayed.
If you use the Open method in your VBA code to open a window, but the
window cannot be opened, a VBA exception will be thrown. You can
handle this exception using the On Error statement in VBA.
Examples The following procedure opens the Receivables Transaction Entry window
when using the Open method when the user initially logs into the
accounting system.
146 V B A D E V E L O P E R S G U I D E
O P E N M E T H O D
The following procedure clicks the lookup button to display the Shipping
Methods lookup window:
PullFocus method
Description The PullFocus method removes the focus from a window and specifies
whether data for the currently-focused field is valid.
Syntax window.PullFocus
Return value A boolean indicating whether the method succeeded in pulling the focus
from the window:
Value Description
True The focus was pulled from the window. Microsoft Dynamics GP field
events (user changed and lost focus) verified that the focused field
contained valid data.
False The focus was not pulled from the window. Microsoft Dynamics GP
field events determined that the focused field contained invalid data,
then displayed a message dialog.
You cannot use the PullFocus method with a window where VBA window events
or window field events are occuring. If you do, a VBA error will occur.
The primary use of the PullFocus method is to check whether the currently-
focused field contains valid data. The accounting system verifies data in
each field when the focus moves out of the field. However, when youre
controlling a window remotely, the focus remains in the last field youve
set using VBA, and the accounting system cannot verify its contents. To
manually verify the contents of this field, use the PullFocus method.
The PullFocus method moves the focus off the window, which runs the
Microsoft Dynamics GP user changed and lost focus events for the focused
field. The accounting system uses these field events to verify the contents of
the field. If the field contains invalid data, a message dialog will appear and
the PullFocus method will return False. If the focused field contains valid
data, the PullFocus method will return True.
148 V B A D E V E L O P E R S G U I D E
P U L L F O C U S M E T H O D
Examples The following procedure runs when the user clicks a button in a VBA user
form. It saves the contents of the Receivables Transaction Entry window by
programmatically clicking the Save button. In this case, the save occurs
even though the user entered a non-existent shipping method in the
currently focused field in the window (Shipping Method). No warning
dialog appeared, and the partial shipping method wasnt saved:
Using the PullFocus method, the same example can conditionally save the
contents of the window only if data in the focused field is valid. In this case,
when the PullFocus method moves the focus out of the Receivables
Transaction Entry window, the Microsoft Dynamics GP user changed and
lost focus events verify the currently-focused field (Shipping Method). If
the field contains invalid data, the PullFocus method returns False, and the
save isnt performed:
Required property
Description The Required property ascertains whether the user entered data in all
fields whose Required property is True.
Comments Required fields are those in which an entry is necessary to store the record
properly. You can make additional fields required using the field objects
Required property.
Use the window objects Required property when saving data objects to
the Dynamic User Object Store (DUOS). Since the accounting system wont
permit the user to save a record in a window without entering all required
fields, checking the Required property from VBA lets you save the DUOS
data object only when the accounting system permits the save.
Examples In the following example, an event procedure for the save button saves
DUOS data objects in the Customer Maintenance window only if the user
entered all required fields (the Required property is True):
150 V B A D E V E L O P E R S G U I D E
S H O W M E T H O D
Show method
Description The Show method displays a window thats open, but invisible.
Syntax window.Show
Comments A window can be open, but still invisible. To check whether a window is
open, but not necessarily visible, use the windows IsLoaded property.
You can make a window invisible using the windows Visible property, the
windows Hide method, or the OpenVisible parameter of the windows
BeforeOpen event.
When you open a window, the accounting system implicitly opens all the
windows in the form invisibly, then displays the first window in the form
(the parent window). The child windows in the form remain invisible, but
loaded. You can use the Show method to selectively display these child
windows without explicitly opening them.
Examples The following example shows a window thats open, but invisible. In this
case, the invisible window (Customer Maintenance Options) is from the
same form as the parent window (Customer Maintenance) from which this
event procedure is run:
Top property
Description The Top property specifies the vertical position of the window.
integer The distance from the top of the main Microsoft Dynamics GP
windows client area, measured in pixels.
Examples In the following example, the event procedure ascertains the position of the
Reminders window, then uses the Top property to align the top border of
the Customer Maintenance window with the top border of the Reminders
window:
152 V B A D E V E L O P E R S G U I D E
V I S I B L E P R O P E R T Y
Visible property
Description The Visible property specifies whether an open window is visible.
Comments You can make any window invisible, provided its open (its
IsLoaded property is True). While invisible, data in the window is still
accessible. Use this property when you want to reference data displayed in
a window without making the window visible.
You can also make a window invisible using the Hide method or the
OpenVisible parameter of the windows BeforeOpen event.
When you open a window, the accounting system implicitly opens all the
windows in the form invisibly, then displays the first window in the form
(the parent window). The child windows in the form remain invisible, but
loaded. You can use the Visible property to selectively display invisible
child windows without explicitly opening them.
Examples When the user enters a transaction amount in the Receivables Transaction
Entry window, the following event procedure compares the amount with
the maximum batch total in an invisible Receivables Batch Entry window:
154 V B A D E V E L O P E R S G U I D E
W I D T H P R O P E R T Y
Width property
Description The Width property specifies the width of a window (in pixels).
Examples The following event procedure runs when the Customer Maintenance
window opens. The event procedure checks if the Reminders window is
open, then uses the Width and Left properties for the Reminders window
to position the Customer Maintenance window to the right and below:
AfterGotFocus event
AfterLostFocus event
AfterUserChanged event
BeforeGotFocus event
BeforeLostFocus event
BeforeUserChanged event
Caption property
Changed event
Empty property
Enabled property
Focus method
FocusSeg method
Height property
Left property
Locked property
Move method
Name property
Object property
Parent property
Required property
TabStop property
Top property
Value property
ValueSeg property
Visible property
Width property
AfterGotFocus event
Description The AfterGotFocus event occurs when the user moves the focus into a field,
using the TAB key or the mouse. It occurs after the Microsoft Dynamics GP
got focus event.
Syntax field_AfterGotFocus()
Comments Use the AfterGotFocus event to set or evaluate the contents of a field
gaining focus. Microsoft Dynamics GP rarely uses the got focus event.
When it does, the accounting system typically checks the value of the field
thats gaining focus, and performs calculations or sets other field values
based on that value.
Examples In the following example, the AfterGotFocus event for the Batch ID field
checks whether the field is empty when the user moves to it. If it is, the
event procedure opens the batch lookup window:
158 V B A D E V E L O P E R S G U I D E
A F T E R L O S T F O C U S E V E N T
AfterLostFocus event
Description The AfterLostFocus event occurs when the user moves the focus out of a
field, using the TAB key or the mouse. It occurs after the Microsoft Dynamics
GP lost focus event.
Syntax field_AfterLostFocus()
Comments Use the AfterLostFocus event to evaluate the value of the field losing focus,
and perform any additional operations in the window. You cannot set the
value of the field losing focus using the AfterLostFocus event. This event
follows the Microsoft Dynamics GP user changed event, which is typically
used to verify the contents of the field.
Examples In the following example, the AfterLostFocus event checks whether the
Payment Terms field is empty. If it is, it displays a VBA dialog that asks the
user if they want to enter one. If the user clicks Yes, the procedure opens the
Payment Terms Lookup window:
AfterUserChanged event
Description The AfterUserChanged event occurs when the user changes the contents of
a field, then moves the focus out of the field, using the TAB key or the
mouse. In fields that toggle, such as push buttons and visual switches, this
event occurs when the user clicks the field. For list fields, this event occurs
when the user selects an item in the list. In all instances, the
AfterUserChanged event occurs after the Microsoft Dynamics GP user
changed event.
Syntax field_AfterUserChanged()
Comments Use the AfterUserChanged event to evaluate the value in the field losing
focus. You cannot set the value of the field losing focus using the
AfterUserChanged event. This event occurs after the Microsoft Dynamics
GP user changed event, which is used to verify the contents of the field.
Examples The following example uses the AfterUserChanged event to check the
value of the Customer ID field in the Invoice Entry window. It disables the
Trade Discount field:
160 V B A D E V E L O P E R S G U I D E
B E F O R E G O T F O C U S E V E N T
BeforeGotFocus event
Description The BeforeGotFocus event occurs when the user moves the focus to a field,
using the TAB key or the mouse. It occurs before the Microsoft Dynamics GP
got focus event.
Comments Use the BeforeGotFocus event to set or evaluate the contents of the field
gaining focus, and cancel any further got focus events for the field from
running (the Microsoft Dynamics GP got focus event and the VBA
AfterGotFocus event).
If Microsoft Dynamics GP uses its got focus event, it typically checks the
value of the field thats gaining focus, and performs calculations or sets
other field values based on that value. However, the accounting system
uses this event infrequently to do such operations.
Examples In the following example, the procedure checks whether the document type
is a return. If so, this procedure cancels the VBA AfterGotFocus event
procedure, which automatically opens a lookup window for the Batch ID
field:
BeforeLostFocus event
Description The BeforeLostFocus event occurs when the user moves the focus out of a
field, using the TAB key or the mouse. It occurs before the Microsoft
Dynamics GP lost focus event.
Comments Use the BeforeLostFocus event to evaluate the value of the field losing
focus, and perform any additional operations in the window. You cannot
set the value of the field losing focus using the BeforeLostFocus event. This
event follows the Microsoft Dynamics GP user changed event, which is
used to verify the contents of the field losing focus.
Examples In the following example, the BeforeLostFocus event procedure runs for
the Number field (document number) in the Receivables Transaction Entry
window. Normally, the Microsoft Dynamics GP lost focus event locks this
field. In this case, the procedure cancels subsequent lost focus events, and
the Number field remains unlocked and editable:
162 V B A D E V E L O P E R S G U I D E
B E F O R E U S E R C H A N G E D E V E N T
BeforeUserChanged event
Description The BeforeUserChanged event occurs when the user changes the contents
of a field, then moves the focus out of the field, using the TAB key or the
mouse. In fields that toggle, such as push buttons and visual switches, this
event occurs when the user clicks the field. For list fields, this event occurs
when the user selects an item in the list. In all instances, the
BeforeUserChanged event occurs before the Microsoft Dynamics GP user
changed event.
Comments Use the BeforeUserChanged event to set or evaluate the value of field
thats losing focus. You can also use this event to bypass, or cancel, the
Microsoft Dynamics GP user changed event that occurs for the field.
For many data entry fields, the Microsoft Dynamics GP user changed event
verifies the value entered in the field. For push buttons, such as a Save or
Delete button, the user changed event saves or deletes the record displayed
in the window.
Use caution when canceling a data entry fields user changed events using the
BeforeUserChanged event. Canceling the Microsoft Dynamics GP user changed
event for these fields inhibits the accounting systems ability to verify the contents
of the field.
164 V B A D E V E L O P E R S G U I D E
C A P T I O N P R O P E R T Y
Caption property
Description The Caption property specifies the caption for a window field.
Comments The following list explains the caption for each field type:
If you make a caption longer than the existing caption, keep in mind that
you may need to resize or rearrange fields to view the entire caption.
Examples In the following example, the event procedure changes the caption for the
Customer ID field to Patient ID, then changes the windows caption:
Changed event
Description The Changed event always occurs when a fields value changes.
Syntax field_Changed()
Comments The Changed event will always occur in the following instances:
When the user changes the field directly. This also activates the fields
BeforeUserChanged and AfterUserChanged events, as well as the
Microsoft Dynamics GP user changed event.
Use the Changed event to execute VBA code for window fields that
Microsoft Dynamics GP updates. This occurs most often when the
accounting system retrieves a record from a table, then displays the records
contents in the window. The AfterUserChanged and BeforeUserChanged
events wont occur for a field in this case, since the accounting system, not
the user, changes the contents of the field.
Exercise caution when using the Changed event. In many cases, Microsoft
Dynamics GP causes the Changed event to occur numerous times for the same
field even though the value of the field may not visibly change. This will execute the
associated VBA event procedure each time the event occurs.
166 V B A D E V E L O P E R S G U I D E
C H A N G E D E V E N T
Examples In the following example, the Changed event procedure runs for the
Document Date field. As the user browses records in the Receivables
Transaction Entry window, the accounting system sets window field values
for each record browsed. This procedure checks the value of the Document
Date field, and displays a message if the document is older than 90 days:
Empty property
Description The Empty property specifies whether a field contains a value.
boolean If True, the field is empty. If False, the field contains a non-zero
value.
Comments To clear a fields value, set the fields Empty property to True. You cannot
clear a field that the accounting system locked or disabled, but you can
clear fields youve locked or disabled using VBA.
Examples The following example uses the Empty property to automatically open a
lookup window if the Salesperson ID field is empty:
168 V B A D E V E L O P E R S G U I D E
E N A B L E D P R O P E R T Y
Enabled property
Description The Enabled property specifies whether a window field is enabled. The
user cant change the contents of a disabled field.
boolean If True, the field is enabled and editable. If False, the field is
disabled.
Comments A disabled field appears dimmed, along with its caption, the fields value,
and any related controls.
You can set the value of a field youve disabled using VBA. You cannot set
the value of a field disabled by the accounting system, nor can you enable
the field. For most windows, Microsoft Dynamics GP application code
disables and enables fields based on entries made by the user. You can
always return a disabled fields value.
Keep in mind that a disabled field is not the same as a locked field (a field
whose Locked property is True). If True, the Locked property makes the
field non-editable, but doesnt dim the fields value, prompt and related
controls.
Examples In the following example, the event procedure disables a field in the
Salesperson Maintenance window for a specific user:
Focus method
Description The Focus method moves the focus to a field.
Syntax field.Focus([setvalue])
setvalue The value you can set for the field gaining focus.
Comments Use the Focus method to skip unnecessary fields in a window, or place the
focus in a specific field based upon a user entry. The Microsoft Dynamics
GP got focus event and the VBA BeforeGotFocus and AfterGotFocus
events occur when you move the focus to a field using the Focus method.
Examples In the following example, the Focus method redirects the focus to the
Shipping Method field when the user moves out of the Customer ID field:
Using the setvalue parameter, the Focus method redirects the focus and sets
the value of the Shipping Method field:
170 V B A D E V E L O P E R S G U I D E
F O C U S S E G M E T H O D
FocusSeg method
Description The FocusSeg method moves the focus to a segment in a composite field.
Microsoft Dynamics GP account number fields are composite fields.
index An integer specifying the segment youre moving the focus to.
setvalue The value you can set for the segment gaining focus.
Comments Use the FocusSeg method to move the focus to a composite segment from
another field in the window, or from one segment to the next within the
same composite.
You can use the setvalue of the FocusSeg method to set the value of a
segment gaining focus. Based on whether youre setting the value from
inside or outside the field, there are two primary ways to set segment
values:
If you set the segments value from outside the composite field (from
another fields event procedure), use the setvalue parameter of the
FocusSeg method to set the segments value. In this case, you can set
only one segments value.
If you set the segments value from inside the composite field (from
the composite fields BeforeGotFocus, AfterGotFocus or BeforeUser-
Changed events), you can use the ValueSeg method to set values for
each segment of the composite. Use the FocusSeg method to move the
focus to a segment, but not set the segments value.
Examples The following example sets an account number segment from outside the
Account field (using the Checkbook ID fields AfterUserChanged event).
The procedure moves the focus to the Account field, then sets the first
segment using the FocusSeg method:
The following example sets account segments from inside the Account
field. In this case, the Account field already has the focus. The
ValueSeg property sets the first two segment values. The FocusSeg
method then moves the focus to the third segment:
172 V B A D E V E L O P E R S G U I D E
H E I G H T P R O P E R T Y
Height property
Description The Height property specifies the height of a window field (in pixels).
Comments For data entry fields, the default height is 20 pixels. For push buttons, such
as Save and Delete, the default height is 24 pixels.
List box and multi-select lists require 20 pixels per item to display the item
properly. Changing the height of drop lists and combo boxes does not
change the height of the list in the undropped position. Instead, changing
the height of these lists determines the height of the list in the dropped
position.
Examples In the following example, the event procedure changes the height of a list
box field in the Account Maintenance window so that it shows two items
rather than four:
Left property
Description The Left property specifies the horizontal position of a field (in pixels).
integer The distance from the fields left border to the windows left
border, measured in pixels. Your operating systems settings for the active
window border specifies the width of the border.
Comments The Left property moves the field and the fields caption. It does not move
any buttons associated with the field, such as a lookup button. You must
move those fields separately.
Setting the Left property to 0 will align the field with the left edge of the
window. However, the fields caption will move off the window area.
Examples In the following example, the event procedure runs when the Account
Maintenance window opens. It first hides the Budget button, then uses the
Left property to move the Currency button to the same position as the
Budget button:
174 V B A D E V E L O P E R S G U I D E
L O C K E D P R O P E R T Y
Locked property
Description The Locked property locks a field. The user cant change the value of a
locked field, but they can view data in the field.
boolean If True, the field is locked and not editable. If False, the field is not
locked.
Comments A locked field appears with a light gray background, indicating that the
user cant change the contents of the field.
You can set the value of a field youve locked using VBA. You cannot set the
value of a field locked by the accounting system, nor can you unlock a field
locked by the accounting system. Typically, Microsoft Dynamics GP
application code locks fields whose values are necessary to preserve the
integrity of accounting data, such as document totals. You can always
return a locked fields value.
Keep in mind that a locked field is not the same as a disabled field (a field
whose Enabled property is False). If False, the Enabled property makes the
field non-editable, and also dims the fields value, prompt and related
controls.
Examples The following event procedure runs when the Account Maintenance
window opens. It locks the Account Number field for a specific user:
Move method
Description The Move method moves and resizes a window field.
left An integer specifying the distance from the fields left border to the
windows border, measured in pixels. Your operating systems settings for
the active window border determines the width of the border.
top An integer specifying the distance from the fields top border to the
top edge of the windows client area, measured in pixels. The client area is
the area of the window, less the windows title bar.
Comments You can use named arguments for the Move method, or enter arguments by
position. If you use positional arguments, enter each in the order shown,
using commas and empty string values ("") to indicate the relative position
of arguments you dont specify. Any unspecified arguments remain
unchanged.
Examples The following example uses positional arguments to change the position of
a Customer ID field when the Customer Maintenance window opens:
Private Sub Window_BeforeOpen(OpenVisible As Boolean)
CustomerID.Move 10, 10, 50, ""
End Sub
The following example uses named arguments to move the left position of
the field and change the width of the field:
Private Sub Window_BeforeOpen(OpenVisible As Boolean)
'Set the left distance and the field width
CustomerID.Move Left: = 10, Width: = 70
End Sub
176 V B A D E V E L O P E R S G U I D E
N A M E P R O P E R T Y
Name property
Description The Name property specifies the internal name VBA uses to reference a
window field or report field object.
Syntax field.Name
Comments You cannot set the Name property at runtime. We recommend that you
change a fields Name property using the Visual Basic Properties window.
Be sure to replace all referenced occurrences of the field with the new name.
Use the Name property to change the field name when you need to resolve
naming conflicts between fields and other objects in your application.
Naming conflicts typically arise between names in your project and
reserved words in VBA. For instance, if you add the Account Maintenance
windows Currency button to your project, you cant compile VBA code
referencing that field until you rename the field. Currency in VBA is a
reserved word.
You can also use the Name property to make a fields name more readable
in VBA code.
Examples Use the Visual Basic Properties window to change a fields Name property.
Object property
Description The Object property returns a field object without the standard properties
and methods extended to it by Visual Basic.
Comments Use the Object property to reference custom properties and methods
assigned to the field object exclusively for Microsoft Dynamics GP. For field
objects, custom properties and methods include:
Method/property Method/property
Caption property Locked property
Empty property Required property
Enabled property Value property (report and window fields)
FocusSeg method Visible property (report and window
fields)
178 V B A D E V E L O P E R S G U I D E
O B J E C T P R O P E R T Y
In these cases, the Object property allows you to bypass the VBA-supplied
standard property or method and use the identically-named property or
method defined by the host application.
Since there are no inherent naming conflicts with VBA methods and properties for
field objects, you dont need to use this property when working with field objects.
Examples The Object property is not relevant for field objects at this time. Refer to the
Visual Basic online help for additional information and examples of the
Object property.
Parent property
Description The Parent property returns a window or report object containing a
specified field object.
Syntax field.Parent
Comments Use the Parent property to access the methods or properties of a window or
report object containing a given field object. This is especially useful if
youre passing field objects to procedures as arguments. In these cases, you
can use the Parent property to obtain information about the window or
report containing the field object you passed to the procedure.
Examples In the following example, two procedures illustrate how to use the Parent
property. The first procedure uses the Call statement to call a sub procedure
named FieldCaption after a Microsoft Dynamics GP window opens. It
passes the windows Customer ID field as an object to the procedure.
The second procedure is a new sub procedure defined for the module. It
receives the Customer ID field object passed from the first procedure. The
procedure then uses the Parent property to ascertain the name of the
window where the field was passed from, and changes the fields caption
based on the windows name:
180 V B A D E V E L O P E R S G U I D E
R E Q U I R E D P R O P E R T Y
Required property
Description The Required property specifies whether the field must contain data before
the user can save information in the window.
boolean If True, the field is required. If False, the field is not required.
Comments Microsoft Dynamics GP will prompt the user to enter data in a required
field if the field is empty when the user attempts to save the record. Fields
marked as required by VBA are displayed with the same caption, font style
and color as fields marked as required by the accounting system.
You cannot set the Required property to False for fields already marked as
required by the accounting system. These are fields necessary for the
system to store the record properly.
Examples The following example sets the Required property for the Phone 1 field in
the Customer Maintenance window:
TabStop property
Description The TabStop property specifies whether a field is in the windows tab
sequence.
boolean If True, the field is in the windows tab sequence. If False, the field
is not in the tab sequence.
Comments The tab sequence is the order in which the focus moves through fields when
the user presses the TAB key. Removing a field from the tab sequence
bypasses the field when you press the TAB key. If you add the field back to
the sequence, it maintains its original order in the sequence.
Removing a rarely-used field from the tab sequence allows users to move
from field to field more efficiently. Once you remove a field from the tab
sequence, the user can still place the focus in the field using the mouse. This
setting doesnt affect the users ability to enter data in the field.
Since you cannot place the focus in hidden, locked or disabled fields, the
accounting system ignores these fields in the tab sequence.
Examples The following example removes three fields from the tab sequence in the
Invoice Entry window:
182 V B A D E V E L O P E R S G U I D E
T O P P R O P E R T Y
Top property
Description The Top property specifies the vertical position of a field.
integer The distance from the fields top border to the top edge of the
windows client area, measured in pixels. The client area is the area of the
window, less the windows title bar.
Examples The following example uses the Top property and Left property to move
the Currency1 button field to the same window position as the invisible
Budget button field:
Value property
Description The Value property specifies the value of a window or report field. For
window fields, the Value property is a read/write property. For report
fields, the Value property is a read-only property unless the field is a user-
defined calculated field.
Comments If you omit the Value property from your code, VBA assumes youre
returning or setting the value of the field. The following example explicitly
uses the Value property:
BatchID.Value = "DEBITMEMOS"
The same code can also omit the Value property and be written like this:
BatchID = "DEBITMEMOS"
The following table lists each field type for which you can set or return a
value using the Value property, as well as a description of the string value
used for the field type:
184 V B A D E V E L O P E R S G U I D E
V A L U E P R O P E R T Y
Examples The following example sets the Sort By list field in the Customer
Maintenance window when the window opens:
The following example sets the value of the Batch ID string field when the
user chooses a document type from the Document Type drop-down list:
The following example checks the value of the Checkbook ID field, then
sets the value of the Cash Account composite field to an existing account
number. Note that the syntax omits the Value property:
Additional information
Working with field values and Window field type reference in Chapter 3, Programming
Window Fields.
186 V B A D E V E L O P E R S G U I D E
V A L U E S E G P R O P E R T Y
ValueSeg property
Description The ValueSeg property specifies the value of a segment in a composite
field.
Comments Use the ValueSeg property to set or return segment values after the
composite gains focus (using the BeforeGotFocus, AfterGotFocus or
BeforeUserChanged events). The composite gains focus when the user
moves to the composite field, or when you programatically move the focus
using the Focus method or FocusSeg method.
Examples The following procedure moves the focus to the Cash Account field using
the FocusSeg method:
Once the Cash Account has focus, a BeforeGotFocus event procedure sets
the value of the segments in the composite using the ValueSeg property:
Visible property
Description The Visible property specifies whether the field is visible.
Comments If you hide a window field using the Visible property, VBA hides both the
field and its caption. You can still return or set the values of fields you make
invisible with VBA. However, you cannot set the value of fields hidden by
the accounting system, nor can you show these fields.
Examples The following example hides sales fields when a user opens the Salesperson
Maintenance window:
Events
The window objects BeforeOpen event.
188 V B A D E V E L O P E R S G U I D E
W I D T H P R O P E R T Y
Width property
Description The Width property specifies the width of a field (in pixels).
Comments The Width property calculates the fields width starting at the left edge of
the field, and sizes the field to the specified width. The Width property
doesnt affect the fields caption.
Examples The following example changes the width of several fields in the Invoice
Entry window:
AfterLineChange event
AfterLineGotFocus event
AfterLineLostFocus event
BeforeLineChange event
BeforeLineGotFocus event
BeforeLineLostFocus event
BeforeLinePopulate event
Changed property
EventMode property
Hide method
Left property
Move method
Name property
Show method
Top property
Visible property
AfterLineChange event
Description The AfterLineChange event occurs when the contents of a line change in an
editable or adds-allowed grid, and the focus moves out of the line. It occurs
after the Microsoft Dynamics GP line change event.
Syntax grid_AfterLineChange()
Comments Use the AfterLineChange event to perform other actions in the window
after the Microsoft Dynamics GP line change event. Microsoft Dynamics GP
uses the line change event to save data in the line when the user leaves the
line. An example of this is the Invoice Entry window, where each grid line
represents a separate transaction record in the invoice. As the user moves to
a new line, the line change event saves the invoice line item record to a
table.
Examples The following AfterLineChange event procedure checks the Subtotal field
in the Invoice Entry window when the user enters line item information in
the Invoice Entry grid. If the invoice subtotal is greater than $1000, it
assigns the transaction to an existing batch:
192 V B A D E V E L O P E R S G U I D E
A F T E R L I N E G O T F O C U S E V E N T
AfterLineGotFocus event
Description The AfterLineGotFocus event occurs when the focus moves to a new line
in a grid. It occurs after the Microsoft Dynamics GP line got focus event.
Syntax grid_AfterLineGotFocus()
Comments Use the AfterLineGotFocus event to evaluate the contents of fields in the
line gaining focus.
Examples In the following example, the AfterLineGotFocus event checks the value of
the Item Number field in the Invoice Entry grid. If the field is empty, the
procedure opens the Items lookup window:
AfterLineLostFocus event
Description The AfterLineLostFocus event occurs when the focus moves out of a line. It
occurs after the Microsoft Dynamics GP line lost focus event.
Syntax grid_AfterLineLostFocus()
Comments Microsoft Dynamics GP rarely uses the line lost focus event. Whether you
choose the VBA AfterLineLostFocus or BeforeLineLostFocus event is of
little consequence.
194 V B A D E V E L O P E R S G U I D E
B E F O R E L I N E C H A N G E E V E N T
BeforeLineChange event
Description The BeforeLineChange event occurs when the contents of a line change in
an editable or adds-allowed grid, and the focus moves out of the line. It
occurs before the Microsoft Dynamics GP line change event.
Comments Use the BeforeLineChange event to check the value of fields in a line, then
cancel subsequent line change events if criteria you specify arent met.
Examples In this example, the BeforeLineChange event checks the value of the
Markdown Amount field in the Invoice Entry grid. If the user attempts to
enter a markdown amount greater than 20%, the KeepFocus parameter stops
the line change event, and moves the focus back to the current line:
BeforeLineGotFocus event
Description The BeforeLineGotFocus event occurs when the focus moves to a new line
in a grid. It occurs before the Microsoft Dynamics GP line got focus event.
Syntax grid_BeforeLineGotFocus()
Comments Use the BeforeLineGotFocus event to evaluate the contents of fields in the
line gaining focus.
Microsoft Dynamics GP rarely uses the line got focus event, so whether you
choose the VBA AfterLineGotFocus or BeforeLineGotFocus event is of
little consequence.
196 V B A D E V E L O P E R S G U I D E
B E F O R E L I N E L O S T F O C U S E V E N T
BeforeLineLostFocus event
Description The BeforeLineLostFocus event occurs when the focus moves out of a line.
It occurs before the Microsoft Dynamics GP line lost focus event.
Syntax grid_BeforeLineLostFocus()
Comments Microsoft Dynamics GP rarely uses the line lost focus event. Whether you
choose the VBA AfterLineLostFocus or BeforeLineLostFocus event is of
little consequence.
BeforeLinePopulate event
Description The BeforeLinePopulate event occurs each time the accounting system
displays a new line in a grid.
Examples The following event procedure displays only Illinois customers in the
Customers and Prospects lookup window:
198 V B A D E V E L O P E R S G U I D E
C H A N G E D P R O P E R T Y
Changed property
Description The Changed property indicates whether any non-button field values in a
grids line have changed.
Comments A grid line changes when the value changes in one of the lines fields. If this
occurs, the Changed property will return True for the current line (the line
with focus) in the grid.
Examples The following example displays a message containing the date and time
when a line in a sales transaction record was changed. This information
could also be added to the DUOS or to another database (such as Microsoft
Access) and stored with the transaction record:
EventMode property
Description The EventMode property specifies whether VBA grid events occur for the
original or modified version of the grid.
Constant Description
emOriginalOnly Grid events occur only for the original version of the grid.
emModifiedOnly Grid events occur only for the modified version of the
grid.
emNever Grid events dont occur for the grid.
Comments If you use the Modifier to modify a window containing a grid (such as a
lookup window), VBA grid events will occur only if you set both the
windows and the grids EventMode property to emModifiedOnly.
Changing this property allows events to occur only for the modified
version of the window and the grid. To change the EventMode property for
the window object, use the Visual Basic Properties window.
Windows modified using the Modifier include a period (.) at the beginning
of the windows caption. Windows that invoke VBA events include a
period at the end of the windows caption. Grids have no caption.
Examples Typically, you set the grids EventMode property using the Visual Basic
Properties window. You can also set it through an event procedure, as
shown in the following example:
Private Sub Window_BeforeOpen(OpenVisible As Boolean)
If CustomersandProspects.Caption = ".Customers and Prospects." _
Then
'Shut off events for the window and the grid.
CustomersandProspects.EventMode = emOriginalOnly
CustomersandProspectsDetail.EventMode = emOriginalOnly
End If
End Sub
200 V B A D E V E L O P E R S G U I D E
H I D E M E T H O D
Hide method
Description The Hide method makes a grid invisible.
Syntax grid.Hide
Comments Use the Hide method to make any grid in an open window invisible. While
invisible, data in the grid is accessible. Use this method when you want to
reference data displayed in the grid without making the grid itself visible,
or if you want to hide the contents of the grid from the user.
If you use the window objects Hide method to hide a window containing a
grid (such as a lookup window), the Hide method hides both the window
and the grid.
Examples The following example hides the grid on the Sales Transaction Entry
window when the window opens. The rest of the window (the windows
header fields, and the windows summary fields) are still visible:
Left property
Description The Left property specifies the horizontal position of the grid (in pixels)
relative to the left edge of the window containing the grid.
integer The distance (in pixels) from the left edge of the grid to the left
edge of the windows border.
Comments The Left property will specify the distance to the left edge of the window
for the grid and any of the fields, prompts and controls associated with the
grid. These items are associated with the grid if theyre no further than one
pixel from the grids border, or if theyre one pixel from the edge of another
associated item.
Examples The following example positions the left edge of the grid 20 pixels from the
left edge of the window using the Left property:
202 V B A D E V E L O P E R S G U I D E
M O V E M E T H O D
Move method
Description The Move method changes the position of a grid relative to the window
containing the grid.
left An integer specifying the distance (in pixels) from the left edge of the
grid to the left edge of the windows border.
top An integer specifying the distance (in pixels) from the top edge of the
grid to the bottom edge of the windows title bar.
Comments You can use named arguments for the Move method, or specify arguments
by position. If you use positional arguments, enter each in the order shown,
using commas and null string values ("") to indicate the relative position of
arguments you dont specify.
The Move method will move the grid and any of the fields, prompts and
controls associated with the grid. These items are associated with the grid if
theyre no further than one pixel from the grids border, or if theyre one
pixel from the edge of another associated item.
Examples The following example uses named arguments to change the height of the
grid when the Customers and Prospects lookup window opens:
The following example positions the grid in the Customers and Prospects
lookup window using positional arguments. It sets only the first two
parameters:
204 V B A D E V E L O P E R S G U I D E
N A M E P R O P E R T Y
Name property
Description The Name property specifies the internal name VBA uses to reference a
grid object.
Syntax grid.Name
Comments The first portion of a grids name is the same as the name of the window
containing the grid. However, grid names include the word Detail at the
end of the name.
Use the Name property to change a grid name when you need to resolve
naming conflicts between grids and other objects in your application. You
can also use the Name property when you want to make an objects name
more readable in VBA code. Be sure to replace all referenced occurrences of
the window with the new name.
Examples To change a grids Name property, use the Visual Basic Properties window.
Show method
Description The Show method makes visible any invisible grid.
Syntax grid.Show
Comments Use the Show method to make visible any grid thats currently invisible.
The Hide method and Visible property make a grid invisible.
Examples The following example shows a grid thats been hidden using either the
Hide method or the Visible property:
206 V B A D E V E L O P E R S G U I D E
T O P P R O P E R T Y
Top property
Description The Top property specifies the vertical position of the grid relative to the
bottom border of the windows title bar.
integer The distance from the top of the grid to the bottom of the windows
title bar, measured in pixels. The top of the grid includes any of the fields,
prompts and controls associated with the grid.
Comments The Top property will move the grid and any of the fields, prompts and
controls associated with the grid. These items are associated with the grid if
theyre no further than one pixel from the grids border, or if theyre one
pixel from the edge of another associated item.
Examples The following example changes the top position of the grid so it now
appears below a new field, Sort By State, in the Customers and Prospects
lookup window:
Visible property
Description The Visible property specifies whether a grid is visible.
Comments While invisible, data in a grid is still accessible. Use this property when you
want to reference data displayed in the grid without making the grid
visible. You can also make a grid invisible using the Hide method.
Examples The following example hides the grid in the Sales Transaction Entry
window based on the current user:
208 V B A D E V E L O P E R S G U I D E
Chapter 11: Report Object
Your project uses the report object to perform several tasks related to
working with reports. The events, methods and properties that apply to the
report object are listed below. A detailed explanation of each follows:
BeforeAF event
BeforeAH event
BeforeBody event
BeforePF event
BeforePH event
BeforeRF event
BeforeRH event
Cancel method
End event
EventMode property
Legend property
Name property
Start event
BeforeAF event
Description The BeforeAF event occurs before each instance of the specified additional
footer prints.
By Val Level As Integer The index of the additional footer the event will
occur for. Since a report may have more than one additional footer
(indicated by F1, F2 and so on), use the Level parameter to specify which
additional footer you want the event to occur for.
Comments Use the BeforeAF event to reference fields an additional footer. A report
may have none, one or several additional footers. Each prints when the data
in the field it is based on changes. Microsoft Dynamics GP uses additional
footers to display summary data, such as a total of all records in the reports
body.
If the report has only one additional footer, the BeforeAF event occurs only
for that additional footer, and you dont need to set a value for the Level
parameter.
Examples In the following example, the BeforeAF event procedure sets a user-defined
calculated field in the second additional footer:
210 V B A D E V E L O P E R S G U I D E
B E F O R E A H E V E N T
BeforeAH event
Description The BeforeAH event occurs before each instance of the specified additional
header prints.
By Val Level As Integer The index of the additional header the event will
occur for. Since a report may have more than one additional header
(indicated on the report layout by H1, H2 and so on), use the Level
parameter to specify which additional header you want the event to occur
for.
Examples In the following example, the BeforeAH event procedure sets the Level
parameter to 2, indicating that the procedure runs for the second additional
header on a report. The procedure then checks the value of the Checkbook
ID field in the second additional header:
BeforeBody event
Description The BeforeBody event occurs before each instance of the reports body
prints.
SuppressBand As Boolean If True, the current body record wont print, and
no events will occur for it. Other portions of the report wont be affected.
Comments Use the BeforeBody event to reference fields in a reports body. Microsoft
Dynamics GP uses the report body for the bulk of a report, which typically
is made up of table fields. A report prints the body repeatedly until all
records on the report have printed.
Examples The following example uses the BeforeBody event to return the value of the
Commissioned Sales field in the current body record of the RM Salesperson
Report. If a salespersons commissioned sales are less than $200,000, the
SuppressBand parameter stops the current body record from printing. The
result is that the report prints only records for salespeople whove exceeded
$200,000 in commissioned sales:
212 V B A D E V E L O P E R S G U I D E
B E F O R E P F E V E N T
BeforePF event
Description The BeforePF event occurs before the reports page footer prints.
SuppressBand As Boolean If True, the page footer wont print and no VBA
events will occur for it.
Comments Use the BeforePF event to reference fields in a reports page footer. Items in
the page footer are placed at the bottom of every report page. The page
footer often includes administrative information, such as the name of the
person running the report.
Examples In the following example, the BeforePF event sets a user-defined calculated
string field in the reports footer. It uses the VBA Time and Date functions
to compose a comment on the report:
BeforePH event
Description The BeforePH event occurs before the reports page header prints.
Comments Use the BeforePH event to reference fields in a reports page header. Items
in the page header are placed at the top of every report page. Page number,
date and time fields are commonly placed in this section of a report.
Examples The following BeforePH event procedure suppresses the page header
based on the current user ID:
214 V B A D E V E L O P E R S G U I D E
B E F O R E R F E V E N T
BeforeRF event
Description The BeforeRF event occurs before the reports report footer prints.
Comments Use the BeforeRF event to reference fields in the report footer. Summary
information is often included in the report footer. If a page footer is also
included on the last page, the report footer will print before the page footer.
Examples The following BeforeRF event procedure runs for the RM Transaction
Inquiry report. If the current user is LESSONUSER1, the procedure hides
two fields in the report footer:
BeforeRH event
Description The BeforeRH event occurs before the reports report header prints.
Comments Use the BeforeRH event to reference fields in the report header. Items in the
report header appear only on the first page of a report. If a page header is
also included on the first page, the report header will appear after the page
header.
Microsoft Dynamics GP typically uses fields in the report header that you
cant reference from the BeforeRH event; such as system variables that
show the page number, user ID and company name, and legend fields that
show the record ranges for the report. Microsoft Dynamics GP rarely uses
table fields or calculated fields in the report header.
216 V B A D E V E L O P E R S G U I D E
C A N C E L M E T H O D
Cancel method
Description The Cancel method cancels a report from a report event.
Syntax report.Cancel
Comments The Cancel method stops the report from printing. If canceled from a report
band event, data from that band will print before the report stops. If the
report prints to the screen, the Cancel method automatically closes the
Screen Output window. The End event occurs after you cancel a report
using the Cancel method.
End event
Description The End event occurs when a report finishes printing.
Syntax report_End()
Comments Use the End event to perform any clean-up of activities for the report,
launch other applications, or open and close other windows. The End event
will always run if a reports Start event occurs, including when a report is
canceled using the Cancel method.
Examples In the following example, the End event procedure launches Microsoft
Outlook after the RM Customer Report prints:
Events
The report objects Start event.
218 V B A D E V E L O P E R S G U I D E
E V E N T M O D E P R O P E R T Y
EventMode property
Description The EventMode property specifies whether report events occur for the
original or modified version of the report.
Constant Description
emOriginalOnly Report events occur only for the original version of the
report.
emModifiedOnly Report events occur only for the modified version of the
report.
emNever Report events dont occur for the report.
Comments If you modify a report using the Report Writer, you must set the
EventMode property to emModified if you want VBA events to occur for
the modified report. To change the EventMode property for the report
object, use the Visual Basic Properties window.
Examples Typically, you set the reports EventMode property using the Visual Basic
Properties window. You can also set it through an event procedure, as
shown in the following example:
Legend property
Description The Legend property specifies the value of a report legend.
Comments Legends are fields whose data is persistent throughout the report, and must
be passed to the report as it starts. You must use the Legend property in the
reports Start event to set or return the value of a legend field in a report.
Legend fields typically include information about the report, such as the
range of records the user chose for the report. The following illustration
shows legend fields in the RM Customer Report used to show the range of
customer records that print for the report.
You dont add a legend field to your project; instead, you use the Legend
propertys index parameter to refer to a specific legend field on the report.
Examples The following Start event procedure returns the value of the Customer
Class legend field using the fields index:
220 V B A D E V E L O P E R S G U I D E
N A M E P R O P E R T Y
Name property
Description The Name property specifies the internal name VBA uses to reference a
report object.
Syntax report.Name
Comments You cannot set the Name property at runtime. We recommend that you
change a reports Name property using the Visual Basic Properties window.
Be sure to replace all referenced occurrences of the report with the new
name.
Use the Name property to change the report name when you need to
resolve naming conflicts between reports and other objects in your
application. These conflicts typically arise between names in your project
and reserved words in VBA.
You can also use the Name property when you want to make an reports
name more readable in VBA code.
Examples To change a reports Name property, use the Visual Basic Properties
window.
Start event
Description The Start event occurs as the report starts to print, but before any data
actually prints.
Syntax report_Start()
Comments Use the Start event to set any report legends using the Legend property.
Legends are fields whose data is persistent throughout the report, and must
be passed to the report as it starts.
Examples The following example sets the value of a legend field from the Start event:
Events
The report objects End event.
222 V B A D E V E L O P E R S G U I D E
Chapter 12: VBAGlobal Object
Your project uses the VBAGlobal object to perform tasks related to the
Dynamic User Object Store (DUOS). It also provides a method to retrieve
the UserInfo object used to access information about the current user. The
methods that apply to the VBAGlobal object are listed below. A detailed
explanation of each follows:
DUOSObjectCombineID method
DUOSObjectExtractID method
DUOSObjectsGet method
UserInfoGet method
DUOSObjectCombineID method
Description The DUOSObjectCombineID method constructs a DUOS data object ID
using two string values.
Comments Data objects you define in the DUOS must have a unique object ID within a
collection. If you create a data object with an object ID that already exists in
the collection, the new data object will overwrite the existing object. To
avoid duplicate object IDs, create a unique ID using this method.
In most cases, you can assign the value of a Microsoft Dynamics GP control
field as the objects ID. A control field, such as a customer ID or a document
number, controls the display of a record in a window, and is a unique value
in the accounting system.
Examples The following procedure runs when the user clicks the Save button in the
Item Vendors Maintenance window. It uses the DUOSObjectsCombineID
method to combine the values of two window fields (Vendor ID and Item
Number) to construct a unique object ID:
224 V B A D E V E L O P E R S G U I D E
D U O S O B J E C T C O M B I N E I D M E T H O D
DUOSObjectExtractID method
Description The DUOSObjectExtractID method extracts two strings combined using
the DUOSObjectCombineID method.
Return value A boolean indicating whether the objectID was combined using the
DUOSObjectCombineID method. If True, objectID was combined.
Comments Use the DUOSObjectExtractID method to return the original string values
combined using the DUOSObjectCombineID method. Extracting an
object ID returns the original combined string values. This is useful if you
want to update DUOS data objects based on one of the original string
values.
226 V B A D E V E L O P E R S G U I D E
D U O S O B J E C T E X T R A C T I D M E T H O D
Examples The following example uses a button on a VBA user form to loop through a
collection named Vendors. It uses the DUOSObjectExtractID method to
check whether each data objects objectID is combined. Any object with a
combined objectID uses an ID composed of the vendors ID and the vendor
type. If the vendor type (ventype) is PREFERRED, the procedure updates
the properties for the data object:
DUOSObjectsGet method
Description The DUOSObjectsGet method returns a DUOS collection for the current
company.
Syntax DUOSObjectsGet(collection_name)
Comments Use the DUOSObjectsGet method to specify a collection in the DUOS and
assign it to a DUOSObjects variable. For instance, the following example
returns a collection named Customers:
DUOSObjectsGet("Items")
If the collection_name specified does not exist, this method creates the
collection with that collection_name.
DUOSObjectsGet("Employees")
228 V B A D E V E L O P E R S G U I D E
DUO SO B J E C T S G E T M E T H O D
UserInfoGet method
Description The UserInfoGet method returns a UserInfo object containing information
about the current user.
Syntax UserInfoGet(collection_name)
Parameters None
Examples The following example retrieves the UserInfo object, and then displays the
current companys name in a message box.
230 V B A D E V E L O P E R S G U I D E
Chapter 13: DUOSObjects Collection
Your project uses the DUOSObjects collection to perform several tasks
related to creating and maintaining a collection of objects in the Dynamic
User Object Store (DUOS). The methods and properties that apply to a
DUOSObjects collection are listed below. A detailed explanation of each
follows:
Exists property
Item property
Name property
Remove method
SortByExisting property
Exists property
Description The Exists property returns True if specified data object exists in a
collection.
Syntax DUOSObjects.Exists(objectID)
objectID The unique identifier for the data object in the collection.
Comments The Exists property is a read-only property. If the specified data object
exists, it will return True. Otherwise, it will return False.
Examples In the following example, the Changed event procedure runs for the Total
field when the user changes an invoice amount in the Invoice Entry
window. The procedure uses the Exists property to ascertain whether a
corresponding invoice object already exists in the collection. If it does exist,
the procedure updates it with the current system date using the VBA Date()
function:
232 V B A D E V E L O P E R S G U I D E
I T E M P R O P E R T Y
Item property
Description The Item property returns a data object from a collection.
Syntax DUOSObjects.Item(objectID)
objectID The unique identifier for the data object in the collection.
Comments The Item property is the default member of the DUOSObjects collection. If
you omit the Item property from your VBA code, the collection assumes
youre returning the data object for the specified collection. The following
example includes the Item property:
ItemCollection.Item("ITM001")
ItemCollection("ITM001")
Examples The following procedure runs when the user clicks a button in a VBA user
form. It returns a collection named Item Info, then uses the Item property to
return a data object with an objectID matching the Item Number in the Item
Maintenance window. The Set statement assigns the returned data object to
an object variable named ItemObject. The Remove method then deletes the
object returned by the Item property:
Name property
Description The Name property returns the name of a collection.
Syntax DUOSObjects.Name
Comments Use the Name property to identify the name of a collection object returned
by the DUOSObjectsGet method. You can use the Name property to
distinguish between multiple collections in the same procedure.
Examples The following procedure runs when the user clicks a button in a VBA user
form. The Name property returns the name of the collection object returned
by the DUOSObjectsGet method:
If CustomerMaintenance.IsLoaded Then
Set Collection = DUOSObjectsGet("Customers")
Else
Set Collection = DUOSObjectsGet("Items")
End If
'Display either "Customers" or "Items"
MsgBox "The current collection is " + Collection.Name
End Sub
234 V B A D E V E L O P E R S G U I D E
R E M O V E M E T H O D
Remove method
Description The Remove method deletes a data object from a collection.
Syntax DUOSObjects.Remove(objectID)
objectID The unique identifier for the data object in the collection.
Examples The following procedure runs when the user clicks a button in a VBA user
form. The procedure loops through a collection named Item Info and checks
the Item Color property for each item. If the value is Red, the collections
Remove method deletes the data object:
SortByExisting property
Description The SortByExisting property specifies the property by which the collection
sorts objects.
Comments Use the SortByExisting property to sort the objects in a collection by the
value of a given property. In the following example, the SortByExisting
property sorts the data objects in the Customers collection by the Date
Added property:
In this case, the items are sorted in ascending order starting with the most
recent date. If the property_name exists only for a subset of objects in the
collection, the collection will be restricted to that subset of objects. In the
following example, the SortByExisting method sorts by the URL Address
property:
236 V B A D E V E L O P E R S G U I D E
S O R T B Y E X I S T I N G P R O P E R T Y
Examples The following procedure runs when the user clicks a button added to the
Customer Maintenance window using the Modifier. It uses the
SortByExisting method to sort the CustomerCollection by the collections
Date Added property. It then creates a report file using the VBA Open
statement, and prints the objects ID, property names and property values
to the file using the VBA Print# statement. Only data objects that include
the Date Added property will be printed, and will be sorted by date:
ID property
Properties property
ID property
Description The ID property returns an object ID for a DUOS data object.
Syntax DUOSObject.ID
Examples The following procedure runs when the user clicks a button in a VBA user
form. The procedure loops through the collection named Item Info and
checks the Item Color property for each item. If the value is Red, the
collections Remove method deletes the object indicated by the ID
property:
240 V B A D E V E L O P E R S G U I D E
P R O P E R T I E S P R O P E R T Y
Properties property
Description The Properties property returns a collection of properties for a DUOS data
object.
Syntax DUOSObject.Properties
Comments You can use the Properties property in combination with the Item property
to specify a named property for the object. In most cases, this is the
preferred way to set or return a propertys value.
Examples The following procedure runs when the user clicks a button in a VBA user
form. This example returns a collection of properties for the current data
object:
Count property
Exists property
Item property
Remove method
Count property
Description The Count property returns the number of property objects in a properties
collection.
Syntax DUOSProperties.Count
Comments The Count property is useful when you want to perform any DUOS
maintenance activities, such as removing unwanted properties, or adding
properties to a data object if it doesnt have the correct number of
properties.
Examples The following example uses the Count property to return the number of
properties for a given data object. If the number of properties is greater than
2, the procedure loops through the collection, and uses the Remove method
to delete properties other than Item Weight and Item Color:
244 V B A D E V E L O P E R S G U I D E
E X I S T S P R O P E R T Y
Exists property
Description The Exists property returns True if a property object exists in a properties
collection.
Syntax DUOSProperties.Exists(property_name)
Examples The following procedure runs for a button in a VBA user form. It uses the
Exists property to check whether the Item Color property exists for a given
data object. If the property doesnt exist, the procedure creates it using the
collections Item property:
Item property
Description The Item property returns a property object from a properties collection.
Syntax DUOSProperties.Item(index)
index The property objects name (a string) or the position (an integer) of
the property object in the collection.
Comments If you reference the property position in the index, the position starts at 1 for
the first property in the data object. The order you added the property to
the data object determines the property objects position.
If you reference the property name in the index, and the name does not
exist, the Item property creates a property in the collection with that name.
ItemProperties.Item("Item Color")
ItemProperties("Item Color")
246 V B A D E V E L O P E R S G U I D E
I T E M P R O P E R T Y
Examples The following procedure runs when the user clicks a button in a VBA user
form. It loops through a properties collection and uses the Item property to
check the property named Item Weight:
In this example, the Item property references the position of the property
rather than the name:
Remove method
Description The Remove method deletes a specified property object from a properties
collection.
Syntax DUOSProperties.Remove(index)
index The property objects name (a string) or the position (an integer) of
the property object in the collection.
Examples The following procedure runs when the user clicks a button in a VBA user
form. It removes a property object from a properties collection using the
Remove method:
248 V B A D E V E L O P E R S G U I D E
Chapter 16: DUOSProperty Object
Your project uses the DUOSProperty object to perform tasks related to a
property for an object in the Dynamic User Object Store (DUOS). The
properties that apply to the DUOSProperty object are listed below. A
detailed explanation of each follows:
Name property
Value property
Name property
Description The Name property specifies the name of a property for a data object.
Syntax DUOSProperty.Name
Examples The following procedure runs when the user clicks a button in a VBA user
form. It uses the Name property with the DUOSProperties Remove
method to delete properties for a data object other than Item Weight and
Item Color:
250 V B A D E V E L O P E R S G U I D E
V A L U E P R O P E R T Y
Value property
Description The Value property specifies the value of a property object.
Comments When you set the value of a property object using the Value property, you
cannot exceed 132 characters. The DUOS stores and returns property values
as strings.
ItemProperty.Value = "Red"
ItemProperty = "Red"
Examples The following example runs when the user clicks a button in a VBA user
form. The procedure loops through a collection named Item Info and uses
the Value property to set the value of the Item Color property for all data
objects:
252 V B A D E V E L O P E R S G U I D E
Chapter 17: UserInfo Object
The UserInfo object contains information about the user currently logged
into Microsoft Dynamics GP. The properties and method that apply to the
UserInfo object are listed below. A detailed explanation of each follows:
CompanyName property
CreateADOConnection method
IntercompanyID property
UserDate property
UserID property
UserName property
CompanyName property
Description The CompanyName property contains the name of the company that the
user is currently logged into in Microsoft Dynamics GP.
Syntax UserInfo.CompanyName
Examples The following example retrieves and displays the name of the company the
user is currently logged into.
254 V B A D E V E L O P E R S G U I D E
C R E A T E A D O C O N N E C T I O N M E T H O D
CreateADOConnection method
Description The CreateADOConnection method creates a new ADO connection to the
database.
Syntax UserInfo.CreateADOConnection()
Examples The following example creates an ADO connection and issues a command
to retrieve all of the customer records from the RM00101 table in the current
companys database. Note how the IntercompanyID property of the
UserInfo object is used to set the default database for the ADO connection.
IntercompanyID property
Description The IntercompanyID property contains the internal ID of the company that
the user is currently logged into in Microsoft Dynamics GP.
Syntax UserInfo.IntercompanyID
Comments The value of the IntercompanyID property is the same as the name of the
SQL database used for that company.
Examples The following example retrieves and displays the intercompany ID for the
company the user is currently logged into.
256 V B A D E V E L O P E R S G U I D E
U S E R D A T E P R O P E R T Y
UserDate property
Description The UserDate property contains the user date currently set in Microsoft
Dynamics GP.
Syntax UserInfo.UserDate
Examples The following example retrieves and displays the user date currently set in
Microsoft Dynamics GP.
UserID property
Description The UserID property contains the user ID value for the user currently
logged into Microsoft Dynamics GP.
Syntax UserInfo.UserID
Comments The value of the UserID property is the same as the users SQL login.
Examples The following example retrieves and displays the user ID for the user
currently logged into Microsoft Dynamics GP.
258 V B A D E V E L O P E R S G U I D E
U S E R N A M E P R O P E R T Y
UserName property
Description The UserName property contains the display name for the user currently
logged into Microsoft Dynamics GP.
Syntax UserInfo.UserName
Examples The following example retrieves and displays the name of the user
currently logged into Microsoft Dynamics GP.
262 V B A D E V E L O P E R S G U I D E
Appendix A: VBA Errors
The information presented here explains the runtime errors that you can
encounter using VBA with Microsoft Dynamics GP. It is divided into the
following sections:
Refer to the VBA online In the following example, the Resume Next clause of the On Error
help for a comprehen- statement allows the procedure to skip the statement that generates an
sive explanation of the error. In this case, setting the Shipping Method field will generate an error
On Error statement (VBA error 1007) since NEW isnt an existing shipping method. However,
and other error han- the procedure continues to run, and the final statement sets the Trade
dling techniques. Discount field:
To access online help for these VBA errors, press the Help button in the error dialog
when the error occurs.
264 V B A D E V E L O P E R S G U I D E
A PP E N D I X A V B A E R R O R S
You set the caption for a field that didnt previously have a caption.
Solution:
If youre using the Caption property to set a fields caption, be sure the
field has an existing caption.
If youre performing other tasks and receive this error, Microsoft Dynamics
GP may be running out of memory. Shut down applications that arent
necessary, then restart the accounting system. If the error persists, contact
Technical Support.
Solution:
To reference a window field in VBA code, the window must be open (its
IsLoaded property is True). The window does not need to be visible to
reference the window field.
When you reference a window field from outside the current window, its
good practice to check the window objects IsLoaded property to ascertain
whether the window containing the field is open. The following procedure
checks if the window is open using the IsLoaded property. If it is, the
procedure sets value of a window field:
Private Sub
If CustomerMaintenance.IsLoaded Then
'Reference a window field
CustomerMaintenance.ShippingMethod = "UPS BLUE"
End If
End Sub
Error 1003 Unsafe operation. This operation could compromise the integrity of
the application.
Situation:
You attempted to enable a field disabled by the accounting system, unlock a
field locked by the accounting system, make visible a field made invisible
by the accounting system, or make unrequired a field marked as required
by the accounting system. To preserve data integrity, Microsoft Dynamics
GP application code overrides any attempt you make through VBA to
change properties for disabled, locked, hidden or required fields.
Solution:
If Microsoft Dynamics GP application code disables, locks, hides or requires
a field, it may impact how your VBA code works with the field.
The following table explains some general guidelines for these properties:
Property Description
Enabled If the accounting system disables a field (its Enabled property is
False), you cannot enable it or set its value using VBA.
Locked If the accounting system locks a field, you cannot unlock it (set its
Locked property to False) or set its value using VBA.
Visible If the accounting system hides a field, you cannot make the field
visible (set its Visible property to True) or set its value using VBA.
Required If the accounting system requires a field, you cannot make the field
unrequired (set its Required property to False).
266 V B A D E V E L O P E R S G U I D E
A PP E N D I X A V B A E R R O R S
Solution:
You cannot set the value of a read-only property.
Error 1005 Unsafe operation. This operation can only be performed in the
BeforeGotFocus or AfterGotFocus events of the target field. The operation could
compromise the integrity of the application.
Situation:
You attempted to set the value of a composite field using the ValueSeg
method from an event other than the composite fields BeforeGotFocus or
AfterGotFocus events.
Solution:
When setting a composite field using the ValueSeg property, set the
composite from only the BeforeGotFocus or AfterGotFocus events for the
field. When a composite gains focus, Microsoft Dynamics GP application
code validates the value of the composite, segment by segment. Since the
accounting system can perform this validation only after segments values
have been set, you must set segment values using the ValueSeg property in
events that precede the validation (the BeforeGotFocus and AfterGotFocus
events).
Error 1006 Unsafe operation. This operation cannot be performed in the target
fields AfterUserChanged, BeforeLostFocus or AfterLostFocus events. This
operation could compromise the integrity of the application.
Situation:
You attempted to set a fields value using the fields AfterUserChanged,
BeforeUserChanged or AfterLostFocus events. These events follow the
Microsoft Dynamics GP user changed event. Microsoft Dynamics GP uses
the user changed event to verify the value in a field; setting the value of the
field after the Microsoft Dynamics GP user changed event does not allow
the accounting system to verify the field.
Solution:
Set the fields value using the BeforeGotFocus, AfterGotFocus or
BeforeUserChanged events. These events precede the Microsoft Dynamics
GP user changed event, and allow the accounting system to verify the
contents of the field.
Error 1007 Unsafe operation. An attempt was made to set a value that violates
the applications business logic. This operation could compromise the integrity of
the application.
Situation:
You attempted to set a fields value from outside the field (before the field
gains focus), such as by using the windows BeforeOpen or AfterOpen
event, or another fields BeforeUserChanged or AfterUserChanged event.
When you set the value of a field before the field gains focus, VBA
automatically runs the Microsoft Dynamics GP user changed event for the
field youre setting. This is necessary so any accounting system application
code associated with these events can verify the fields value. Although
Microsoft Dynamics GP doesnt perform this verification for all fields, they
will perform verification for fields that affect business logic (such as an
invoice discount percent, or a tax amount) or for add-on-the-fly fields. If the
accounting system determines that the fields value is invalid, it will first
display its error dialog, followed by the VBA error dialog.
268 V B A D E V E L O P E R S G U I D E
A PP E N D I X A V B A E R R O R S
Solution:
There are three ways to avoid this type of error:
Whenever possible, set a fields value from inside the field, after it
has gained focus, using the fields AfterGotFocus or BeforeGotFocus
field events. If your event procedure sets an invalid value, the account-
ing system will still display a dialog, but VBA wont generate the error:
If you set the value from outside the field, before the field has gained
focus, use the Focus method with the setvalue parameter in your event
procedure. The Focus method moves the focus to the field, then sets it
to the value of the setvalue parameter:
Only set fields from outside the field if you know the accounting sys-
tem isnt performing field verification. Microsoft Dynamics GP per-
forms verification for fields that affect business logic (such as an invoice
discount percent, or a tax amount) or for add-on-the-fly fields. You can
set the value of add-on-the-fly fields only if the add-on-the-fly value
already exists as a record (such as an existing shipping method).
Error 1008 Unsafe Operation. An attempt was made to set a value into an
application field that is disabled. This operation could compromise the integrity of
the application.
Situation:
You attempted to set the value of a field that was locked or disabled by the
accounting system.
Solution:
If Microsoft Dynamics GP application code disables or locks a field (the
fields Enabled property is False, or its Locked property is True), you
cannot set its value. The accounting system disables fields when they dont
apply in a given situation, and locks fields to preserve the integrity of
accounting data (such as document totals). However, you can set the value
of fields youve disabled or locked through VBA.
Error 80040208 This operation cannot be performed while the window is firing
events.
Situation:
You attempted to use the PullFocus method with a window while VBA
window events or window field events are occuring.
Solution:
The PullFocus method forces a window to lose the focus and should only
be used in windows where no pending VBA window events or field events
are occuring. VBA events rely on the focus being present to run properly.
270 V B A D E V E L O P E R S G U I D E
Glossary Check box field
A field that to allows a user to mark or
Editable grid
A scrolling window that allows the user
unmark a selection. In VBA, the value 1 to edit items in the window.
Additional headers and footers indicates a marked item; a 0 indicates an
Report bands that are printed when a unmarked item. Event
specified field changes. An action for a given object where
Combo box field associated application code (an event
Adds-allowed grid A list field that allows a user to enter a procedure) runs.
A grid that allows the user to enter and text value or choose that value from a list.
save new information using the grid. In VBA, a string value specifies an item in Event procedure
a combo box. VBA code that executes when a given
Ask dialog event occurs.
A modal dialog box that contains one or Composite field
more buttons, allowing the user to make A field made up of multiple segments. Field
a selection, and displays message text. Microsoft Dynamics GP account numbers A field contains a single piece of
are composite fields. information. A field can be displayed in a
Band window or on a report. The type of
A section of a report, such as the body, Concatenate information displayed depends on the
report header, report footer or a page To connect two or more strings to form a fields type.
header. single string.
Focus
Band events Control field The indicator that shows the object being
A VBA event that occurs just before a A unique field in a window thats used to controlled in the current window.
report band prints. control access to a record. In Microsoft
Dynamics GP, control fields are unique Form
Browse-only grid values, such as document numbers or In applications like Microsoft Dynamics
A grid that allows the user to browse customer IDs. GP, a group of windows that function
through items in the window, but not together for a common purpose.
make any changes. Currency field
A field that allows the user to enter a Format
Button drop list field currency amount. The extra characters, spacing and
A button field that drops to allow a attributes that can be applied to a field
user to select one item from a list of Disabled field when data is entered or displayed.
values. VBA uses a numeric value to A field that is unavailable to or cannot be
identify the selected item in the list. chosen by a user. A disabled button or Forms dictionary
field appears dimmed. The dictionary that stores user-modified
Calculated field resources for Microsoft Dynamics GP.
A report field containing an expression Drop-down list field This dictionary is created when the
that combines fields in a reports table, A field that drops to allow users to Modifier is accessed for the first time.
report fields, constants, functions and select one item from a list. VBA uses a
operators. You can set or return the value numeric value to identify the selected Getstring dialog
of user-defined calculated fields. You can item in the list. A modal dialog that contains a data entry
only return the value of Microsoft field and OK and Cancel buttons.
Dynamics GP calculated fields. DUOS
An acronym for Dynamic User Object Grid
Caption Store. The DUOS lets you use VBA to A window used to display information
A field caption is a text label that shows create, store and retrieve user-definable from a table (also known as a scrolling
the user the information thats displayed data objects. window). Grids allow the user to scroll
or that can be entered in the through records in the table. There are
corresponding window field. A window Editable field three types of grids: browse-only,
caption is the title of the window. A field whose contents can be changed editable and adds-allowed.
by the user.
272 V B A D E V E L O P E R S G U I D E
G L O S S A R Y
276 V B A D E V E L O P E R S G U I D E
I N D E X
278 V B A D E V E L O P E R S G U I D E
I N D E X
280 V B A D E V E L O P E R S G U I D E
I N D E X
282 V B A D E V E L O P E R S G U I D E