VB中mshflexgrid中getfocus与cellleave事件的区别

最新推荐文章于 2024-04-09 14:52:11 发布

changgeng_zhao

最新推荐文章于 2024-04-09 14:52:11 发布

阅读量2.1k

点赞数

文章标签： vb string sql integer function join

本文链接：https://blog.csdn.net/changgeng_zhao/article/details/4020481

版权

只要点mshflexgrid控件getfocus事件就会被触发，而cellLeave事件只有改变单元格时才会被触发，而且cellLeave事件要比getfocus事件先执行，如果cellLeave事件中有msgbox或exit sub，那么getfocus就不会再执行。

另外，为了使mshflexgrid变成一个可编辑的控件，变相的处理方式是引入一个文本框，在getfocus事件中将该文本框填充到表格的单元格中，下面是我的一个程序的一个文本的源代码，贴出来供大家共享，这个文件里面虽然参杂了我的业务代码，但是如果仔细看是能够明白如何使mshflexgrid变成一个可编辑控件的

Public foutputStockBillId As String
Public buttonKey As String
Dim msg As String
Private Sub cancel_Click()
Unload Me
End Sub
Private Sub confirm_Click()
   outputStockBillEntryMSHFlexGrid_leavecell
   On Error GoTo err:
   '输入校验
   If fnumber.Text = "" Then
      MsgBox "入库单编码不能为空！", vbOKOnly, "错误"
      fnumber.SetFocus
      Exit Sub
   End If
   If outputStockBillEntryMSHFlexGrid.Rows < 1 Then
      MsgBox "请选择商品！", vbOKOnly, "错误"
      Exit Sub
   End If
    '客户信息不能为空
   If Me.fcustomerId = "" Then
      MsgBox "客户信息不能为空！", vbOKOnly, "错误"
      Exit Sub
   End If
   '检查编码是否重复
   If isNumberExist(fnumber.Text) Then
      MsgBox "出库单编码重复，请重新输入！", vbOKOnly, "错误"
      Exit Sub
   End If
   '检查数据输入是否正确
   For i = 1 To outputStockBillEntryMSHFlexGrid.Rows - 1
       If outputStockBillEntryMSHFlexGrid.TextMatrix(i, 7) = "" Then
          MsgBox outputStockBillEntryMSHFlexGrid.TextMatrix(i, 5) + "重量不能为空，请输入！", vbOKOnly, "提示"
          Exit Sub
       End If
       If Not CheckInput.isPositiveRealNumber(outputStockBillEntryMSHFlexGrid.TextMatrix(i, 7)) Then
          MsgBox outputStockBillEntryMSHFlexGrid.TextMatrix(i, 5) + "重量输入有误，请重新输入！", vbOKOnly, "提示"
          Exit Sub
       End If
       If outputStockBillEntryMSHFlexGrid.TextMatrix(i, 8) = "" Then
          MsgBox outputStockBillEntryMSHFlexGrid.TextMatrix(i, 5) + "数量不能为空，请输入！", vbOKOnly, "提示"
          Exit Sub
       End If
       If Not CheckInput.isPositiveRealNumber(outputStockBillEntryMSHFlexGrid.TextMatrix(i, 8)) Then
          MsgBox outputStockBillEntryMSHFlexGrid.TextMatrix(i, 5) + "数量输入有误，请重新输入！", vbOKOnly, "提示"
          Exit Sub
       End If
       If outputStockBillEntryMSHFlexGrid.TextMatrix(i, 9) = "" Then
          MsgBox outputStockBillEntryMSHFlexGrid.TextMatrix(i, 5) + "单价不能为空，请输入！", vbOKOnly, "提示"
          Exit Sub
       End If
       If Not CheckInput.isPositiveRealNumber(outputStockBillEntryMSHFlexGrid.TextMatrix(i, 9)) Then
          MsgBox outputStockBillEntryMSHFlexGrid.TextMatrix(i, 5) + "单价输入有误，请重新输入！", vbOKOnly, "提示"
          Exit Sub
       End If
   Next i

   '消退时数量不能大于原来的数据
   For i = 1 To outputStockBillEntryMSHFlexGrid.Rows - 1
       If Not isEnoughAmount(outputStockBillEntryMSHFlexGrid.TextMatrix(i, 1), outputStockBillEntryMSHFlexGrid.TextMatrix(i, 8)) Then
          MsgBox "消退数量不能大于原来的出库单数量", vbOKOnly, "错误"
          Exit Sub
       End If
   Next i
   '防止用户直接点确定按钮，强制调整重量
   For i = 1 To outputStockBillEntryMSHFlexGrid.Rows - 1
       outputStockBillEntryMSHFlexGrid.TextMatrix(i, 7) = getBackWeight(outputStockBillEntryMSHFlexGrid.TextMatrix(i, 1), CDbl(outputStockBillEntryMSHFlexGrid.TextMatrix(i, 8)))
   Next i
   '按照先进后出修改入库单剩余数量，插入消退单，修改库存数量，修改出库单的重量时按照先进后出的原则
   Dim conn As New ADODB.Connection
   conn.connectionString = dataBaseConn.getExistConnectionString
   conn.Open
   conn.BeginTrans '开始事务
      Dim today As Date
      today = Now
      foutputStockBillId = createGUID.createGUID
      Dim foutputStockBillEntryId As String

      '插入出库单记录商品
      conn.Execute "insert into t_outputStockBill(foutputStockBillId,fnumber,foutputStockDatetime,fcustomerId,fisBackBill,fparentBillNumber) " _
      + " values('" + foutputStockBillId + "','" + fnumber.Text + "','" + CStr(today) + "','" + fcustomerId.Text + "','1','" + outputStockBillEntryMSHFlexGrid.TextMatrix(1, 3) + "')"
      '插入出库单分录,修改原出库单的退单数
      For i = 1 To outputStockBillEntryMSHFlexGrid.Rows - 1
        foutputStockBillEntryId = createGUID.createGUID
        conn.Execute "insert into t_outputStockBillEntry(foutputStockBillEntryId, foutputStockBillId, fgoodId,fweight,famount,funitPrice,foutDate) " _
            + " values('" + foutputStockBillEntryId + "','" + foutputStockBillId + "','" + outputStockBillEntryMSHFlexGrid.TextMatrix(i, 2) + "','" _
            + outputStockBillEntryMSHFlexGrid.TextMatrix(i, 7) + "','" + outputStockBillEntryMSHFlexGrid.TextMatrix(i, 8) + "','" _
            + outputStockBillEntryMSHFlexGrid.TextMatrix(i, 9) + "','" + CStr(today) + "')"
        conn.Execute "update t_outputStockBillEntry set fbackAmount = (case when fbackAmount is null then " + outputStockBillEntryMSHFlexGrid.TextMatrix(i, 8) + " else fbackAmount+" + outputStockBillEntryMSHFlexGrid.TextMatrix(i, 8) + " end),fbackWeight=(case when fbackWeight is null then " + outputStockBillEntryMSHFlexGrid.TextMatrix(i, 8) + " else fbackWeight+" + outputStockBillEntryMSHFlexGrid.TextMatrix(i, 8) + " end) where foutputStockBillEntryId='" + outputStockBillEntryMSHFlexGrid.TextMatrix(i, 1) + "'"
        '修改入库单分录商品剩余数量，按先进后出原则将入库单中的商品的数量消除掉，注意要查找重量和出库单重量匹配的入库单修改
        Dim sql As String
        sql = "declare @famount decimal(18,2),@fbackAmount decimal(18,2),@finputStockBillEntryId varchar(200),@fId varchar(36),@k decimal(18,2) " _
           + "declare cur cursor for select t1.famount,t1.fbackAmount,t2.finputStockBillEntryId,t1.fId from t_outputFromInput t1 " _
           + "inner join t_inputStockBillEntry t2 on t1.finputStockBillEntryId=t2.finputStockBillEntryId Where t1.foutputStockBillEntryId='" + outputStockBillEntryMSHFlexGrid.TextMatrix(i, 1) + "' and t1.famount > t1.fbackAmount " _
           + "order by t2.fentryDate desc " _
           + "open cur " _
           + "set @k = " + outputStockBillEntryMSHFlexGrid.TextMatrix(i, 8) + " " _
           + "fetch next from cur into @famount,@fbackAmount,@finputStockBillEntryId,@fId " _
           + "while @@FETCH_STATUS=0 " _
           + "begin " _
           + " if @famount-@fbackAmount>=@k " _
           + "     begin " _
           + "       update t_inputStockBillEntry set fremainAmount=fremainAmount+@k where finputStockBillEntryId=@finputStockBillEntryId " _
           + "       update t_outputFromInput set fbackAmount=fbackAmount+@k where fId=@fId " _
           + "       break " _
           + "     End " _
           + " Else " _
           + "     begin " _
           + "       update t_inputStockBillEntry set fremainAmount=@famount where finputStockBillEntryId=@finputStockBillEntryId " _
           + "       update t_outputFromInput set fbackAmount=@famount where fId=@fId " _
           + "       set @k=@k-(@famount-@fbackAmount) " _
           + "       fetch next from cur into @famount,@fbackAmount,@finputStockBillEntryId,@fId " _
           + "      End " _
           + "End " _
           + " close cur " _
           + "DEALLOCATE cur"
      conn.Execute sql
      '修改库存记录,原来的基础上减去重量和数量
      conn.Execute "update t_stock set famount=famount+" + outputStockBillEntryMSHFlexGrid.TextMatrix(i, 8) _
             + ",fweight=fweight+" + outputStockBillEntryMSHFlexGrid.TextMatrix(i, 7) _
             + " where fgoodId='" + outputStockBillEntryMSHFlexGrid.TextMatrix(i, 2) + "'"
      Next i
      conn.CommitTrans '结束事务

   outputStockBillList.Form_Load
   Unload Me
   Exit Sub
