VB数据库编程中MSHFlexGrid控件用法详解

1、VB 数据库编程中 MSHFlexGrid 控件用法详解添加 MSHFlexGrid 将 Name 改成 flex1添加 text 将 name 改成 txteditPublic rs1 As New ADODB.Recordset, rs2 As New ADODB.Recordset'定义数据集对象Dim i As Integer, j As IntegerSub EditKeyCode(MSHFlexGrid As Control, Edt As Control, KeyCode As Integer, Shift As Integer)'标准编辑控件处理。Select

2、Case KeyCodeCase 27'ESC:隐藏焦点并将其返回 MSFIexGridEdt.Visible = FalseMSHFIexGrid.SetFocusCase 13'ENTER 将焦点返回 MSFIexGrid 。MSHFIexGrid.SetFocusDoEventsIf MSHFIexGrid.CoI < MSHFIexGrid.CoIs - 1 ThenMSHFIexGrid.CoI = MSHFIexGrid.CoI + 1EIseIf MSHFIexGrid.CoI = MSHFIexGrid.CoIs - 1 Then MSHFIexGrid.

3、Row = MSHFIexGrid.Row + 1 MSHFIexGrid.CoI = 1End IfEnd IfCase 38'向上MSHFIexGrid.SetFocusDoEventsIf MSFIexGrid.Row > MSFIexGrid.FixedRows Then MSFIexGrid.Row = MSFIexGrid.Row - 1End IfEnd SeIect End SubSub view_DP()If fIex1.CoI = 4 Or fIex1.CoI = 5 Or fIex1.CoI = 6 Then'格式化 FIex1 表的第 7 列、第

