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

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

添加MSHFlexGrid将Name改成flex1

添加text将name改成txtedit

Public rs1As New ADODB.Recordset,rs2As New ADODB.Recordset'定义数据集对象Dim i As Integer,j As Integer

Sub EditKeyCode(MSHFlexGrid As Control,Edt As Control,KeyCode As Integer,Shift As Integer)

'标准编辑控件处理。

Select Case KeyCode

Case27'ESC:隐藏焦点并将其返回MSFlexGrid

Edt.Visible=False

MSHFlexGrid.SetFocus

Case13'ENTER将焦点返回MSFlexGrid。

MSHFlexGrid.SetFocus

DoEvents

If MSHFlexGrid.Col

MSHFlexGrid.Col=MSHFlexGrid.Col+1

Else

If MSHFlexGrid.Col=MSHFlexGrid.Cols-1Then

MSHFlexGrid.Row=MSHFlexGrid.Row+1

MSHFlexGrid.Col=1

End If

End If

Case38'向上

MSHFlexGrid.SetFocus

DoEvents

If MSFlexGrid.Row>MSFlexGrid.FixedRows Then

MSFlexGrid.Row=MSFlexGrid.Row-1

End If

End Select

End Sub

Sub view_DP()

If flex1.Col=4Or flex1.Col=5Or flex1.Col=6Then

'格式化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)=Format(flex1.TextMatrix(flex1.Row,6),"0.00") End If

Dim A,B As Single'声明单精度浮点型变量

On Error Resume Next

For i=1To 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

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=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)=.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=1To flex1.Cols-1

flex1.TextMatrix(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)/2

'初始化编辑框

txtEdit=""

flex1.Rows=101

flex1.Cols=8

'设置列标头。

s$="^|^商品名称|^规格|^单位|^数量|^进货价|^金额|^备注"

flex1.FormatString=s$

End Sub

Private Sub flex1_KeyPress(KeyAscii As Integer)

If flex1.Col>=4Or flex1.Col=1Then

MSHFlexGridEdit flex1,txtEdit,KeyAscii

End If

End Sub

'添加下列例程以初始化文本框，并将焦点从Hierarchical FlexGrid传递到TextBox控件：Sub MSHFlexGridEdit(MSHFlexGrid As Control,Edt As Control,KeyAscii As Integer) '使用已输入的字符。

Select Case KeyAscii

'空格表示编辑当前的文本。

Case0To32

Edt=MSHFlexGrid

Edt.SelStart=1000

'其它所有字符表示取代当前的文本。

Case Else

Edt=Chr(KeyAscii)

Edt.SelStart=1

End Select

'在合适的位置显示Edt。

Edt.Move MSHFlexGrid.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>=4Or flex1.Col=1Then

MSHFlexGridEdit flex1,txtEdit,32'模拟一个空格。

End If

End Sub

Private Sub lblSum_Click()

End Sub

Private Sub txt1_KeyDown(Index As Integer,KeyCode As Integer,Shift As Integer) If KeyCode=vbKeyReturn And Index=0Then

Adodc1.RecordSource="供应商信息表where供应商编号like+'%'+'"+txt1(0)+ "'+'%'or供应商全称like+'%'+'"+txt1(0)+"'+'%'or简称like+'%'+'"+txt1(0)+"'+'%'"

Adodc1.Refresh

If Adodc1.Recordset.RecordCount>0Then

DataGrid1.Visible=True

DataGrid1.SetFocus

Else

Adodc1.RecordSource="供应商信息表"

Adodc1.Refresh

If Adodc1.Recordset.RecordCount>0Then

DataGrid1.Visible=True

DataGrid1.SetFocus

Else

MsgBox"无可选的供应商信息，请首先录入供应商数据！",,"提示窗口"

End If

End If

End If

If KeyCode=vbKeyReturn And Index=1Then

flex1.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(KeyAscii 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=1Then

Adodc2.RecordSource="商品信息表where商品名称like+'%'+'"+txtEdit+"'+'%'"

Adodc2.Refresh

If Adodc2.Recordset.RecordCount>0Then

DataGrid2.Visible=True

DataGrid2.SetFocus

Else

Adodc2.RecordSource="商品信息表"

Adodc2.Refresh

If Adodc2.Recordset.RecordCount>0Then

DataGrid2.Visible=True

DataGrid2.SetFocus

Else

MsgBox"无可选的商品信息，请首先录入商品数据！",,"提示窗口"

End If

End If

End If

'只有TextBox控件在“数量”单元格时，才使用以下过程

If flex1.Col>=4Then

EditKeyCode flex1,txtEdit,KeyCode,Shift

End 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.Visible=False Then Exit Sub

flex1=txtEdit

txtEdit.Visible=False

End Sub

Private Sub cmdCancel_Click()

For i=1To flex1.Rows-1

For j=1To 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 Integer'声明一个整型变量

'创建入库票号

rs1.Open"select*from入库主表order by票号",Cnn,adOpenStatic

If rs1.RecordCount>0Then

If Not rs1.EOF Then rs1.MoveLast

If rs1.Fields("票号")<>""Then

lsph=Val(Right(Trim(rs1.Fields("票号")),4))+1

txtph.Text=Date&"rk"&Format(lsph,"0000")

End If

Else

txtph.Text=Date&"rk"&"0001"

End If

rs1.Close

txtDate.Text=Date

'设置控件有效或无效

SetButtons True

For i=1To flex1.Rows-1

For j=1To 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=1To flex1.Rows-1

If flex1.TextMatrix(i,1)<>""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-1Then

MsgBox"没有要保存的数据！",,"提示窗口"

Exit Sub

End If

rs1.Open"select*from入库从表",Cnn,adOpenKeyset,adLockOptimistic

For i=1To 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)<>""Then 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)

rs1.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

End Sub