汇总VB数据库编程中MSHFlexGrid控件用法详解.docx

1、汇总VB数据库编程中MSHFlexGrid控件用法详解汇总VB数据库编程中MSHFlexGrid控件用法详解VB数据库编程中MSHFlexGrid控件用法详解添加MSHFlexGrid将Name改成flex1 添加text 将name 改成txtedit Public rs1 As New ADODB.Recordset, rs2 As New ADODB.Recordset 定义数据集对象Dim i As Integer, j As Integer Sub EditKeyCode(MSHFlexGrid As Control, Edt As Control, KeyCode As Integ

2、er, Shift As Integer) 标准编辑控件处理。 Select Case KeyCode Case 27 ESC:隐藏焦点并将其返回 MSFlexGridEdt.Visible = False MSHFlexGrid.SetFocus Case 13 ENTER 将焦点返回 MSFlexGrid。MSHFlexGrid.SetFocus DoEvents If MSHFlexGrid.Col MSFlexGrid.FixedRows ThenMSFlexGrid.Row = MSFlexGrid.Row - 1End If End Select End Sub Sub view_

3、DP()If flex1.Col = 4 Or flex1.Col = 5 Or flex1.Col = 6 Then格式化Flex1表的第7列、第8列、第9列、第11列flex1.TextMatrix(flex1.Row, 5) = Format(flex1.TextMatrix(flex1.Row, 5), 0.00)flex1.TextMatrix(flex1.Row, 6) = Val(flex1.TextMatrix(flex1.Row, 4) * Val(flex1.TextMatrix(flex1.Row, 5) flex1.TextMatrix(flex1.Row, 6) =

4、Format(flex1.TextMatrix(flex1.Row, 6), 0.00)End If Dim A, B As Single 声明单精度浮点型变量On Error Resume Next For i = 1 To flex1.Rows - 1 If flex1.TextMatrix(i, 1) And flex1.TextMatrix(i, 4) And flex1.TextMatrix(i, 6) Then A = Val(flex1.TextMatrix(i, 6) + A 求合计金额B = Val(flex1.TextMatrix(i, 4) + B 求合计数量End If

5、 Next i lblCount = B lblSum = Format(A, 0.00) 格式化合计金额End Sub Sub SetButtons(bVal As Boolean) cmdRegister.Enabled = Not bVal cmdSave.Enabled = bVal cmdCancel.Enabled = bVal flex1.Enabled = bVal Frame1.Enabled = bVal End Sub Private Sub DataGrid2_KeyDown(KeyCode As Integer, Shift As Integer)If KeyCode

6、 = vbKeyReturn Then With Adodc2.Recordset 赋值给flex1表格 If .Fields(商品名称) Then flex1.TextMatrix(flex1.Row, 1) = Trim(.Fields(商品名称) If .Fields(规格) Then flex1.TextMatrix(flex1.Row, 2) = Trim(.Fields(规格) If .Fields(单位) Then flex1.TextMatrix(flex1.Row, 3) = Trim(.Fields(单位) flex1.TextMatrix(flex1.Row, 5) =

7、.Fields(成本价)End With flex1.Col = 4 DataGrid2.Visible = False DataGrid2不可见txtEdit.Text = txtEdit.SetFocus End If If KeyCode = vbKeyEscape Then 按ESC键DataGrid2不可见DataGrid2.Visible = False txtEdit.SetFocus txtEdit获得焦点End If End Sub Private Sub delone_Click() For i = 1 To flex1.Cols - 1 flex1.TextMatrix(

8、flex1.Row, i) = Next i view_DP End Sub Private Sub flex1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)利用PopupMenu方法 If Button And vbRightButton Then PopupMenu edit 弹出菜单 End If End Sub Private Sub Form_Load() Dim i As Integer 使第一列较窄。 flex1.ColWidth(0) = flex1.ColWidth(0) /

9、2 初始化编辑框 txtEdit = flex1.Rows = 101 flex1.Cols = 8 设置列标头。 s$ = |商品名称 |规格 |单位 |数量 |进货价 |金额 |备注 flex1.FormatString = s$ End SubPrivate Sub flex1_KeyPress(KeyAscii As Integer) If flex1.Col = 4 Or flex1.Col = 1 Then MSHFlexGridEdit flex1, txtEdit, KeyAsciiEnd If End Sub 添加下列例程以初始化文本框，并将焦点从 Hierarchical

10、FlexGrid 传递到 TextBox 控件:Sub MSHFlexGridEdit(MSHFlexGrid As Control, Edt As Control, KeyAscii As Integer)使用已输入的字符。 Select Case KeyAscii 空格表示编辑当前的文本。 Case 0 To 32 Edt = MSHFlexGrid Edt.SelStart = 1000 其它所有字符表示取代当前的文本。 Case Else Edt = Chr(KeyAscii) Edt.SelStart = 1 End Select 在合适的位置显示 Edt。 Edt.Move MSH

11、FlexGrid.Left + MSHFlexGrid.CellLeft - 15, _MSHFlexGrid.Top + MSHFlexGrid.CellTop - 15, _MSHFlexGrid.CellWidth, _ MSHFlexGrid.CellHeight Edt.Visible = True Edt.SetFocus End Sub Private Sub flex1_DblClick() If flex1.Col = 4 Or flex1.Col = 1 Then MSHFlexGridEdit flex1, txtEdit, 32 模拟一个空格。End If End Su

12、b Private Sub lblSum_Click() End Sub Private Sub txt1_KeyDown(Index As Integer, KeyCode As Integer, Shift As Integer)If KeyCode = vbKeyReturn And Index = 0 ThenAdodc1.RecordSource = 供应商信息表 where 供应商编号 like +%+ + txt1(0) + +%or 供应商全称 like +%+ + txt1(0) + +%or 简称 like +%+ + txt1(0) + +%Adodc1.Refresh

13、If Adodc1.Recordset.RecordCount 0 ThenDataGrid1.Visible = True DataGrid1.SetFocus Else Adodc1.RecordSource = 供应商信息表Adodc1.Refresh If Adodc1.Recordset.RecordCount 0 ThenDataGrid1.Visible = True DataGrid1.SetFocus Else MsgBox 无可选的供应商信息，请首先录入供应商数据, , 提示窗口End If End If End If If KeyCode = vbKeyReturn An

14、d Index = 1 Thenflex1.Col = 1 flex1.Row = 1 flex1.SetFocus End If End Sub Private Sub DataGrid1_KeyDown(KeyCode As Integer, Shift As Integer)If KeyCode = vbKeyReturn Then txt1(0) = Adodc1.Recordset.Fields(供应商全称)txt1(1).SetFocus DataGrid1.Visible = False End If End Sub Private Sub txtEdit_KeyPress(Ke

15、yAscii As Integer) 删除回车符，以消除嘟嘟声。 If KeyAscii = Asc(vbCr) Then KeyAscii = 0 End Sub Private Sub txtEdit_KeyDown(KeyCode As Integer, Shift As Integer)If KeyCode = vbKeyReturn And flex1.Col = 1 ThenAdodc2.RecordSource = 商品信息表 where 商品名称 like +%+ + txtEdit + +%Adodc2.Refresh If Adodc2.Recordset.RecordCo

16、unt 0 ThenDataGrid2.Visible = True DataGrid2.SetFocus Else Adodc2.RecordSource = 商品信息表Adodc2.Refresh If Adodc2.Recordset.RecordCount 0 ThenDataGrid2.Visible = True DataGrid2.SetFocus Else MsgBox 无可选的商品信息，请首先录入商品数据, , 提示窗口End If End If End If 只有TextBox控件在“数量”单元格时，才使用以下过程If flex1.Col = 4 Then EditKeyC

17、ode flex1, txtEdit, KeyCode, ShiftEnd If End Sub 当输入数据并按下 ENTER 键，或用鼠标单击MSHFlexGrid 控件中的另一个单元时，焦点将返回此控件。这时 TextBox 中的文本被复制到活动单元中Private Sub flex1_GotFocus() If txtEdit.Visible = False Then Exit Sub flex1 = txtEdit txtEdit.Visible = False view_DP End Sub Private Sub flex1_LeaveCell() If txtEdit.Visib

18、le = False Then Exit Sub flex1 = txtEdittxtEdit.Visible = False End Sub Private Sub cmdCancel_Click() For i = 1 To flex1.Rows - 1 For j = 1 To flex1.Cols - 1 flex1.TextMatrix(i, j) = Next j Next i SetButtons False txtEdit.Visible = False view_DP End Sub Private Sub cmdRegister_Click() Dim lsph As In

19、teger 声明一个整型变量创建入库票号 rs1.Open select * from 入库主表 order by 票号, Cnn, adOpenStaticIf rs1.RecordCount 0 Then If Not rs1.EOF Then rs1.MoveLast If rs1.Fields(票号) Then lsph = Val(Right(Trim(rs1.Fields(票号), 4) + 1txtph.Text = Date & rk & Format(lsph, 0000)End If Else txtph.Text = Date & rk & 0001 End If rs1

20、.Close txtDate.Text = Date 设置控件有效或无效 SetButtons True For i = 1 To flex1.Rows - 1 For j = 1 To flex1.Cols - 1 flex1.TextMatrix(i, j) = Next j Next i txt1(0) = txt1(1) = txt1(0).SetFocus view_DP End Sub Private Sub cmdSave_Click()Dim js As Integer For i = 1 To flex1.Rows - 1 If flex1.TextMatrix(i, 1)

21、And flex1.TextMatrix(i, 2) And flex1.TextMatrix(i, 4) = Then MsgBox 第 & i & 行录入错误, , 提示窗口Exit Sub End If If flex1.TextMatrix(i, 1) = Then js = js + 1 End If Next i If js = flex1.Rows - 1 Then MsgBox 没有要保存的数据, , 提示窗口Exit Sub End If rs1.Open select * from 入库从表, Cnn, adOpenKeyset, adLockOptimisticFor i

22、 = 1 To flex1.Rows - 1 If flex1.TextMatrix(i, 1) And flex1.TextMatrix(i, 2) And flex1.TextMatrix(i, 4) Then 添加新记录到入库从表中 rs1.AddNew If flex1.TextMatrix(i, 1) Then rs1.Fields(商品名称) = flex1.TextMatrix(i, 1)If flex1.TextMatrix(i, 2) Then rs1.Fields(规格) = flex1.TextMatrix(i, 2)If flex1.TextMatrix(i, 3) T

23、hen rs1.Fields(单位) = flex1.TextMatrix(i, 3)rs1.Fields(入库数量) = Val(flex1.TextMatrix(i, 4)rs1.Fields(单价) = Val(flex1.TextMatrix(i, 5)rs1.Fields(金额) = Val(flex1.TextMatrix(i, 6)If flex1.TextMatrix(i, 7) Then rs1.Fields(备注) = flex1.TextMatrix(i, 7)If txtph.Text Then rs1.Fields(入库票号) = Trim(txtph.Text)rs

24、1.Update Cnn.Execute (update 商品信息表 set 库存数量=库存数量+ & Val(flex1.TextMatrix(i, 4) & where 商品名称 = + _flex1.TextMatrix(i, 1) + and 规格= + flex1.TextMatrix(i, 2) + )End If Next i rs1.Close rs2.Open select * from 入库主表, Cnn, adOpenKeyset, adLockOptimistic 打开数据库 rs2.AddNew If txtph.Text Then rs2.Fields(票号) = Trim(txtph.Text)If txtDate.Text Then rs2.Fields(入库日期) = Trim(txtDate.Text)If txt1(0) Then rs2.Fields(供应商全称) = txt1(0)If txt1(1) Then rs2.Fields(经手人) = txt1(1)rs2.Fields(入库金额) = lblSum rs2.Update rs2.Close 设置控件有效或无效 SetButtons False End Sub Private Sub cmdQuit_Click() End