问题:
在机房收费系统中有一个修改信息的功能。通过组合查询查到学生的记录,选中一行后,相当于选中了一个学生信息记录,点击修改后,改学生的信息会直接映射到修改窗体的Text控件框中!
其实自己不太会,听了超凡的分析,明白了大概的思路,然后自己一点一点摸索出来了!
过程:
第一小部分测试:窗体传参
我先在一个测试窗体test1放置一次command控件,并写如下代码
1.定义了一个string类型的全局变量txtName
2.点击的过程中给它赋值,并让测试2窗体显示
并让这句话输出在测试2窗体的Text控件中
在设置了一个测试窗体test2,放置一个Text控件用来显示传递过来的参数,不写任何代码。
结果测试:
第二小部分测试:选中一行记录,并让记录全部显示到修改窗体上
1.首先先建立一个查询让结果集返回到MSFlexGrid框里
2.选中哪一行就把哪一行的的数据显示
我的做法:
Private Sub 查询_Click()
Dim txtSQL As String
Dim MsgText As String
Dim mrc As ADODB.Recordset '作为的连接online_info表查询的结果集
txtSQL = "select cardno,studentNo,ondate,OnTime,computer from OnLine_Info"
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF = True Then
MsgBox "查询到0条记录", vbOKOnly + vbInformation, "提示!"
Else
With MSFlexGrid1
.Rows = 1
.CellAlignment = 4
.TextMatrix(0, 0) = "卡号"
.TextMatrix(0, 1) = "学号"
.TextMatrix(0, 2) = "上机日期"
.TextMatrix(0, 3) = "上机时间"
.TextMatrix(0, 4) = "机器名"
.TextMatrix(0, 5) = "选中标记"
Do While Not (mrc.EOF)
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = mrc!cardno
.TextMatrix(.Rows - 1, 1) = mrc!studentNo
.TextMatrix(.Rows - 1, 2) = mrc!ondate
.TextMatrix(.Rows - 1, 3) = mrc!OnTime
.TextMatrix(.Rows - 1, 4) = mrc!computer
.TextMatrix(.Rows - 1, 5) = ""
mrc.MoveNext
Loop
End With
End If
End Sub
Private Sub MSFlexGrid1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
With MSFlexGrid1
test2.Text1.Text = MSFlexGrid1.TextMatrix(.rows-1, 3)
End With
End Sub
Private Sub 修改_Click()
test2.Show
End Sub
测试结果出现问题,发现MSFlexGrid1_MouseDown好像具有一次性。
这样就比较麻烦,如果用户一开始不知道要修改哪个信息或是误点了一条记录,再次想点击正确的记录,可结果一直显示的是第一次点击的记录,就会出问题!
为此,增加改进:
Dim j As Integer '声明一个全局变量来标识所选中的行
Private Sub MSFlexGrid1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
j = MSFlexGrid1.MouseRow
With MSFlexGrid1
test2.Text1.Text = MSFlexGrid1.TextMatrix(j, 3)
End With
End Sub
这样就会点击哪一行,传递哪一行记录的参数
总结:
我觉得这属于跨窗体传参!我不懂怎么让一下子全都过去,然后我就按照自己已有经验,一点一点尝试,一点点的测试代码!最后在这个过程中不光顺着自己的思路做出来,也让我对代码测试有了深的印象!
最后的代码分享给大家继续改进优化!
Public txtSID As String
Public txtName As String
Public ComboSex As String
Public txtDept As String
Public txtGrade As String
Public txtClass As String
Public txtCardNo As String
Public txtBalance As String
Public txtStaus As String
Public txtExplain As String
Public ComboType As String
Private Sub Form_Load()
Dim i, j, k
'加载窗体时依次填充comboBox的下拉列表
For i = 0 To 2
With comboField(i)
.AddItem "卡号"
.AddItem "学号"
.AddItem "姓名"
.AddItem "性别"
.AddItem "系别"
.AddItem "年级"
.AddItem "班级"
End With
Next i
For j = 0 To 2
With comboOpSign(j)
.AddItem "="
.AddItem "<"
.AddItem ">"
.AddItem "<>"
End With
Next j
For k = 0 To 1
With comboCombineRelation(k)
.AddItem "与"
.AddItem "或"
End With
Next k
'如果组合查询框为空(不是“与”也不是“或”),不能添加下面的查询条件
If comboCombineRelation(0).Text = Trim("") Then
comboField(1).Enabled = False
comboField(2).Enabled = False
comboOpSign(1).Enabled = False
comboOpSign(2).Enabled = False
InquiryContent(1).Enabled = False
InquiryContent(2).Enabled = False
comboCombineRelation(1).Enabled = False
End If
If comboCombineRelation(1).Text = Trim("") Then
comboField(2).Enabled = False
comboOpSign(2).Enabled = False
InquiryContent(2).Enabled = False
End If
With MSFlexGrid1
.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) = "时间"
End With
End Sub
'激活
Private Sub comboCombineRelation_Click(Index As Integer)
If comboCombineRelation(0).Text <> Trim("") Then
comboField(1).Enabled = True
comboOpSign(1).Enabled = True
comboCombineRelation(1).Enabled = True
InquiryContent(1).Enabled = True
End If
If comboCombineRelation(1).Text <> Trim("") Then
comboField(2).Enabled = True
comboOpSign(2).Enabled = True
InquiryContent(2).Enabled = True
End If
End Sub
Private Function FieldName(strFieldName As String) As String
Select Case strFieldName
Case "卡号"
FieldName = "cardno"
Case "学号"
FieldName = "studentNo"
Case "姓名"
FieldName = "studentName"
Case "性别"
FieldName = "sex"
Case "系别"
FieldName = "department"
Case "年级"
FieldName = "grade"
Case "班级"
FieldName = "class"
End Select
End Function
Private Function FieldName1(strFieldName As String) As String
Select Case comboCombineRelation(0).Text
Case "与"
FieldName1 = "and"
Case "或"
FieldName1 = "or"
End Select
End Function
Public Function Testtxt(txt As String) As Boolean
If Trim(txt) = "" Then
Testtxt = False
Else
Testtxt = True
End If
End Function
Private Sub cmdInquiry_Click()
Dim MsgText As String
Dim txtSQL As String
Dim mrc As ADODB.Recordset
'把汉字转换成数据库中的字段名,实现第一行查询
If Not Testtxt(comboField(0).Text) Then
MsgBox "请选择字段名", vbOKOnly + vbExclamation, "提示"
comboField(0).SetFocus
Exit Sub
Else
If Not Testtxt(comboOpSign(0).Text) Then
MsgBox "请选择操作符", vbOKOnly + vbExclamation, "提示"
comboOpSign(0).SetFocus
Exit Sub
Else
If Not Testtxt(InquiryContent(0).Text) Then
MsgBox "请输入要查询的内容"
InquiryContent(0).SetFocus
Exit Sub
Else
txtSQL = "select studentNo,studentName,cardno,cash,department,grade,class,sex,status,explain,type,date,time from student_Info where "
StrA = FieldName(comboField(0).Text) & comboOpSign(0).Text & "'" & InquiryContent(0).Text & "'"
txtSQL = txtSQL & StrA
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF Then
MsgBox "该条件的数据不存在", vbOKOnly + vbExclamation, "提示"
Exit Sub
End If
End If
End If
End If
'实现第二行查询
If Not Testtxt(comboCombineRelation(0).Text) Then
GoTo Case1
Else
If Not Testtxt(comboField(1).Text) Then
MsgBox "请选择字段名", vbOKOnly + vbExclamation, "提示"
comboField(1).SetFocus
Exit Sub
Else
If Not Testtxt(comboOpSign(1).Text) Then
MsgBox "请选择操作符", vbOKOnly + vbExclamation, "提示"
comboOpSign(1).SetFocus
Exit Sub
Else
If Not Testtxt(InquiryContent(1).Text) Then
MsgBox "请输入要查询的内容", vbOKOnly + vbExclamation, "提示"
InquiryContent(1).SetFocus
Exit Sub
Else
txtSQL = "select studentNo,studentName,cardno,cash,department,grade,class,sex,status,explain,type,date,time from student_Info where "
StrA = FieldName(comboField(0).Text) & comboOpSign(0).Text & "'" & InquiryContent(0).Text & "'"
strB = FieldName(comboField(1).Text) & comboOpSign(1).Text & "'" & InquiryContent(1).Text & "'"
txtSQL = txtSQL & StrA & FieldName1(comboCombineRelation(0).Text) & " " & strB
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF Then
MsgBox "该条件的数据不存在", vbOKOnly + vbExclamation, "提示"
Exit Sub
End If
End If
End If
End If
End If
'实现第三行查询
If Trim(comboCombineRelation(1).Text) = "" Then
GoTo Case1
Else
If Not Testtxt(comboField(2).Text) Then
MsgBox "请选择字段名", vbOKOnly + vbExclamation, "提示"
comboField(2).SetFocus
Exit Sub
End If
If Not Testtxt(comboOpSign(2).Text) Then
MsgBox "请选择操作符", vbOKOnly + vbExclamation, "提示"
comboOpSign(2).SetFocus
Exit Sub
End If
If Not Testtxt(InquiryContent(2).Text) Then
MsgBox "请输入要查询的内容", vbOKOnly + vbExclamation, "提示"
InquiryContent(2).SetFocus
Exit Sub
End If
txtSQL = "select studentNo,studentName,cardno,cash,department,grade,class,sex,status,explain,type,date,time from student_Info where "
StrA = FieldName(comboField(0).Text) & comboOpSign(0).Text & "'" & InquiryContent(0).Text & "'"
strB = FieldName1(comboCombineRelation(0).Text) & " " & FieldName(comboField(1).Text) & comboOpSign(1).Text & " '" & InquiryContent(1).Text & "'"
strC = FieldName1(comboCombineRelation(1).Text) & " " & FieldName(comboField(2).Text) & comboOpSign(2).Text & " '" & InquiryContent(2).Text & "'"
txtSQL = txtSQL & StrA & strB & strC
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF Then
MsgBox "该条件的数据不存在", vbOKOnly + vbExclamation, "提示"
Exit Sub
End If
End If
Case1:
With MSFlexGrid1
Do While Not mrc.EOF
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc!studentNo) '学号
.TextMatrix(.Rows - 1, 1) = Trim(mrc!studentName) '姓名
.TextMatrix(.Rows - 1, 2) = Trim(mrc!cardno) '卡号
.TextMatrix(.Rows - 1, 3) = Format(mrc!cash) '金额
.TextMatrix(.Rows - 1, 4) = Format(mrc!department) '系别
.TextMatrix(.Rows - 1, 5) = Format(mrc!grade) '年级
.TextMatrix(.Rows - 1, 6) = Format(mrc!Class) '班级
.TextMatrix(.Rows - 1, 7) = Trim(mrc!sex) '性别
.TextMatrix(.Rows - 1, 8) = Format(mrc!Status) '状态
.TextMatrix(.Rows - 1, 9) = Format(mrc!explain) '备注
.TextMatrix(.Rows - 1, 10) = Trim(mrc!Type) '类型
.TextMatrix(.Rows - 1, 11) = Format(mrc!Date) '日期
.TextMatrix(.Rows - 1, 12) = Trim(mrc!Time) '时间
mrc.MoveNext
Loop
End With
End Sub
Private Sub cmdEmpty_Click()
comboField(0) = ""
comboField(1) = ""
comboField(2) = ""
comboOpSign(0) = ""
comboOpSign(1) = ""
comboOpSign(2) = ""
InquiryContent(0) = ""
InquiryContent(1) = ""
InquiryContent(2) = ""
comboCombineRelation(0) = ""
comboCombineRelation(1) = ""
End Sub
Private Sub cmdExit_Click()
Unload Me
End Sub
Private Sub MSFlexGrid1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
Dim j As Integer
j = MSFlexGrid1.MouseRow
With MSFlexGrid1
frmModifyStudentinfo.txtSID.Text = MSFlexGrid1.TextMatrix(j, 0)
frmModifyStudentinfo.txtName.Text = MSFlexGrid1.TextMatrix(j, 1)
frmModifyStudentinfo.ComboSex.Text = MSFlexGrid1.TextMatrix(j, 7)
frmModifyStudentinfo.txtDept.Text = MSFlexGrid1.TextMatrix(j, 4)
frmModifyStudentinfo.txtGrade.Text = MSFlexGrid1.TextMatrix(j, 5)
frmModifyStudentinfo.txtClass.Text = MSFlexGrid1.TextMatrix(j, 6)
frmModifyStudentinfo.txtCardNo.Text = MSFlexGrid1.TextMatrix(j, 2)
frmModifyStudentinfo.txtBalance.Text = MSFlexGrid1.TextMatrix(j, 3)
frmModifyStudentinfo.txtStatus.Text = MSFlexGrid1.TextMatrix(j, 8)
frmModifyStudentinfo.txtExplain.Text = MSFlexGrid1.TextMatrix(j, 9)
frmModifyStudentinfo.ComboType.Text = MSFlexGrid1.TextMatrix(j, 10)
End With
End Sub
Private Sub cmdModify_Click()
'怎样的到选中的那行
frmModifyStudentinfo.Show
End Sub