4、8列、第 9 列、第 11列 fIex1.TextMatrix(fIex1.Row, 5) = Format(fIex1.TextMatrix(fIex1.Row, 5), "0.00") fIex1.TextMatrix(fIex1.Row, 6)= VaI(fIex1.TextMatrix(fIex1.Row, 4)*VaI(fIex1.TextMatrix(fIex1.Row, 5)fIex1.TextMatrix(fIex1.Row, 6) = Format(fIex1.TextMatrix(fIex1.Row, 6), "0.00") End

5、IfDim A, B As SingIe'声明单精度浮点型变量On Error Resume NextFor i = 1 To flex1.Rows - 1If flex1.TextMatrix(i, 1) <> "" And flex1.TextMatrix(i, 4) <> "" And flex1.TextMatrix(i, 6) <> "" ThenA = Val(flex1.TextMatrix(i, 6) + A' 求合计金额B = Val(flex1.TextMatri

6、x(i, 4) + B' 求合计数量End If Next i lblCount = B lblSum = Format(A, "0.00")'格式化合计金额End SubSub SetButtons(bVal As Boolean) cmdRegister.Enabled = Not bVal cmdSave.Enabled = bVal cmdCancel.Enabled = bVal flex1.Enabled = bV al Frame1.Enabled = bValEnd SubPrivate Sub DataGrid2_KeyDown(KeyCo

7、de As Integer, Shift As Integer)If KeyCode = vbKeyReturn ThenWith Adodc2.Recordset '赋值给 flex1 表格 If .Fields(" 商品名称 ") <> "" Then flex1.TextMatrix(flex1.Row, 1) = Trim(.Fields("商品名称 ")If .Fields(" 规格") <> "" Then flex1.TextMatrix(flex1

8、.Row, 2) = Trim(.Fields(" 规格") If .Fields(" 单位") <> "" Then flex1.TextMatrix(flex1.Row, 3) = Trim(.Fields(" 单位") flex1.TextMatrix(flex1.Row, 5) = .Fields(" 成本价 ")End With flex1.Col = 4 DataGrid2.Visible = False'DataGrid2 不可见txtEdit.Text = &

9、quot;" txtEdit.SetFocusEnd IfIf KeyCode = vbKeyEscape Then'按 ESC 键 DataGrid2 不可见DataGrid2.Visible = False' txtEdit.SetFocus 'txtEdit 获得焦点End IfEnd SubPrivate Sub delone_Click()For i = 1 To flex1.Cols - 1 flex1.TextMatrix(flex1.Row, i) = ""Next i view_DPEnd SubPrivate Sub f

10、lex1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)'利用 PopupMenu 方法If Button And vbRightButton Then PopupMenu edit' 弹出菜单End IfEnd SubPrivate Sub Form_Load()Dim i As Integer '使第一列较窄。 flex1.ColWidth(0) = flex1.ColWidth(0) / 2 '初始化编辑框 txtEdit = "" fle

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

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

13、e MSHFlexGrid.Left + MSHFlexGrid.CellLeft - 15, _ MSHFlexGrid.Top + MSHFlexGrid.CellTop - 15, _ MSHFlexGrid.CellWidth, _ MSHFlexGrid.CellHeight Edt.Visible = True Edt.SetFocusEnd SubPrivate Sub flex1_DblClick()'模拟一个空格。If flex1.Col >= 4 Or flex1.Col = 1 ThenMSHFlexGridEdit flex1, txtEdit, 32 E

14、nd IfEnd SubPrivate Sub lblSum_Click()End SubPrivate 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 +'%

15、'+'" + txt1(0) + "'+'%'or 简称 like +'%'+'" + txt1(0) + "'+'%'"Adodc1.RefreshIf Adodc1.Recordset.RecordCount > 0 ThenDataGrid1.Visible = TrueDataGrid1.SetFocusElseAdodc1.RecordSource = " 供应商信息表 "Adodc1.RefreshIf Adodc1.R

16、ecordset.RecordCount > 0 ThenDataGrid1.Visible = TrueDataGrid1.SetFocusElseMsgBox " 无可选的供应商信息，请首先录入供应商数据！", , "提示窗口 "End IfEnd IfEnd IfIf KeyCode = vbKeyReturn And Index = 1 Thenflex1.Col = 1flex1.Row = 1flex1.SetFocusEnd IfEnd SubPrivate Sub DataGrid1_KeyDown(KeyCode As Integ

17、er, Shift As Integer)If KeyCode = vbKeyReturn Thentxt1(0) = Adodc1.Recordset.Fields(" 供应商全称 ")txt1(1).SetFocusDataGrid1.Visible = FalseEnd IfEnd SubPrivate Sub txtEdit_KeyPress(KeyAscii As Integer)'删除回车符，以消除嘟嘟声。If KeyAscii = Asc(vbCr) Then KeyAscii = 0 End SubPrivate Sub txtEdit_KeyDow

18、n(KeyCode As Integer, Shift As Integer)If KeyCode = vbKeyReturn And flex1.Col = 1 ThenAdodc2.RecordSource = " 商品信息表 where 商品名称 like +'%'+'" + txtEdit + "'+'%'" Adodc2.RefreshIf Adodc2.Recordset.RecordCount > 0 ThenDataGrid2.Visible = TrueDataGrid2.SetFo

19、cusElse Adodc2.RecordSource = " 商品信息表 " Adodc2.RefreshIf Adodc2.Recordset.RecordCount > 0 Then DataGrid2.Visible = True DataGrid2.SetFocusElseMsgBox " 无可选的商品信息，请首先录入商品数据！ ", , "提示窗口 " End IfEnd IfEnd If'只有 TextBox 控件在“数量”单元格时，才使用以下过程If flex1.Col >= 4 ThenEditK

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

21、tEdit.Visible = False Then Exit Subflex1 = txtEdit txtEdit.Visible = False End Sub Private Sub cmdCancel_Click()For i = 1 To flex1.Rows - 1For j = 1 To flex1.Cols - 1 flex1.TextMatrix(i, j) = ""Next jNext iSetButtons FalsetxtEdit.Visible = False view_DPEnd SubPrivate Sub cmdRegister_Click(

22、)Dim lsph As Integer'声明一个整型变量'创建入库票号rs1.Open "select * from 入库主表 order by 票号 ", Cnn, adOpenStaticIf rs1.RecordCount > 0 ThenIf Not rs1.EOF Then rs1.MoveLastIf rs1.Fields(" 票号 ") <> "" Thenlsph = Val(Right(Trim(rs1.Fields(" 票号 "), 4) + 1 txtph.

23、Text = Date & "rk" & Format(lsph, "0000")End IfElse txtph.Text = Date & "rk" & "0001"End Ifrs1.ClosetxtDate.Text = Date '设置控件有效或无效 SetButtons True For i = 1 To flex1.Rows - 1 For j = 1 To flex1.Cols - 1 flex1.TextMatrix(i, j) = ""

24、Next jNext itxt1(0) = ""txt1(1) = "" txt1(0).SetFocus view_DPEnd SubPrivate Sub cmdSave_Click()Dim js As IntegerFor i = 1 To flex1.Rows - 1If flex1.TextMatrix(i, 1) <> "" And flex1.TextMatrix(i, 2) <> "" And flex1.TextMatrix(i, 4) = "" Th

25、enMsgBox "第" & i & " 行录入错误！ ", , "提示窗口 "Exit SubEnd IfIf flex1.TextMatrix(i, 1) = "" Thenjs = js + 1End IfNext iIf js = flex1.Rows - 1 ThenMsgBox " 没有要保存的数据！ ", , " 提示窗口 "Exit SubEnd Ifrs1.Open "select * from 入库从表 ", Cnn,

26、adOpenKeyset, adLockOptimistic For i = 1 To flex1.Rows - 1If flex1.TextMatrix(i, 1) <> "" And flex1.TextMatrix(i, 2) <> "" And flex1.TextMatrix(i, 4) <> "" Then'添加新记录到 "入库从表 " 中 rs1.AddNew商品名称 ") = flex1.TextMatrix(i, 1) 规格 ") =

27、 flex1.TextMatrix(i, 2) 单位 ") = flex1.TextMatrix(i, 3)If flex1.TextMatrix(i, 1) <> "" Then rs1.Fields("If flex1.TextMatrix(i, 2) <> "" Then rs1.Fields("If flex1.TextMatrix(i, 3) <> "" Then rs1.Fields(" rsl.Fields(”入库数量 ”)=Val(flex1.TextMatrix(i, 4) rsl.Fields(”单价")=Val(flex1.TextMatrix(i, 5)rsl.Fields(”金额")=Val(flex1.TextMatrix(i, 6)If flex1.TextMatrix(i, 7) <> "" Then rs1.Fields(" 备注") = flex1.TextMatrix(i, 7)I