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


Patrick Roth - Click for blog homepageThis is a reposting of an article Patrick Roth originally wrote on my Developing for Dynamics GP blog.

A 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 SubPrivate 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.CommandDim 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, http://opensource.org/licenses/ms-pl.html.)

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

3 thoughts on “VBA – Determining the company name selected in the Company Login window

  1. Hi Jeff
    To add a modal window to VBA you will need to use the keyboard shortcut keys as you will not be able to access the menus with the mouse.
    Ctrl-F11 to Add the Window to VBA
    Shift-F11 to Add the Fields to VBA
    Hope this helps
    David

    Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.