err:
   MsgBox err.Description
   If conn.State = adStateOpen Then
      conn.RollbackTrans
      conn.Close
   End If
   Unload Me
End Sub
Private Function isExistGood(ByVal goodId As String) As Boolean
   Dim conn1 As ADODB.Connection
   Set conn1 = New ADODB.Connection
   conn1.connectionString = dataBaseConn.getExistConnectionString
   conn1.Open
   Dim rs As ADODB.Recordset
   Dim sql As String
   Set rs = conn1.Execute(sql)
   sql = "select fgoodId from t_stock where fgoodId ='" + goodId + "'"
   If Not (rs.BOF And rs.EOF) Then
      isExistGood = True
   Else
      isExistGood = False
   End If
   conn1.Close
End Function
Private Function isExistEnoughAmountGood(ByVal goodId As String, ByVal goodAmount As String) As Boolean
   On Error GoTo err
   Dim conn1 As ADODB.Connection
   Set conn1 = New ADODB.Connection
   conn1.connectionString = dataBaseConn.getExistConnectionString
   conn1.Open
   Dim rs As ADODB.Recordset
   Dim sql As String
    sql = "select fgoodId from t_stock where fgoodId ='" + goodId + "' and famount>=" + goodAmount
   Set rs = conn1.Execute(sql)
   If Not (rs.BOF And rs.EOF) Then
      isExistEnoughAmountGood = True
   Else
      isExistEnoughAmountGood = False
   End If
   conn1.Close
   Exit Function
