Sunday, March 29, 2009

How to determine what company was selected from the Company drop-down on the Company Login window

Background

This question came up in a recent Dynamics GP newgroup post. A developer was trying to run some Visual Basic for Applications (VBA) code in response to the company selected by the user from the Company drop-down list, but was unsuccessful determining which company was selected by the user.



Challenges

From a customization and development perspective, the Company drop-down list presents the following challenges:

1) Different users may have access to different companies, therefore, the number of companies displayed by the drop-down may vary.

2) The company drop-down list does not store the company ID. It stores a positional value for the company record being displayed.

3) Companies are displayed in the drop-down by Company ID. However, due to challenge number 1, company IDs may present gaps in the sequence based on user access.

Solution

The following VBA code shows how to retrieve the company selected by the user based on the user access to the different company databases. The code executes a SQL statement against the system database when the DDL focus is lost. Since the user is already logged in into the system, it is possible to create an ADO connection with the CreateADOConnection() method.


' Created by Mariano Gomez, MVP
' Code is provided as is, with no warraties express or implied.
Private Sub Company_AfterLostFocus()
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

Dim objUsr As UserInfo
Dim userID As String

'Retrieve an ADO connection for the current user
Set cn = UserInfoGet.CreateADOConnection()

'Set the connection properties
cn.CursorLocation = adUseClient

'Set the current database, using the IntercompanyID property
cn.DefaultDatabase = "DYNAMICS"

'Create a command to select all customers
Set objUsr = VbaGlobal.UserInfoGet()
userID = objUsr.userID
With cmd
.ActiveConnection = cn
.CommandType = adCmdText
.CommandText = "SELECT B.CMPNYNAM FROM SY60100 A INNER JOIN SY01500 B ON A.CMPANYID = B.CMPANYID WHERE A.USERID = '" & userID & "' ORDER BY A.CMPANYID"
End With

Set rst = cmd.Execute

rst.MoveFirst
i = 1

While Not rst.EOF
If i = Company.Value Then
MsgBox "You've selected " & rst(0)
rst.MoveLast
End If
i = i + 1
rst.MoveNext
Wend

'Close the connection
cn.Close

End Sub


Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

1 comment:

moataz said...

thanks for help me :)