VBA - Determining the company name selected in the Company Login window

Patrick Roth - Click for blog homepageA question came up a while back where the user wanted to know what company the user was logging into in order to give them a message depending on the company selected.

The specific issue was that VBA cannot get the name of the company from the DDL (drop down list), it can only give you the position.  Also the position in the list doesn't have any bearing on the company id of the company displayed.  It is just an ordered list by alphanumeric sorting by the Company Name field.

The question wasn't specific to when exactly the user needed to see this message.  When initially I read the question and based on how it was posed, it seems they wanted to know right when the DDL was selected.  But it could have been when the OK button was pressed and it also could be just after they log in.

Given that the technique used would be different, I will present both solutions.

Situation 1

For this solution, we will assume that the programmer needs to know the company after the user logs into Dynamics GP in order to give them some information or take a specific action.

The easiest way to do this it to use a Window_AfterClose() event on the Company Login window (Switch_Company form in Dexterity) and use the UserInfoGet object to retrieve the company logged in.

In the VBA code sample below, before the window opens we see what the current company is.  We could either being logging into Dynamics GP for the first time or just switching companies.  And if switching companies, we might press Cancel or just re-select the same company.  So by checking the previous logged in company and comparing we only show our message if the company we are going to really changed.

VBA Code for Company Login window

 Option Explicit
Dim oldcompany As String

Private Sub Window_AfterOpen()
    oldcompany = UserInfoGet.CompanyName
End Sub

Private Sub Window_AfterClose()
    If oldcompany <> UserInfoGet.CompanyName Then
        MsgBox "You have just logged into the company " + UserInfoGet.CompanyName
    End If
End Sub

Situation 2

While the solution given for the first situation is OK if we want to let the user log in, it is possible that we might want to give a message before the GP login occurs.  Or potentially stop the user from being able to log into the company for some specific reason.

If that is the case, we would then need to know the selected company before the Company Login window was closed.  The only way to do that is to know the company name selected in the Company DDL.

Because we cannot directly read the name from the Company DDL field, we have to pull the information directly from SQL in the order that Dynamics is displaying it.  Then we'd know the company being selected and could act upon that.

VBA Code for Company Login window 2

 Option Explicit

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command

Dim CurrentUser As String

Private Sub Window_AfterOpen()
    Set cn = UserInfoGet.CreateADOConnection
    'Switch to DYNAMICS db as that is where our query will run and not the current company
    cn.DefaultDatabase = "DYNAMICS"
    CurrentUser = UserInfoGet.UserID
    cmd.ActiveConnection = cn
End Sub

Private Sub Window_AfterClose()
    Set cn = Nothing
    Set rs = Nothing
    Set cmd = Nothing
End Sub

Private Sub Company_AfterUserChanged()
    Dim sql As String
    Dim i As Integer

    sql = sql + "    select a.CMPANYID as CompanyNumber,b.CMPNYNAM as CompanyName from SY60100 a"
    sql = sql + "    join SY01500 b"
    sql = sql + "        on a.CMPANYID = b.CMPANYID"
    sql = sql + "   where a.USERID = '" & CurrentUser & "'"
    sql = sql + "       order by b.CMPNYNAM"

    cmd.CommandText = sql

    Set rs = cmd.Execute
    If Not (rs.EOF And rs.BOF) Then
        'Nice if we could just jump to the current record
        'but looks like we have to loop through them.
        'We start on the first record in the recordset
        'so loop n-1 times to get to the correct one.
        'We don't need to read the entire recordset,
        'just to the one we've selected.  It is Company - 1
        'because we start on the first record
        For i = 1 To Company - 1
            rs.MoveNext
        Next

        MsgBox "You have chosen company: " + Trim(rs(1).Value)
    End If
End Sub

 

Depending on your exact needs; either one of these solutions might work for you or at least get you started.

 

Best Regards,

Patrick

Developer Support

// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)