err:
   MsgBox err.Description, vbOKOnly, "错误"
End Function
Private Function isExistEnoughWeightGood(ByVal goodId As String, ByVal goodWeight As String) As Boolean
   Dim conn1 As ADODB.Connection
   Set conn1 = New ADODB.Connection
   conn1.connectionString = dataBaseConn.getExistConnectionString
   conn1.Open
   Dim rs As ADODB.Recordset
   Dim sql As String
   Set rs = conn1.Execute(sql)
   sql = "select fgoodId from t_stock where fgoodId ='" + goodId + "' and famount=" + goodWeight
   If Not (rs.BOF And rs.EOF) Then
      isExistEnoughWeightGood = True
   Else
      isExistEnoughWeightGood = False
   End If
   conn1.Close
End Function

Private Sub custName_Click()
   custSelDlg.Top = Me.Top + 1000
   custSelDlg.Left = Me.Left + 1000
   custSelDlg.Show 1
End Sub

Private Sub Form_Load()
   fnumber.Text = getOutputStockBillNextNumber
   Dim conn As New ADODB.Connection
   conn.connectionString = dataBaseConn.getExistConnectionString
   conn.Open '打开连接
   '获取出库单编码和客户信息（包括客户名称，客户ID）
   Dim sql As String
   sql = "select t1.fnumber,t1.fcustomerId,t2.fname from t_outputStockBill t1 inner join t_customer t2 on t1.fcustomerId=t2.fcustomerId "
   Dim rs As ADODB.Recordset
   Set rs = conn.Execute(sql)
   If Not (rs.EOF And rs.BOF) Then
      fcustomerId.Text = rs.Fields("fcustomerId")
      custName.Text = rs.Fields("fname")
   End If

   Dim stitle As String
    stitle = "< |<出库单分录ID |<商品ID |<出库单编码 |<出库时间 |<商品名称 |<商品规格 |<总重量 |<数量|<单价 "
    outputStockBillEntryMSHFlexGrid.FormatString = stitle
    outputStockBillEntryMSHFlexGrid.colWidth(0) = 300
    outputStockBillEntryMSHFlexGrid.colWidth(1) = 0
    outputStockBillEntryMSHFlexGrid.colWidth(2) = 0
    outputStockBillEntryMSHFlexGrid.colWidth(3) = 0
    outputStockBillEntryMSHFlexGrid.colWidth(4) = 0
    Dim colWidth As Long
    colWidth = outputStockBillEntryMSHFlexGrid.Width - outputStockBillEntryMSHFlexGrid.colWidth(0) - 100
    outputStockBillEntryMSHFlexGrid.colWidth(5) = colWidth / 5
    outputStockBillEntryMSHFlexGrid.colWidth(6) = colWidth / 5
    outputStockBillEntryMSHFlexGrid.colWidth(7) = colWidth / 5
    outputStockBillEntryMSHFlexGrid.colWidth(8) = colWidth / 5
    outputStockBillEntryMSHFlexGrid.colWidth(9) = colWidth / 5

    While outputStockBillEntryMSHFlexGrid.TextMatrix(1, 2) = "" And outputStockBillEntryMSHFlexGrid.Rows > 2
       outputStockBillEntryMSHFlexGrid.RemoveItem 1
    Wend
    If outputStockBillEntryMSHFlexGrid.Rows = 2 And outputStockBillEntryMSHFlexGrid.TextMatrix(1, 2) = "" Then
       outputStockBillEntryMSHFlexGrid.RowHeight(1) = 0
    End If
    outputStockBillEntryMSHFlexGrid.Refresh
    Me.tb.BackColor = RGB(240, 240, 240)

    sql = getQuerySql()
    Set rs = conn.Execute(sql)
    If Not (rs.BOF And rs.EOF) Then
       rs.MoveFirst
       Do While Not rs.EOF
           If outputStockBillEntryMSHFlexGrid.Rows = 2 And outputStockBillEntryMSHFlexGrid.TextMatrix(1, 2) = "" Then
              outputStockBillEntryMSHFlexGrid.TextMatrix(1, 1) = rs.Fields("foutputStockBillEntryId")
              outputStockBillEntryMSHFlexGrid.TextMatrix(1, 2) = rs.Fields("fgoodId")
              outputStockBillEntryMSHFlexGrid.TextMatrix(1, 3) = rs.Fields("fnumber")
              outputStockBillEntryMSHFlexGrid.TextMatrix(1, 4) = CStr(rs.Fields("foutputStockDatetime"))
              outputStockBillEntryMSHFlexGrid.TextMatrix(1, 5) = rs.Fields("fname")
              outputStockBillEntryMSHFlexGrid.TextMatrix(1, 6) = rs.Fields("fnorm")
              outputStockBillEntryMSHFlexGrid.TextMatrix(1, 7) = CStr(rs.Fields("fweight"))
              outputStockBillEntryMSHFlexGrid.TextMatrix(1, 8) = CStr(rs.Fields("famount"))
              outputStockBillEntryMSHFlexGrid.TextMatrix(1, 9) = CStr(rs.Fields("funitprice"))
           Else
              outputStockBillEntryMSHFlexGrid.AddItem "" + vbTab _
       + rs.Fields("foutputStockBillEntryId") + vbTab _
       + rs.Fields("fgoodId") + vbTab _
       + rs.Fields("fnumber") + vbTab _
       + CStr(rs.Fields("foutputStockDatetime")) + vbTab _
       + rs.Fields("fname") + vbTab _
       + rs.Fields("fnorm") + vbTab _
       + CStr(rs.Fields("fweight")) + vbTab _
       + CStr(rs.Fields("famount")) + vbTab _
       + CStr(rs.Fields("funitprice")) + vbTab
           End If
           outputStockBillEntryMSHFlexGrid.RowHeight(outputStockBillEntryMSHFlexGrid.Rows - 1) = 300
           rs.MoveNext
       Loop
    End If
    Set rs = Nothing
    conn.Close
