Closing form with Flexibility 1

[Zbob]

I am trying to prevent users from changing status 4 orders.

I am using the following code, Is there anyway to close the form or exit?

Private Sub DiscPct1_GotFocus()
Dim statrs As Recordset
Dim statstmt As String
Dim ord As String
ord = macForm.No.Text

Call OpenMacolaConnection

Set statrs = New ADODB.Recordset

statstmt = "SELECT OEORDHDR.ORDER_STATUS FROM OEORDHDR " _
& " WHERE OEORDHDR.ORDER_NO= '" & ord & "'"

statrs.Open statstmt, Macola, adOpenforward, adLockReadOnly, adCmdText

If statrs(0) = 4 Then

MsgBox "Changes not Allowed This order is a Status 4"

macForm.macExit



End If

 

[stravis]

Hi ZBOB,

If you want to stop users from changing Status 4 orders, I would suggest stopping them when they attempt to enter the order. I have listed the complete code for this below. Please check my comments for important information about changes that I made to your code. One last thing to note before digging in to the code is that the MacForm.MacExit procedure rarely works. Usually you need to use the SendKeys "{ESC}" command to simulate the ESCAPE key. This command can be used to twice to completely close a screen. Anyway, here is the code.

' Begin Code Snippet
Private Declare Function SendMessage Lib "user32" _
    Alias "SendMessageA" _
    (ByVal hwnd As Long, _
    ByVal wMsg As Long, _
    ByVal wParam As Long, _
    lParam As Any) As Long
Private Const EM_SetSel = &HB1

Private Macola As New ADODB.Connection
Private m_bStatusChecked As Boolean
Private m_bForceEscape As Boolean

Private Sub No_LoseFocus(AllowLoseFocus As Boolean)
    If CheckStatus = "4" Then
        ' Notify User
        MsgBox "Changes not Allowed This order is a Status 4"
        
        ' Do not allow Macola to perform LoseFocus
        AllowLoseFocus = False
        
        ' Select the Text to facilitate editing
        Call SelectText(No)
     Else
        ' Set Global Pass Flag indicating the status has been checked
        ' Note: I call this sort of Boolean value a Global Pass Flag
        '       because it is a variable that is defined at the module
        '       level that is used to provide communication between
        '       multiple procedures in a Flexibility Project to indicate
        '       that an operation has already been performed.  We need this
        '       sory of flag because Flexibility does not "fire" all events
        '       under all circumstances, so we need to "trap" events in
        '       multiple event procedures.
        m_bStatusChecked = True
    End If
End Sub

Private Sub Date1_GotFocus()
    ' Check the value of the Global Pass Flag
    If m_bStatusChecked = False Then
        If CheckStatus = "4" Then
            MsgBox "Changes not Allowed This order is a Status 4"
            SendKeys "{ESC}"
        End If
    End If
End Sub

Private Function CheckStatus() As String
    Dim statrs As Recordset
    Dim statstmt As String
    Dim ord As String
    
    ' Macola stores Orders Numbers as 8 character Zero Filled Numbers,
    ' we need to format the number display on screen to make the database query work
    ' properly.
    ord = Right(String(8, "0") & Trim(macForm.No.Text), 8)
    
    ' Check to see if the database connection is open
    If Macola.State <> adStateOpen Then
        ' Open a database connection
        Call OpenMacolaConnection
    End If
    
    Set statrs = New ADODB.Recordset
    
    ' Use the _SQL Table Definitions, these tables are compatible with the
    ' latest versions of Macola
    statstmt = &quot;SELECT OEORDHDR_SQL.status FROM OEORDHDR_SQL &quot; _
            & &quot;WHERE OEORDHDR_SQL.Ord_No = '&quot; & ord & &quot;'&quot;

    statrs.Open statstmt, Macola, adOpenforward, adLockReadOnly, adCmdText
    
    If statrs.EOF = False Then
        ' Sets the Return Value
        CheckStatus = Trim(statrs(&quot;status&quot;))
    Else
        CheckStatus = &quot;&quot;
    End If
    
    ' Closes and Releases your Connection
    If statrs.State = adStateOpen Then statrs.Close
    Set statrs = Nothing
End Function

Private Sub OpenMacolaConnection()
    Dim sConnStr As String
    Dim sUID As String
    Dim sPwd As String
    
    ' Set User Name and Password
    sUID = &quot;SUPERVISOR&quot;
    sPwd = &quot;supervisor&quot;
    
    sConnStr = &quot;dsn=_Data_02_MSSQL&quot;
    
    Macola.Open sConnStr, sUID, sPwd
End Sub
    
   
Private Sub macForm_CloseForm()
    ' Explicitly Close and Release Your Connection
    If Macola.State = adStateOpen Then Macola.Close
    Set Macola = Nothing
End Sub


Private Sub No_GotFocus()
    ' Reset Global Pass Flag
    m_bStatusChecked = False
End Sub

Private Sub SelectText(ByRef TextBox As macEditBox)
    Dim iStrLen As Long
    iStrLen = Len(TextBox.Text)
    
    ' Send Windows API Message to TextBox to select all the text in the TextBox
    SendMessage TextBox.hwnd, EM_SetSel, 0, iStrLen
End Sub
' End Code Snippet

Scott
NEXO Systems, Inc.

 

[stravis]

If your intent was to simply prevent the user from adding, deleting, or modifying existing line items on orders with a status 4, you can use the following code.

Private m_bPerformedOperation As Boolean

Private Sub DiscPct1_GotFocus()
    ' Use this code if you wish to prevent changing existing items on an order
    If Status.Text = "1" Then
        If m_bPerformedOperation = False Then
            m_bPerformedOperation = True
            MsgBox "Changes not Allowed This order is a Status 4"
            SendKeys "{ESC}"
        Else
            SendKeys "{ESC}"
        End If
    End If
End Sub

Private Sub ItemNo_GotFocus()
    ' Use this code if you wish to prevent adding items to an order
    If Status.Text = "1" Then
        MsgBox "Changes not Allowed This order is a Status 4"
        SendKeys "{ESC}"
    End If
End Sub

Private Sub ListBox_GotFocus()
    m_bPerformedOperation = False
End Sub

Private Sub macForm_Delete(AllowDelete As Boolean)
    ' This code will prevent line items from being deleted but not the whole order
    If Date1.Enabled = False Then
        MsgBox "Changes not Allowed This order is a Status 4"
        AllowDelete = False
    End If
End Sub

Scott
NEXO Systems, Inc.

 

[stravis]

Oops!

The last code snippet should have all values that currently read 'Status.Text = "1"' changed to 'Status.Text = "4"'. Sorry, I forgot to change that before I copied the code in.

Scott