VBA User Information 1

[Borvik]

I'm trying to disable/hide some fields on a window based on the user class.

I know how to disable/hide the fields - and have successfully done that on the window startup, but I'm having trouble determining what the current users class is.

Does anyone know how to get the current users information if VBA so that comparisons could be done on it?

 

[macleod1021]

Borvik,

The SY40400 table has all of the Class Information in it. If you query that table against the user, it will return the Class of that user.

Leeland

 

[Borvik]

macleod1021 - Ordinarily that is what I would do - query the database - however, this is within the VBA that is provided by the customization options with Great Plains.

I suppose the question should rest - How do I get ANY user information for the currently logged in user (for the current session of GP), using VBA within GP?

In order to do the SQL query lookup I would still the the username to lookup and find the class, but I'm not sure how to pull that.

 

[Borvik]

Got it!

I had to add the Toolbar window, and then the UserID field from that window to VBA.

Then I just used the following code:

Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rstupdate As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim sqlstring As String
Dim UserID As String
Dim UserClass As String

Private Sub Window_AfterClose()
    ' Clear all variables used and close connections
    If rst.State = adStateOpen Then rst.Close
    If cn.State = adStateOpen Then cn.Close
End Sub

Private Sub Window_AfterOpen()
    cmd.CommandText = "SELECT USRCLASS FROM SY01400 WHERE USERID='" & UserID & "'"
    Set rst = cmd.Execute()
    UserClass = rst!USRCLASS
    If Trim(UserClass) = "SALES" Then
        Hold.Enabled = False
        Inactive.Enabled = False
    End If
End Sub

Private Sub Window_BeforeOpen(OpenVisible As Boolean)
    UserID = Toolbar.eEnterpriseUserID
    'replace YOURSERVERNAME with the name of the server you are connecting to
    'enter in the password necessary after password=
    With cn
        .ConnectionString = "Provider=SQLOLEDB;Data Source=YOURSERVERHERE;User Id=sa;Password=;Initial Catalog=DYNAMICS"
        .CursorLocation = adUseNone
        .Open
    End With
    cmd.ActiveConnection = cn       ' Establish a command object for read-only recordsets
    cmd.CommandType = adCmdText
End Sub

Hope that can help out someone else.

 

[winthropdc]

Borvik

Sorry, but part of your code is evil.

It is extremely bad to hard code a connection string including the 'sa' user's password.

This is what we have RetrieveGlobals.dll for. Using RetrieveGlobals also means you don't need to have the Toolbar added to VBA.

In v8.0 onwards it is no longer possible to add the toolbar via normal means. I actually have a toolbar.package exported from v7.50 which I import into v8.0 or v9.0 when I need to expose the old Toolbar and its fields to VBA.

Here is some code for implementing RetrieveGlobals and ADO. This is set up so you do not need to add references, but if you have added the reference to RetrieveGlobals and Active X Data Objects you can used the commented out code instead.

'Dim userinfo As New RetrieveGlobals.retrieveuserinfo
Dim userinfo As Object
Dim luserid As String
Dim lsqlpassword As String
Dim lintercompanyid As String
Dim lsqldatasourcename As String
Dim constring As String

'Dim cn As New ADODB.Connection
'Dim rst As New ADODB.Recordset
Dim cn As Object
Dim rst As Object
Dim sqlstring As String

' Create links without having reference marked
Set userinfo = CreateObject("RetrieveGlobals.retrieveuserinfo")
Set cn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

' RetrieveGlobals
lsqldatasourcename = userinfo.sql_datasourcename()
luserid = userinfo.retrieve_user()
lsqlpassword = userinfo.sql_password()
lintercompanyid = userinfo.intercompany_id()
'MsgBox (luserid & " " & lsqlpassword & " " & lintercompanyid & " " & lsqldatasourcename)

'Handle 'sa' password not being returned by RetrieveGlobals
If UCase(luserid) = "SA" And lsqlpassword = "" Then
lsqlpassword = InputBox("Please enter the password for user " & luserid & ":")
End If

' Create Connection String
constring = "Provider=MSDASQL" & _
";Data Source=" & lsqldatasourcename & _
";User ID=" & luserid & _
";Password=" & lsqlpassword & _
";Initial Catalog=" & lintercompanyid
'MsgBox constring

' ADO Connection
With cn
.ConnectionString = constring
.CursorLocation = adUseClient
.Open
End With

sqlstring = "SELECT USRCLASS FROM SY01400 WHERE USERID='" & luserid & "'"

' Pass through SQL
rst.Open sqlstring, cn, adOpenDynamic, adLockOptimistic, adCmdText
If Not (rst.EOF And rst.BOF) Then
UserClass = Trim(rst!USRCLASS)
Else
UserClass = ""
End If
rst.Close

If Trim(UserClass) = "SALES" Then
Hold.Enabled = False
Inactive.Enabled = False
Else
Hold.Enabled = True
Inactive.Enabled = True
End If

David Musgrave [MSFT]
Senior Development Consultant
Escalation Engineer
MBS Support - Asia Pacific

Microsoft Business Solutions

http://www.microsoft.com/BusinessSolutions

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.

 

[Borvik]

I figured it wasn't great to do it that way, but I wasn't sure how to grab the other information.

Thanks for showing that to me Dave - a great help!