End Sub
Private Function isNumberExist(number As String) As Boolean
   Dim conn As New ADODB.Connection
   conn.connectionString = dataBaseConn.getExistConnectionString
   conn.Open
   sql = "select foutputStockBillId,fnumber from t_outputStockBill where fnumber='" + number + "'"
   Dim rs As Recordset
   Set rs = conn.Execute(sql)
   If Not (rs.BOF And rs.EOF) Then
      If buttonKey = "add" Then
         isNumberExist = True
      Else
         If rs.Fields("foutputStockBillId") <> foutputStockBillId Then
            isNumberExist = True
         End If
      End If
   Else
      isNumberExist = False
   End If
   Set rs = Nothing
   conn.Close
End Function

Private Function getQuerySql() As String
't1.fnumber,t1.foutputStockDatetime,t3.fname,t3.fnorm,t2.fweight,t2.famount,t2.funitPrice
   getQuerySql = "select t2.foutputStockBillEntryId,t3.fgoodId,t1.fnumber,t1.foutputStockDatetime,t3.fname,t3.fnorm,convert(varchar(200),(case when t2.fbackweight is null then t2.fweight else t2.fweight-t2.fbackweight end)) as fweight,convert(varchar(200),(t2.famount-t2.fbackAmount)) as famount,t2.funitPrice " _
       + " from t_outputStockBill t1 " _
       + " inner join t_outputStockBillEntry t2 on t1.foutputStockBillId=t2.foutputStockBillId " _
       + " inner join t_good t3 on t2.fgoodId=t3.fgoodId " _
       + " where t1.foutputStockBillId='" + foutputStockBillId + "' and (t2.fbackAmount<t2.famount or t2.fbackAmount is null) and (t1.FisBackBill<>'1' or t1.fisBackBill is null)"
