VB數(shù)據(jù)庫(kù)編程中MSHFlexGrid控件用法詳解

1、VB 數(shù)據(jù)庫(kù)編程中 MSHFlexGrid 控件用法詳解添加 MSHFlexGrid 將 Name 改成 flex1添加 text 將 name 改成 txteditPublic rs1 As New ADODB.Recordset, rs2 As New ADODB.Recordset'定義數(shù)據(jù)集對(duì)象Dim i As Integer, j As IntegerSub EditKeyCode(MSHFlexGrid As Control, Edt As Control, KeyCode As Integer, Shift As Integer)'標(biāo)準(zhǔn)編輯控件處理。Select

2、Case KeyCodeCase 27'ESC:隱藏焦點(diǎn)并將其返回 MSFIexGridEdt.Visible = FalseMSHFIexGrid.SetFocusCase 13'ENTER 將焦點(diǎn)返回 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'聲明單精度浮點(diǎn)型變量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' 求合計(jì)金額B = Val(flex1.TextMatri

6、x(i, 4) + B' 求合計(jì)數(shù)量End If Next i lblCount = B lblSum = Format(A, "0.00")'格式化合計(jì)金額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(" 規(guī)格") <> "" Then flex1.TextMatrix(flex1

8、.Row, 2) = Trim(.Fields(" 規(guī)格") If .Fields(" 單位") <> "" Then flex1.TextMatrix(flex1.Row, 3) = Trim(.Fields(" 單位") flex1.TextMatrix(flex1.Row, 5) = .Fields(" 成本價(jià) ")End With flex1.Col = 4 DataGrid2.Visible = False'DataGrid2 不可見(jiàn)txtEdit.Text = &

9、quot;" txtEdit.SetFocusEnd IfIf KeyCode = vbKeyEscape Then'按 ESC 鍵 DataGrid2 不可見(jiàn)DataGrid2.Visible = False' txtEdit.SetFocus 'txtEdit 獲得焦點(diǎn)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 '設(shè)置列標(biāo)頭。s$ = "A|A商品名稱|A規(guī)格|A單位廣數(shù)量|A進(jìn)貨價(jià)|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'添加下列例程以初始化文本框，并將焦點(diǎn)從Hierarchical FlexGri

12、d 傳遞到 TextBox 控件：Sub MSHFlexGridEdit(MSHFlexGrid As Control, Edt As Control, KeyAscii As Integer) '使用已輸入的字符。Select Case KeyAscii '空格表示編輯當(dāng)前的文本。Case 0 To 32Edt = MSHFlexGridEdt.SelStart = 1000 '其它所有字符表示取代當(dāng)前的文本。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 Then'模擬一個(gè)空格。MSHFlexGridEdit 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 = " 供應(yīng)商信息表 where 供應(yīng)商編號(hào) like +'%'+'" + txt1(0) "'+'%'or 供應(yīng)商全稱 like +'%

15、'+'" + txt1(0) + "'+'%'or 簡(jiǎn)稱 like +'%'+'" + txt1(0) + "'+'%'"Adodc1.RefreshIf Adodc1.Recordset.RecordCount > 0 ThenDataGrid1.Visible = TrueDataGrid1.SetFocusElseAdodc1.RecordSource = " 供應(yīng)商信息表 "Adodc1.RefreshIf Adodc1.R

16、ecordset.RecordCount > 0 ThenDataGrid1.Visible = TrueDataGrid1.SetFocusElseMsgBox "無(wú)可選的供應(yīng)商信息，請(qǐng)首先錄入供應(yīng)商數(shù)據(jù)！", , "提示窗口 "End IfEnd IfEnd IfIf KeyCode = vbKeyReturn And Index = 1 Thenflex1.Col = 1flex1.Row = 1flex1.SetFocusEnd IfEnd SubPrivate Sub DataGrid1_KeyDown(KeyCode As Intege

17、r, Shift As Integer)If KeyCode = vbKeyReturn Thentxt1(0) = Adodc1.Recordset.Fields(" 供應(yīng)商全稱 ")txt1(1).SetFocusDataGrid1.Visible = FalseEnd IfEnd SubPrivate Sub txtEdit_KeyPress(KeyAscii As Integer)'刪除回車符，以消除嘟嘟聲。If KeyAscii = Asc(vbCr) Then KeyAscii = 0 End SubPrivate Sub txtEdit_KeyDown

18、(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.SetFoc

19、usElse Adodc2.RecordSource = " 商品信息表 " Adodc2.RefreshIf Adodc2.Recordset.RecordCount > 0 Then DataGrid2.Visible = True DataGrid2.SetFocusElseMsgBox "無(wú)可選的商品信息，請(qǐng)首先錄入商品數(shù)據(jù)！", , "提示窗口 "End IfEnd IfEnd If'只有 TextBox 控件在“數(shù)量”單元格時(shí)，才使用以下過(guò)程If flex1.Col >= 4 ThenEditKeyCo

20、de flex1, txtEdit, KeyCode, ShiftEnd If End Sub '當(dāng)輸入數(shù)據(jù)并按下 ENTER 鍵，或用鼠標(biāo)單擊 MSHFlexGrid 控件中的另一個(gè)單元時(shí)， '焦點(diǎn)將返回此控件。這時(shí)TextBox 中的文本被復(fù)制到活動(dòng)單元中Private Sub flex1_GotFocus()If txtEdit.Visible = False Then Exit Subflex1 = txtEdit txtEdit.Visible = False view_DPEnd SubPrivate Sub flex1_LeaveCell()If txtEdit.

21、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()Dim l

22、sph As Integer'聲明一個(gè)整型變量'創(chuàng)建入庫(kù)票號(hào)rs1.Open "select * from 入庫(kù)主表 order by 票號(hào) ", Cnn, adOpenStaticIf rs1.RecordCount > 0 ThenIf Not rs1.EOF Then rs1.MoveLastIf rs1.Fields(" 票號(hào) ") <> "" Thenlsph = Val(Right(Trim(rs1.Fields(" 票號(hào) "), 4) + 1 txtph.Text =

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

24、Next 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) = "" ThenMsgB

25、ox "第" & i & " 行錄入錯(cuò)誤！ ", , "提示窗口 "Exit SubEnd IfIf flex1.TextMatrix(i, 1) = "" Thenjs = js + 1End IfNext iIf js = flex1.Rows - 1 ThenMsgBox " 沒(méi)有要保存的數(shù)據(jù)！ ", , " 提示窗口 "Exit SubEnd Ifrs1.Open "select * from 入庫(kù)從表 ", Cnn, adOpen

26、Keyset, adLockOptimistic For i = 1 To flex1.Rows - 1If flex1.TextMatrix(i, 1) <> "" And flex1.TextMatrix(i, 2) <> "" And flex1.TextMatrix(i, 4) <> "" Then商品名稱 ") = flex1.TextMatrix(i, 1) 規(guī)格 ") = flex1.TextMatrix(i, 2) 單位 ") = flex1.TextM

27、atrix(i, 3)'添加新記錄到 "入庫(kù)從表 " 中 rs1.AddNewIf 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(”入庫(kù)數(shù)量 ”)=Val(flex1.TextMatrix(i, 4) rsl.Fields(”單價(jià)")=Val(flex1.TextMatrix(i, 5)rsl.Fields(”金額")=Val(flex1.TextMatrix(i, 6)If flex1.TextMatrix(i, 7) <> "" Then rs1.Fields(" 備注") = flex1.TextMatrix(i, 7)If