这个窗体是一个简单的组合查询窗体,之所以它简单是因为它没有涉及到时间控件,所以可以节省不少代码量,但是它有一个难点就是把选中修改,把修改后的信息返还到这个窗体中,所以这个窗体也涉及到另外一个窗体!
代码展示:
学生基本信息维护
Public TmpCardno As String 'TmpCardno称它为“临时卡号”,是您选择的那行数据的列的值
Public tmpload As String 'tmpLoad是在“修改信息窗体”添加的子窗体
Public Function field(strfield As String) As String
Select Case strfield
Case "卡号"
field = "cardno"
Case "学号"
field = "studentNo"
Case "姓名"
field = "studentName"
Case "性别"
field = "sex"
Case "系别"
field = "department"
Case "年级"
field = "grade"
Case "班级"
field = "class"
End Select
End Function
Public Function RelationName(strRelationName As String) As String
Select Case strRelationName
Case "与"
RelationName = "and"
Case "或"
RelationName = "or"
End Select
End Function
Private Sub cmdCheck_Click()
Dim txtsql As String
Dim msgtext As String
Dim mrc As ADODB.Recordset '定义数据集对象
'在Student_info 这张表中获得整行记录
txtsql = "select * from student_Info where "
If Trim(comboField(0).Text) = "" Or Trim(ComboOpSign(0).Text) = "" Or Trim(txtInquriryContent(0).Text) = "" Then
MsgBox "请将选项内容填写完整!", vbOKOnly, "提示"
Exit Sub
Else
txtsql = txtsql & " " & field(comboField(0).Text) & " " & ComboOpSign(0).Text & " '" & Trim(txtInquriryContent(0).Text) & "'"
If ComboCombineRelation(0).Text <> "" Then
If Trim(comboField(1).Text) = "" Or Trim(ComboOpSign(1).Text) = "" Or Trim(txtInquriryContent(1).Text) = "" Then
MsgBox "请将第二行选项内容填写完整!", vbOKOnly, "提示"
Exit Sub
Else
txtsql = txtsql & " " & RelationName(ComboCombineRelation(0).Text) & " " & field(comboField(1).Text) & " " & ComboOpSign(1).Text & "'" & Trim(txtInquriryContent(1).Text) & "'"
If ComboCombineRelation(1).Text <> "" Then
If comboField(2).Text = "" Or ComboOpSign(2).Text = "" Or txtInquriryContent(2).Text = "" Then
MsgBox "请将第三行内容填写完整", vbOKOnly, "温馨提示"
Exit Sub
Else
txtsql = txtsql & " " & RelationName(ComboCombineRelation(1).Text) & " " & field(comboField(2).Text) & " " & ComboOpSign(2).Text & "'" & Trim(txtInquriryContent(2).Text) & "'"
End If
End If
End If
End If
End If
Set mrc = ExecuteSQL(txtsql, msgtext)
If mrc.EOF Then
MsgBox "无数据,请重新填写", vbInformation
comboField(0).SetFocus
comboField(0).Text = ""
MSHFlexGrid1.Clear
Else
With MSHFlexGrid1
.Rows = 1
.CellAlignment = 4
.TextMatrix(0, 0) = "学号"
.TextMatrix(0, 1) = "姓名"
.TextMatrix(0, 2) = "卡号"
.TextMatrix(0, 3) = "金额"
.TextMatrix(0, 4) = "系别"
.TextMatrix(0, 5) = "年级"
.TextMatrix(0, 6) = "班级"
.TextMatrix(0, 7) = "性别"
.TextMatrix(0, 8) = "状态"
.TextMatrix(0, 9) = "备注"
.TextMatrix(0, 10) = "类型"
.TextMatrix(0, 11) = "日期"
.TextMatrix(0, 12) = "时间"
Do While Not mrc.EOF
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc.fields(1))
.TextMatrix(.Rows - 1, 1) = Trim(mrc.fields(2))
.TextMatrix(.Rows - 1, 2) = Trim(mrc.fields(0))
.TextMatrix(.Rows - 1, 3) = Trim(mrc.fields(7))
.TextMatrix(.Rows - 1, 4) = Trim(mrc.fields(4))
.TextMatrix(.Rows - 1, 5) = Trim(mrc.fields(5))
.TextMatrix(.Rows - 1, 6) = Trim(mrc.fields(6))
.TextMatrix(.Rows - 1, 7) = Trim(mrc.fields(3))
.TextMatrix(.Rows - 1, 8) = Trim(mrc.fields(10))
.TextMatrix(.Rows - 1, 9) = Trim(mrc.fields(8))
.TextMatrix(.Rows - 1, 10) = Trim(mrc.fields(14))
.TextMatrix(.Rows - 1, 11) = Trim(mrc.fields(12))
.TextMatrix(.Rows - 1, 12) = Trim(mrc.fields(13))
mrc.MoveNext
Loop
End With
End If
End Sub
Private Sub cmdDelete_Click()
MSHFlexGrid1.Clear
If Not (mrc.EOF Or mrc.BOF) Then
mrc.fields(0) = Trim(txtCardNo.Text)
mrc.fields(1) = Trim(txtSID.Text)
mrc.fields(2) = Trim(txtName.Text)
mrc.fields(3) = Trim(comboSex.Text)
mrc.fields(4) = Trim(txtDept.Text)
mrc.fields(5) = Trim(txtGrade.Text)
mrc.fields(6) = Trim(txtClass.Text)
mrc.fields(7) = Trim(txtmoney.Text)
mrc.fields(8) = Trim(txtexplain.Text)
mrc.fields(10) = Trim(txtstate.Text)
mrc.fields(14) = Trim(Combotype.Text)
mrc.Update
mrc.Close
End If
Me.Hide
'修改信息后,重新加载到基本信息维护窗体的查询结果中
End Sub
'End Function
Private Sub cmdModify_Click()
'TmpCardno = MSHFlexGrid1.TextMatrix(MSHFlexGrid1.RowSel, 0) '选择一行数据
frmeditstuinfo.txtSID.Text = MSHFlexGrid1.TextMatrix(MSHFlexGrid1.RowSel, 0)
frmeditstuinfo.txtClass.Text = MSHFlexGrid1.TextMatrix(MSHFlexGrid1.RowSel, 6)
frmeditstuinfo.txtName.Text = MSHFlexGrid1.TextMatrix(MSHFlexGrid1.RowSel, 1)
frmeditstuinfo.txtCardNo.Text = MSHFlexGrid1.TextMatrix(MSHFlexGrid1.RowSel, 2)
frmeditstuinfo.comboSex.Text = MSHFlexGrid1.TextMatrix(MSHFlexGrid1.RowSel, 7)
frmeditstuinfo.txtmoney.Text = MSHFlexGrid1.TextMatrix(MSHFlexGrid1.RowSel, 3)
frmeditstuinfo.txtDept.Text = MSHFlexGrid1.TextMatrix(MSHFlexGrid1.RowSel, 4)
frmeditstuinfo.txtexplain.Text = MSHFlexGrid1.TextMatrix(MSHFlexGrid1.RowSel, 9)
frmeditstuinfo.txtGrade.Text = MSHFlexGrid1.TextMatrix(MSHFlexGrid1.RowSel, 5)
frmeditstuinfo.Combotype.Text = MSHFlexGrid1.TextMatrix(MSHFlexGrid1.RowSel, 10)
frmeditstuinfo.txtstate.Text = MSHFlexGrid1.TextMatrix(MSHFlexGrid1.RowSel, 8)
frmeditstuinfo.Show '显示修改信息窗体
MsgBox "请先选择数据,再进行修改!", , "温馨提示"
Exit Sub
End Sub
Private Sub Form_Load()
comboField(0).AddItem "卡号"
comboField(0).AddItem "学号"
comboField(0).AddItem "姓名"
comboField(0).AddItem "性别"
comboField(0).AddItem "系别"
comboField(0).AddItem "年级"
comboField(0).AddItem "班级"
comboField(1).AddItem "卡号"
comboField(1).AddItem "学号"
comboField(1).AddItem "姓名"
comboField(1).AddItem "性别"
comboField(1).AddItem "系别"
comboField(1).AddItem "年级"
comboField(1).AddItem "班级"
comboField(2).AddItem "卡号"
comboField(2).AddItem "学号"
comboField(2).AddItem "姓名"
comboField(2).AddItem "性别"
comboField(2).AddItem "系别"
comboField(2).AddItem "年级"
comboField(2).AddItem "班级"
ComboOpSign(0).AddItem "="
ComboOpSign(0).AddItem "<>"
ComboOpSign(1).AddItem "="
ComboOpSign(1).AddItem "<>"
ComboOpSign(2).AddItem "="
ComboOpSign(2).AddItem "<>"
ComboCombineRelation(0).AddItem "与"
ComboCombineRelation(0).AddItem "或"
ComboCombineRelation(1).AddItem "与"
ComboCombineRelation(1).AddItem "或"
End Sub
Private Sub MSHFlexGrid1_Click()
MSHFlexGrid1.SelectionMode = flexSelectionByRow '单击时选择整行
MSHFlexGrid1.FocusRect = flexFocusNone '在当前但愿的周围画一个焦点框
MSHFlexGrid1.HighLight = flexHighlightWithFocus '该值决定了所选定的单元是否突出显示
End Sub
Private Sub MSHFlexGrid1_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim tmpselect As Boolean
tmpselect = True
End Sub
Private Sub txtInquriryContent_KeyPress(Index As Integer, KeyAscii As Integer)
Dim ctemp As String
ctemp = "#$%^&*" '禁止输入的字符
If InStr(1, ctemp, Chr(KeyAscii)) <> 0 Then
KeyAscii = 0
End If
End Sub
修改学生信息
Private Sub cmdOk_Click()
Dim txtsql As String
Dim msgtext As String
Dim mrc As ADODB.Recordset
txtCardNo.Enabled = False
txtsql = "select * from student_info where cardno= '" & Trim(txtCardNo.Text) & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
If mrc.EOF And mrc.BOF = True Then
mrc.fields(0) = Trim(txtCardNo.Text)
mrc.fields(1) = Trim(txtSID.Text)
mrc.fields(2) = Trim(txtName.Text)
mrc.fields(3) = Trim(comboSex.Text)
mrc.fields(4) = Trim(txtDept.Text)
mrc.fields(5) = Trim(txtGrade.Text)
mrc.fields(6) = Trim(txtClass.Text)
mrc.fields(7) = Trim(txtmoney.Text)
mrc.fields(8) = Trim(txtexplain.Text)
mrc.fields(10) = Trim(txtstate.Text)
mrc.fields(14) = Trim(Combotype.Text)
mrc.Update
Do While Not mrc.EOF
With MSHFlexGrid1
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc.fields(1))
.TextMatrix(.Rows - 1, 1) = Trim(mrc.fields(2))
.TextMatrix(.Rows - 1, 2) = Trim(mrc.fields(0))
.TextMatrix(.Rows - 1, 3) = Trim(mrc.fields(7))
.TextMatrix(.Rows - 1, 4) = Trim(mrc.fields(4))
.TextMatrix(.Rows - 1, 5) = Trim(mrc.fields(5))
.TextMatrix(.Rows - 1, 6) = Trim(mrc.fields(6))
.TextMatrix(.Rows - 1, 7) = Trim(mrc.fields(3))
.TextMatrix(.Rows - 1, 8) = Trim(mrc.fields(10))
.TextMatrix(.Rows - 1, 9) = Trim(mrc.fields(8))
.TextMatrix(.Rows - 1, 10) = Trim(mrc.fields(14))
.TextMatrix(.Rows - 1, 11) = Trim(mrc.fields(12))
.TextMatrix(.Rows - 1, 12) = Trim(mrc.fields(13))
mrc.MoveNext
End With
Loop
MsgBox "无任何修改", 0 + 48, "警告"
End If
Me.Hide
End Sub
Public Sub tmpload()
Dim txtsql As String
Dim msgtext As String
Dim mrc As ADODB.Recordset
txtsql = "select * from student_info where cardno= '" & Trim(txtCardNo.Text) & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
If mrc.EOF Then
MsgBox "加载信息不存在,请确认后重新操作", 0 + 48, "警告"
Else
txtSID.Text = mrc!studentNo
txtName.Text = mrc!studentname
comboSex.Text = mrc!sex
txtDept.Text = mrc!department
txtGrade.Text = mrc!grade
txtClass.Text = mrc!Class
txtCardNo.Text = mrc!cardno
txtmoney.Text = mrc!cash
txtstate.Text = mrc!status
txtexplain.Text = mrc!explain
Combotype.Text = mrc!Type
mrc.Close
End If
End Sub
Private Sub txtSID_KeyPress(KeyAscii As Integer)
Dim ctemp As String
ctemp = "#$%^&*" '禁止输入的字符
If InStr(1, ctemp, Chr(KeyAscii)) <> 0 Then
KeyAscii = 0
End If
Select Case KeyAscii
Case 48 To 57 '只能输入数字
Case 65 To 90 '只能输入大小写字母
Case 97 To 122 ' 只能输入退格
Case 8
Case Else
KeyAscii = 0
End Select
End Sub
看完代码之后,你学会了吗?有什么创新的地方吗?