End Function

Private Sub outputStockBillEntryMSHFlexGrid_GotFocus()
   '如果单元格宽度不够，将焦点交给删除按钮
   If outputStockBillEntryMSHFlexGrid.CellWidth < 5 Or outputStockBillEntryMSHFlexGrid.CellHeight < 5 Then
      deleteGood.SetFocus
      Exit Sub
   End If
   If outputStockBillEntryMSHFlexGrid.col <> 8 Then
      deleteGood.SetFocus
      Exit Sub
   End If
   tb.Left = outputStockBillEntryMSHFlexGrid.CellLeft + outputStockBillEntryMSHFlexGrid.Left - 10
   tb.Top = outputStockBillEntryMSHFlexGrid.CellTop + outputStockBillEntryMSHFlexGrid.Top - 10
   tb.Height = outputStockBillEntryMSHFlexGrid.CellHeight - 5

   tb.Width = outputStockBillEntryMSHFlexGrid.CellWidth - 5
   tb.Visible = True
   tb.SetFocus
   Me.tb = Me.outputStockBillEntryMSHFlexGrid.Text
   Me.tb.Tag = faIndex(Me.outputStockBillEntryMSHFlexGrid.row, Me.outputStockBillEntryMSHFlexGrid.col)
   Me.tb.SetFocus
   Me.tb.SelStart = Len(Me.tb)
   '控制只能输入数量
   If outputStockBillEntryMSHFlexGrid.col <> 8 Then
      tb.Locked = True
   Else
      tb.Locked = False

   End If
   '显示错误信息
   If msg <> "" Then
     MsgBox msg, vbOKOnly, "提示"
     msg = ""
   End If
End Sub

