前言
组合查询真不愧是三大难点之一,在做组合查询的时候用了一周的时间,出现各种问题,经过看博客,向其他人讨教,终于完成了,当时就只做了个组合查询,能让它运行,满足查询条件,可是把修改学生信息没做,最后这两天才做的,现在分享出来。
一、维护窗体
通过对这两个窗体的操作来实现自己想要达到的效果
1.查询
2.信息修改
二、逻辑
*1.通过查询窗体将信息查找出来,在查找的时候可以查找一条记录,这时就可以不用组合查询了,做起来也很简单,但是要查询好几条记录就不容易了,必须通过组合查询来实现,同时满足两个组合关系。
*
2.在信息查找出来以后觉得哪里需要修改,就可以之间点修改,能有修改这个功能也很符合现实生活。假如A办了一张健身房的卡,那么他不想去了,有可能想去别的健身房了,那么他这个卡里还有钱,怎么办呢?这时就可以转让给B了,将卡的信息修改为B的信息。修给信息的时候要考虑到卡号是唯一的,也是主键,是不能修改的,其他信息是可以修改的。修改完了为了确定修改成功,而且很快能查出来,就可以是查询表里的信息立马更新,这样可以减少很多时间。
三、部分代码
为了减少代码冗余,利用数组和for 循环,将信息加载到界面中,
> For i = 0 To 2
> With Combo1(i)
> .AddItem "卡号"
> .AddItem "学号"
> .AddItem "姓名"
> .AddItem "性别"
> .AddItem "上机日期"
> .AddItem "上机时间"
> .AddItem "下机日期"
> .AddItem "下机时间"
> .AddItem "机器名"
> End With
> Next i
>
> For j = 0 To 2
> With Combo2(j)
> .AddItem "="
> .AddItem "<"
> .AddItem ">"
> .AddItem "<>"
> End With
>
> Next j
> `For k = 0 To 1
> With Combo3(k)
> .AddItem "与"
> .AddItem "或"
> End With Next k
`
字段名转换
> Select Case StrFiledName
> Case "卡号"
> FiledName = "cardno"
> Case "学号"
> FiledName = "studentNo"
> Case "姓名"
> FiledName = "studentName"
> Case "性别"
> FiledName = "Sex"
> Case "学院"
> FiledName = "department"
> Case "年级"
> FiledName = "grade"
> Case "班级"
> FiledName = "class"
> Case "与"
> FiledName = "and"
> Case "或"
> FiledName = "or"
> End Select
组合查询,按条件查找
txtSQL = "select * from student_Info where " & FiledName(Combo1(0).Text) _
& " " & Combo2(0).Text & " '" & Trim(Text1(0).Text) & " '"
Set mrc = ExecuteSQL(txtSQL, MsgText)
If Trim(Combo3(0).Text <> "") Then '第一个组合查询
If Trim(Combo1(1).Text) = "" Or Trim(Combo2(1).Text) = "" Or Trim(Text1(1).Text) = "" Then
MsgBox "您选择了第一个组合关系,请输入数据之后再查询", vbOKOnly, "提示信息"
Exit Sub
Else
txtSQL = txtSQL & FiledName(Combo3(0).Text) & " " & FiledName(Combo1(1).Text) _
& Combo2(1).Text & "'" & Trim(Text1(1).Text) & "'"
Set mrc = ExecuteSQL(txtSQL, MsgText)
End If
End If
If Trim(Combo3(1).Text) <> "" Then '第二个组合关系
If Trim(Combo1(2).Text) = "" Or Trim(Combo2(2).Text) _
= "" Or Trim(Text1(2).Text) = "" Then
MsgBox "您选择了第二个组合,请输入数据之后在查询", vbOKOnly, "提示"
Exit Sub
Else
txtSQL = txtSQL & FiledName(Combo3(1).Text) & " " & FiledName(Combo1(2).Text) & Combo2(2).Text & "'" & Trim(Text1(2).Text) & "'"
End If
End If
通过修改按钮将查询到的信息赋值到修改学生信息表
> Private Sub cmdMod_Click()
> tmpcard = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 0)
>
> frmModifyinformation.txtSID = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 1)
> frmModifyinformation.txtName = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 2)
> frmModifyinformation.ComboSex = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 3)
> frmModifyinformation.txtDept = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 4)
> frmModifyinformation.txtGrade = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 5)
> frmModifyinformation.txtClass = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 6)
> frmModifyinformation.txtCardNo = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 0)
> frmModifyinformation.txtCash = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 7)
> frmModifyinformation.txtStatus = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 10)
> frmModifyinformation.txtExplain = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 8)
> frmModifyinformation.ComboType = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 13)
>
> frmModifyinformation.Show
>
> MsgBox "请对选择数据进行修改!", , "温馨提示"
> Exit Sub End Sub
按下鼠标选中要赋值的哪一行
> Private Sub MSFlexGrid1_MouseDown(Button As Integer, Shift As Integer,
> x As Single, y As Single)
>
> With MSFlexGrid1
>
> .Row = .MouseRow '把选中行的值给.row
> nowrow = .Row '把鼠标指定行给nowrow
> .col = 0
> .ColSel = .Cols - 1
>
> End With
> End Sub
加载信息,并修改
> txtSQL = "select * from student_Info where cardno='" & Trim(tmpcardno)
> & "'"
> Set mrc = ExecuteSQL(txtSQL, MsgText)
> If mrc.EOF Then
> MsgBox "加载信息不存在,请确认后重新操作!", , "温馨提示"
> 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
> txtCash.Text = mrc!cash
> txtStatus.Text = mrc!Status
> txtExplain.Text = mrc!explain
> ComboType.Text = mrc!Type
> mrc.Close
> End If End Sub
更新修改的信息
> txtSQL = "select * from student_Info where cardNo ='" &
> Trim(txtCardNo.Text) & "'" Set mrc = ExecuteSQL(txtSQL, MsgText) '
> If Not (mrc.BOF Or mrc.EOF) 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(txtCash.Text)
> mrc.Fields(8) = Trim(txtExplain.Text)
> mrc.Fields(10) = Trim(txtStatus.Text)
> mrc.Fields(14) = Trim(ComboType.Text)
> mrc.Update
> mrc.Close
> End If
> '修改信息后,重新加载到基本信息维护窗体的查询结果中
> frmStuInfoMain.cmdQuery.Value = True
> Me.Hide
结语:看似简单,可做起来不好做,只要认真思考总会做出来,办法总比困难多。