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