Private Sub deleteGood_Click()
'当删除到最后只剩下一行非固定行时，此行只能隐藏，不能删除，其它则可以直接删除掉
If outputStockBillEntryMSHFlexGrid.Rows = 2 Then
    Dim i As Integer
    For i = 1 To outputStockBillEntryMSHFlexGrid.Cols - 1
       outputStockBillEntryMSHFlexGrid.TextMatrix(1, i) = ""
    Next i
    outputStockBillEntryMSHFlexGrid.RowHeight(1) = 0
ElseIf outputStockBillEntryMSHFlexGrid.Rows > 2 Then
    outputStockBillEntryMSHFlexGrid.RemoveItem outputStockBillEntryMSHFlexGrid.RowSel
End If
End Sub
Private Sub outputStockBillEntryMSHFlexGrid_leavecell()
'如果行大于一，说明有数据，才执行下面的操作
'如果不是从第八列移开，也不执行一下操作
   If outputStockBillEntryMSHFlexGrid.row > 0 And outputStockBillEntryMSHFlexGrid.col = 8 And tb.Text = "" Then
      msg = "数量不能为空，请输入！"
    End If
    If outputStockBillEntryMSHFlexGrid.row > 0 And outputStockBillEntryMSHFlexGrid.col = 8 And Not CheckInput.isPositiveRealNumber(tb.Text) Then
      msg = "数量输入有误，请重新输入！"
    End If
If outputStockBillEntryMSHFlexGrid.row > 0 And outputStockBillEntryMSHFlexGrid.col = 8 And tb.Text <> "" And CheckInput.isPositiveRealNumber(tb.Text) Then
      '如果文本框中内容为空，则退出
      '如果从第八列，即数量列移开，那么将给第七列赋值，按照先进后出的原则，给第七列赋值为既定数量的商品的总的重量
      '按照先进后去的原则取出商品，然后根据输入的数量，确定商品是否够量，如果不够量，提示客户，如果够量，则累计算出商品的总的重量
      Dim conn1 As ADODB.Connection
      Dim sql As String
      Dim rs As ADODB.Recordset
      Set conn1 = New ADODB.Connection
      conn1.connectionString = dataBaseConn.getExistConnectionString
      conn1.Open
      '出库单分录中消退数量不能超过原来的数量
      If Not isEnoughAmount(outputStockBillEntryMSHFlexGrid.TextMatrix(outputStockBillEntryMSHFlexGrid.row, 1), CDbl(tb.Text)) Then
         msg = "消退数量不能大于原来的出库单数量"
      Else
         '根据客户输入的数量来计算商品总的重量
         outputStockBillEntryMSHFlexGrid.TextMatrix(outputStockBillEntryMSHFlexGrid.row, 7) = getBackWeight(outputStockBillEntryMSHFlexGrid.TextMatrix(outputStockBillEntryMSHFlexGrid.row, 1), CDbl(tb.Text))
      End If
      Set rs = Nothing
      conn1.Close
   End If
End Sub

Private Sub tb_KeyDown(KeyCode As Integer, Shift As Integer)
On Error Resume Next
Select Case KeyCode

   Case 27   ' ESC
     'Tb.Visible = False
     ' outputStockBillEntryMSHFlexGrid.SetFocus
   Case 37      ' LEFT
      'MoveToCell fgEditMoveleft
      KeyCode = 0
      If Me.outputStockBillEntryMSHFlexGrid.col = Me.outputStockBillEntryMSHFlexGrid.FixedCols Then Exit Sub
      Me.outputStockBillEntryMSHFlexGrid.col = Me.outputStockBillEntryMSHFlexGrid.col - 1
      Me.outputStockBillEntryMSHFlexGrid.SetFocus
   Case 38      ' Up.
      'MoveToCell fgEditMoveup
      KeyCode = 0
      If Me.outputStockBillEntryMSHFlexGrid.row = Me.outputStockBillEntryMSHFlexGrid.FixedRows Then Exit Sub
      Me.outputStockBillEntryMSHFlexGrid.row = Me.outputStockBillEntryMSHFlexGrid.row - 1
      Me.outputStockBillEntryMSHFlexGrid.SetFocus
   Case 39, 13     ' right.回车
     ' MoveToCell fgEditMoveright
      KeyCode = 0
      Me.outputStockBillEntryMSHFlexGrid.col = Me.outputStockBillEntryMSHFlexGrid.col + 1
      Me.outputStockBillEntryMSHFlexGrid.SetFocus
   Case 40      ' Down.
      'MoveToCell fgEditMoveDown
      KeyCode = 0
      Me.outputStockBillEntryMSHFlexGrid.row = Me.outputStockBillEntryMSHFlexGrid.row + 1
      Me.outputStockBillEntryMSHFlexGrid.SetFocus
     ' tb_LostFocus
      End Select

