Flexibility on ES 5

[pronet74]

Since there is no documentation on Flex ES and things have changed from progression flex I was hoping maybe one of you might help me.

What I am doing is building vba code so that it puts the login username into the userdef_fld_1 everytime they make an order entry. It's to keep track on who made the order. This works great, however if someone else 'changes' the order entry, the code will still run.

I need to know when the screen is in 'add' mode and when it's in 'change' mode so that the person who 'changes' the record will not get credit for the order entry.

This is why I need to know about the .mode property. It no longer exists in ES flex. There is a formmode property in ES Flex, however printing out this variable while the OE screen is in add or change mode just shows a null value.

 

[abaldwin]

I have a couple of suggestions for you.

I just did this very piece of code for a client and I used method 1 below.


Method 1
Unhide the user defined field 1 and its label.
Change the text of the label to whatever you desire. (this becomes the name of the text box you will have access to.

Create a global variable in a module, on the got focus event of the user def fld set the global variable to equal whatever is in the text box .

On the change event of the form check:
If globalvariable <> "" and textbox.text <> "" and globvarialbe <> textbox.text then
textbox.text = global variable. (this in effect keeps a change from occuring

else
textbox.text = macfor.conninfo.username (or whatever the real property is, I forget exactly at the moment)

end if

Method 2 if you really desire to look at the mode is to parse the me.caption which should give you the full caption showing in the order entry window. If it contains Change or Add then do your code accordingly. I believe that in Add mode it says add and in change it says change.

Andy



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[pronet74]

I did it another way. I coded some Window API calls to read the application title. If it was '001 Enter Orders - [Change]' then I knew they were in change mode. Here is the complete code:

So what this does is grab the login username (from windows) since Macola uses that name If they are not then upon hitting 'Order Type' it checks to see if the order number variable has a number it in. It also checks to see if the user is in change mode. If it's not in change mode then it knows the last order was an addition and to add the username to the user_def_field_1.


'///////// Goes in OE Screen \\\\\\\\\\\\

Private Type USER_INFO_2
usri2_name As Long
usri2_password As Long ' Null, only settable
usri2_password_age As Long
usri2_priv As Long
usri2_home_dir As Long
usri2_comment As Long
usri2_flags As Long
usri2_script_path As Long
usri2_auth_flags As Long
usri2_full_name As Long
usri2_usr_comment As Long
usri2_parms As Long
usri2_workstations As Long
usri2_last_logon As Long
usri2_last_logoff As Long
usri2_acct_expires As Long
usri2_max_storage As Long
usri2_units_per_week As Long
usri2_logon_hours As Long
usri2_bad_pw_count As Long
usri2_num_logons As Long
usri2_logon_server As Long
usri2_country_code As Long
usri2_code_page As Long
End Type

Private Declare Function apiNetGetDCName _
Lib "netapi32.dll" Alias "NetGetDCName" _
(ByVal servername As Long, _
ByVal DomainName As Long, _
bufptr As Long) As Long

' function frees the memory that the NetApiBufferAllocate
' function allocates.
Private Declare Function apiNetAPIBufferFree _
Lib "netapi32.dll" Alias "NetApiBufferFree" _
(ByVal buffer As Long) _
As Long

' Retrieves the length of the specified wide string.
Private Declare Function apilstrlenW _
Lib "kernel32" Alias "lstrlenW" _
(ByVal lpString As Long) _
As Long

Private Declare Function apiNetUserGetInfo _
Lib "netapi32.dll" Alias "NetUserGetInfo" _
(servername As Any, _
username As Any, _
ByVal level As Long, _
bufptr As Long) As Long

' moves memory either forward or backward, aligned or unaligned,
' in 4-byte blocks, followed by any remaining bytes
Private Declare Sub sapiCopyMem _
Lib "kernel32" Alias "RtlMoveMemory" _
(Destination As Any, _
Source As Any, _
ByVal Length As Long)

Private Declare Function apiGetUserName Lib _
"advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, _
nSize As Long) _
As Long


Private Const MAXCOMMENTSZ = 256
Private Const NERR_SUCCESS = 0
Private Const ERROR_MORE_DATA = 234&
Private Const MAX_CHUNK = 25
Private Const ERROR_SUCCESS = 0&

Public FormMode As Variant
Public varfGetUserName As Variant

Private Function fGetUserName() As String
' Returns the network login name
Dim lngLen As Long, lngRet As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngRet = apiGetUserName(strUserName, lngLen)
If lngRet Then
varfGetUserName = Left$(strUserName, lngLen - 1)
End If
End Function

Private Sub Enter_UserName()
Dim username_sql As Variant
Dim AccessMacola As New ADODB.Connection
Dim AccessConnStr As Variant

Call fGetUserName
AccessConnStr = "dsn=AccessToSQLHQ"
If AccessMacola.State = adStateOpen Then AccessMacola.Close

AccessMacola.Open AccessConnStr

username_sql = "UPDATE oeordhdr_sql SET oeordhdr_sql.user_def_fld_1 = '" & varfGetUserName & "' WHERE (((oeordhdr_sql.ord_no)=" & OrdNo & "))"

AccessMacola.Execute username_sql, dbFailOnError
End Sub

Private Sub macForm_Close()
'Add code so time is given so Enter_UserName can run
For i = 1 To 30000
Next i

End Sub


Private Sub Order_LoseFocus(AllowLoseFocus As Boolean)
OrdNo = Trim(Order.Text)
Call ScreenStatus 'Check to see if screen is in change mode
End Sub
Private Sub Type_GotFocus()
If OrdNo <> "" Then
If booIsThisOpen = False Then 'Screen is not in change mode so add UserName to Order
Enter_UserName
OrdNo = ""
End If
End If
End Sub

'////////////// Goes in Module1 \\\\\\\\\
Global OrdNo As Variant

Declare Function EnumWindows Lib "user32.dll" (ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long
Declare Function GetWindowTextLength Lib "user32.dll" Alias "GetWindowTextLengthA" (ByVal hwnd As Long) As Long
Declare Function GetWindowText Lib "user32.dll" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Declare Function GetActiveWindow Lib "user32.dll" () As Long
Declare Function SendMessage Lib "user32.dll" Alias "SendMessageA" (ByVal hwnd As Long, ByVal Msg As Long, wParam As Any, lParam As Any) As Long

Global booIsThisOpen As Boolean
Global strIsThisopen As String

Public Function EnumWindowsProc(ByVal hwnd As Long, ByVal lParam As Long) As Long

On Error Resume Next

Dim slength As Long, TitleBar As String
Dim retval As Long
Static winnum As Integer

booIsThisOpen = False
winnum = winnum + 1
slength = GetWindowTextLength(hwnd) + 1

If slength > 1 Then
TitleBar = Space(slength)
retval = GetWindowText(hwnd, TitleBar, slength)
If InStr(TitleBar, strIsThisopen) Then
booIsThisOpen = True
Exit Function
End If
End If

EnumWindowsProc = 1

End Function

Public Function ScreenStatus()

'Checks to see if screen is in Change mode.
strIsThisopen = "001 Enter Orders - [Change]"
Call EnumWindows(AddressOf EnumWindowsProc, 0)

End Function



My other problem now is when a user change the ship date on one particular item in an order entry I want it to ask the user if they want all the dates changed. Some of our orders can reach over 100 line items. I had this working perfectly in progression however Flex ES listbox how now been changed to 'listview'. There is no 'Count' property on this listview! So I have NO idea how to count how many line items there are in the order in order to change them.

In progression I was simulating keystrokes. I just might have to do an update query on that oeordlin_sql table.

Flexibility especially in ES, SUCKS! There is NO documentation, this coming directly from Exact Software when I called them about this.

 

[abaldwin]

Well your code seems to work but I think it is a bit much over the 6 or so lines it took me to do this.

As for the date thing, I would use and update statement on the table, am not sure of what type of locks may be on existing records but you could always do it on exiting the current order.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[pronet74]

Yep.. just completed it. It will now ask you as well if you want to change all the ship dates when you change one line item. For anyone interested in the whole code and save yourself some $$ from having someone else do it, here it is:

////////////////// OE Code \\\\\\\\\\\\\\\\\\\\

Private Type USER_INFO_2
usri2_name As Long
usri2_password As Long ' Null, only settable
usri2_password_age As Long
usri2_priv As Long
usri2_home_dir As Long
usri2_comment As Long
usri2_flags As Long
usri2_script_path As Long
usri2_auth_flags As Long
usri2_full_name As Long
usri2_usr_comment As Long
usri2_parms As Long
usri2_workstations As Long
usri2_last_logon As Long
usri2_last_logoff As Long
usri2_acct_expires As Long
usri2_max_storage As Long
usri2_units_per_week As Long
usri2_logon_hours As Long
usri2_bad_pw_count As Long
usri2_num_logons As Long
usri2_logon_server As Long
usri2_country_code As Long
usri2_code_page As Long
End Type

Private Declare Function apiNetGetDCName _
Lib "netapi32.dll" Alias "NetGetDCName" _
(ByVal servername As Long, _
ByVal DomainName As Long, _
bufptr As Long) As Long

' function frees the memory that the NetApiBufferAllocate
' function allocates.
Private Declare Function apiNetAPIBufferFree _
Lib "netapi32.dll" Alias "NetApiBufferFree" _
(ByVal buffer As Long) _
As Long

' Retrieves the length of the specified wide string.
Private Declare Function apilstrlenW _
Lib "kernel32" Alias "lstrlenW" _
(ByVal lpString As Long) _
As Long

Private Declare Function apiNetUserGetInfo _
Lib "netapi32.dll" Alias "NetUserGetInfo" _
(servername As Any, _
username As Any, _
ByVal level As Long, _
bufptr As Long) As Long

' moves memory either forward or backward, aligned or unaligned,
' in 4-byte blocks, followed by any remaining bytes
Private Declare Sub sapiCopyMem _
Lib "kernel32" Alias "RtlMoveMemory" _
(Destination As Any, _
Source As Any, _
ByVal Length As Long)

Private Declare Function apiGetUserName Lib _
"advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, _
nSize As Long) _
As Long


Private Const MAXCOMMENTSZ = 256
Private Const NERR_SUCCESS = 0
Private Const ERROR_MORE_DATA = 234&
Private Const MAX_CHUNK = 25
Private Const ERROR_SUCCESS = 0&

Public FormMode As Variant
Public varfGetUserName As Variant
Public ChangeAllShipDates As Variant
Public ShipDateChange As Variant
Public DateBefore As Variant
Public DateAfter As Variant

Private Function fGetUserName() As String
' Returns the network login name
Dim lngLen As Long, lngRet As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngRet = apiGetUserName(strUserName, lngLen)
If lngRet Then
varfGetUserName = Left$(strUserName, lngLen - 1)
End If
End Function

Private Sub Enter_UserName()
Dim username_sql As Variant
Dim AccessMacola As New ADODB.Connection
Dim AccessConnStr As Variant

Call fGetUserName
AccessConnStr = "dsn=AccessToSQLHQ"
If AccessMacola.State = adStateOpen Then AccessMacola.Close

AccessMacola.Open AccessConnStr

username_sql = "UPDATE oeordhdr_sql SET oeordhdr_sql.user_def_fld_1 = '" & varfGetUserName & "' WHERE (((oeordhdr_sql.ord_no)=" & OrdNo & "))"

AccessMacola.Execute username_sql, dbFailOnError

End Sub

Private Sub ListView_GotFocus()
'Call MsgBox("ListView has Focus", vbOKOnly)

End Sub

Private Sub macForm_Close()
'Add code so time is given so Enter_UserName can run
For i = 1 To 30000
Next i

End Sub


Private Sub Order_LoseFocus(AllowLoseFocus As Boolean)
OrdNo = Trim(Order.Text)
Call ScreenStatus 'Check to see if screen is in change mode
End Sub



Private Sub ReqShip_GotFocus()
DateBefore = ReqShip.Text

End Sub


Private Sub ReqShip_LoseFocus(AllowLoseFocus As Boolean)

DateAfter = ReqShip.Text

Call ScreenStatus 'Check to see if screen is in change mode
If booIsThisOpen = True And DateBefore <> DateAfter Then
msganswer = MsgBox("Do you wish to change all line items to the changed date?", vbYesNo)
ShipDateChange = ReqShip.Text

If msganswer = 6 Then
ChangeAllShipDates = True
Else
ChangeAllShipDates = False
End If

End If

End Sub


Private Sub Type_GotFocus()
Dim username_sql As Variant
Dim AccessMacola As New ADODB.Connection
Dim AccessConnStr As Variant

If OrdNo <> "" Then
If booIsThisOpen = False Then 'Screen is not in change mode so add UserName to Order
Enter_UserName

ElseIf booIsThisOpen = True And ChangeAllShipDates = True Then 'User changed Ship Date and wants them all to change


Call fGetUserName
AccessConnStr = "dsn=AccessToSQLHQ"
If AccessMacola.State = adStateOpen Then AccessMacola.Close

AccessMacola.Open AccessConnStr

updateshipdate_sql = "UPDATE oeordlin_sql SET oeordlin_sql.req_ship_dt = '" & ShipDateChange & "' WHERE (((LTrim([ord_no]))=" & OrdNo & "))"

AccessMacola.Execute updateshipdate_sql, dbFailOnError
End If
ChangeAllShipDates = False
OrdNo = ""
End If
End Sub


///////////////// MODULE1 CODE \\\\\\\\\\\\
Global OrdNo As Variant


Declare Function EnumWindows Lib "user32.dll" (ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long
Declare Function GetWindowTextLength Lib "user32.dll" Alias "GetWindowTextLengthA" (ByVal hwnd As Long) As Long
Declare Function GetWindowText Lib "user32.dll" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Declare Function GetActiveWindow Lib "user32.dll" () As Long
Declare Function SendMessage Lib "user32.dll" Alias "SendMessageA" (ByVal hwnd As Long, ByVal Msg As Long, wParam As Any, lParam As Any) As Long

Global booIsThisOpen As Boolean
Global strIsThisopen As String

Public Function EnumWindowsProc(ByVal hwnd As Long, ByVal lParam As Long) As Long

On Error Resume Next

Dim slength As Long, TitleBar As String
Dim retval As Long
Static winnum As Integer

booIsThisOpen = False
winnum = winnum + 1
slength = GetWindowTextLength(hwnd) + 1

If slength > 1 Then
TitleBar = Space(slength)
retval = GetWindowText(hwnd, TitleBar, slength)
If InStr(TitleBar, strIsThisopen) Then
booIsThisOpen = True
Exit Function
End If
End If

EnumWindowsProc = 1

End Function

Public Function ScreenStatus()

'Checks to see if screen is in Change mode.
strIsThisopen = "001 Enter Orders - [Change]"
Call EnumWindows(AddressOf EnumWindowsProc, 0)

End Function


If you have any questions, just ask. I hope others contribute their ES Flex code so others may benefit.

 

[abaldwin]

One possible flaw is if a user is running ES under their name while another user is logged into windows. You will get the windows user name and not the actual macola user.

macfor.conninfo.user will give you the current signed in user.


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[stravis]

Hi Andy,
Under ES all user security is by windows authentication and there is no way to login as a different user than the current windows account. So your last concern is not an issue.
Scott Travis

http://www.infoSpring.net

 

[dgillz]

Scott,

Actually if you right click and select "run as" amd you know the user name and PW, you can do this.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[stravis]

Thanks Don,

I don't know why I never figured that out. A big old star for you and a thank you. This feature is very useful.

Scott Travis

http://www.infoSpring.net

 

[abaldwin]

Actually Scott, You can run as a different user. I do it all the time. Especially when you write code and link resources for all the other users. You need to sign in as the "owner" of the code to make modifications.


Check it out by right-clicking on the ES Icon and you will see "run as"

Andy

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[abaldwin]

Dang, Did not see your post Don and Scott. Sorry for the repetitious post.

Andy

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[MacolaHelp]

This is probably silly, but isn't there still an order audit trail in ES which tracks the maker and the modifier of the order & what changes they made? Granted, in progression the performance of the report stinks due to the lack of good keys or lousy programming, but we have that in version 7+ already.