This 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.
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
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.
// 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.