Working with field values

You return and set window field values using the Value property. If your code specifies the field name, but omits the Value property, VBA assumes you’re 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 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.

Using the ValueSeg property

The ValueSeg property specifies the value of a given segment in a composite field (typically, an account number field). This is useful when you want to evaluate only a single segment of the composite. The Value property can evaluate the value of the entire composite, but not a single segment.

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:

Private Sub CashAccount_AfterLostFocus()
	If CashAccount.ValueSeg(1) = "600" Then
	'This is an expense account
	'Set the maximum payables amount for this account
		Amount = "1000.00"
		Amount.Locked = True
	End If
End Sub

You can also use the ValueSeg property to set individual segments in a composite. However, there’s 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.

Private Sub Account_AfterGotFocus()
	CashAccount.ValueSeg(1) = "100"
	'Set the second segment
	CashAccount.ValueSeg(2) = "1100"
	'Move the focus to the third segment
	CashAccount.FocusSeg(3)
End Sub

The following event procedure has the same effect, but uses the Value property to set the composite’s value:

Private Sub Account_AfterGotFocus()
	CashAccount.Value = "100-1100"
	CashAccount.FocusSeg (3)
End Sub

Using the Empty property

The Empty property specifies whether a field’s value is empty. If you set a field’s Empty property to True, VBA clears the field. Using the Empty property in this manner is the same as setting the field’s Value property to a null value (""). The following example uses the Empty property to automatically open a lookup window if the Salesperson ID field is empty:

Private Sub SalespersonID_AfterGotFocus()
	If SalespersonID.Empty = True Then
		'Click the lookup button to display the lookup window
		LookupButton8 = 1
	End If
End Sub

Returning field values

You can use the Value property to return window field’s value, providing the window that contains the field is open (the window’s IsLoaded property is True). If the window is open but invisible, you can return field values from that window. If you’ve disabled, locked or hidden the field using VBA, you can still return the field’s value. If Microsoft Dynamics GP hides the field, you cannot return the field’s value.

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”.

Performing numeric calculations

Since all field values returned to VBA are strings, numeric calculations or comparisons won’t function properly unless you convert string values to the appropriate numeric data type first.

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 VBA’s numeric conversion functions prior to performing the comparison. In the following example, VBA’s CInt() function converts the ItemQuantity1 and ItemQuantity2 fields, resulting in a correct comparison:

If CInt(Quantity1) > CInt(Quantity2) Then
'Quantity1 (70) is now less than Quantity2 (100) 

Likewise, when performing a calculation using a Microsoft Dynamics GP value and a VBA value, you must convert the value from the accounting system before performing the calculation. For example, to calculate the number of days between a Microsoft Dynamics GP date value (Document Date) and the current system date returned using the VBA Date function, convert the Microsoft Dynamics GP date using the CDate() function:

Private Sub DocumentDate_Changed()
	Dim DaysOld As Integer
	If CDate(DocumentDate) < Date - 30 Then
		'The document is at least 30 days old
		DaysOld = Date - CDate(DocumentDate)
		MsgBox "This document is " + Str(DaysOld) + " days old." + _
		"Please post."
	End If
End Sub

The following table shows the VBA functions available for converting string values to a specific data type:

Setting field values

You can use the Value property to set a field’s value for any field in a Microsoft Dynamics GP window, providing the window that contains the field is open (the window’s IsLoaded property is True). If the window is open but invisible, you can set field values from that window. If you’ve disabled, locked or hidden the field using VBA, you can still set the field’s value. If the accounting system disables, locks or hides the field, you cannot set the field’s value.

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:

'Set a the Batch ID string field
BatchID = "DEBITMEMOS"
'Set the Document Date field
DocumentDate = "02/07/97"
'Set the Discount Amount currency field
DiscountAmount = "120.95"

Other fields, such as list fields, check boxes, push buttons and radio buttons, accept numeric values:

'Set the Document Type drop-down list to item 2
DocumentType = 2
'Mark the Hold check box
Hold = 1
'Push the Save button
Save = 1
'Select the first option in the Posting Type radio button group
PostingType = 0

Documentation Feedback

---