End Sub

Private Sub tb_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
      Case 9, 13, 27, 37, 38, 39, 40
         KeyAscii = 0
   End Select
End Sub

Private Sub tb_LostFocus()
Me.tb.Visible = False
Me.outputStockBillEntryMSHFlexGrid.TextArray(tb.Tag) = tb
Me.tb = ""
'Me.Caption = "txt_lostfocus"
End Sub
Function faIndex(row As Integer, col As Integer) As Long
   faIndex = row * outputStockBillEntryMSHFlexGrid.Cols + col
End Function
Private Sub outputStockBillEntryMSHFlexGrid_Scroll()
Dim dx As Long, dy As Long
' move tb with cell or hide if cell out of view
If tb.Visible Then
    With outputStockBillEntryMSHFlexGrid
      If .RowIsVisible(.row) And .ColIsVisible(.col) Then
        dx = .Left + .CellLeft - 10
        dy = .Top + .CellTop - 10
        tb.Move dx, dy
      Else
        tb.Move -tb.Width, -tb.Height
      End If
    End With
End If
End Sub

Private Function isEnoughAmount(ByVal outputStockBillEntryId As String, ByVal amount As String) As Boolean
   Dim conn As New ADODB.Connection
   conn.connectionString = dataBaseConn.getExistConnectionString
   conn.Open
   Dim sql As String
   sql = "select 1 from t_outputStockBillEntry where foutputStockBillEntryId='" + outputStockBillEntryId + "' and ((fbackAmount is null and famount>" + amount + ") or famount>=(fbackAmount+" + amount + "))"
   Dim rs As ADODB.Recordset
   Set rs = conn.Execute(sql)
   If Not (rs.EOF And rs.BOF) Then
      isEnoughAmount = True
   Else
      isEnoughAmount = False
   End If
   Set rs = Nothing
   conn.Close
End Function
Private Function getOutputStockBillNextNumber() As String
   Dim conn1 As New ADODB.Connection
   conn1.connectionString = dataBaseConn.getExistConnectionString
   conn1.Open
   Dim sql As String
   sql = "select fnumber from t_outputStockBill order by fnumber"
   Dim rs As ADODB.Recordset
   Set rs = conn1.Execute(sql)
   getOutputStockBillNextNumber = CreateNumber.getStrNumber(rs, "O", 4)
   Set rs = Nothing
   conn1.Close
End Function

Private Function getBackWeight(ByVal outputStockBillEntryId As String, ByVal amount As Double) As String
    Dim sql As String
    sql = "select t1.famount,t1.fbackAmount,t1.fId,t2.finputStockBillEntryId,t2.fweight from t_outputFromInput t1 " _
        + "inner join t_inputStockBillEntry t2 on t1.finputStockBillEntryId=t2.finputStockBillEntryId " _
        + "where t1.foutputStockBillEntryId='" + outputStockBillEntryId + "' and t1.famount > t1.fbackAmount " _
        + "order by t2.fentryDate desc"
    Dim conn As New ADODB.Connection
    conn.connectionString = dataBaseConn.getExistConnectionString
    conn.Open
    Dim tolWeight As Double
    tolWeight = 0
    Dim rs As ADODB.Recordset
    Set rs = conn.Execute(sql)
    If Not (rs.BOF And rs.EOF) Then
       rs.MoveFirst
       Do While Not rs.EOF
          If rs.Fields("famount") - rs.Fields("fbackAmount") >= amount Then
             tolWeight = tolWeight + rs.Fields("fweight") * amount
             Exit Do
          Else
             tolWeight = tolWeight + rs.Fields("fweight") * (rs.Fields("famount") - rs.Fields("fbackAmount"))
             amount = amount - (rs.Fields("famount") - rs.Fields("fbackAmount"))
             rs.MoveNext
          End If
       Loop
    End If
    Set rs = Nothing
    conn.Close
    getBackWeight = tolWeight
End Function