📄 frmfind.frm
字号:
VERSION 5.00
Begin VB.Form frmFind
Caption = "图书信息查询"
ClientHeight = 2220
ClientLeft = 60
ClientTop = 450
ClientWidth = 7005
LinkTopic = "Form1"
ScaleHeight = 2220
ScaleWidth = 7005
StartUpPosition = 3 '窗口缺省
Begin VB.CommandButton cmdexit
Caption = "关闭"
Height = 495
Left = 4320
TabIndex = 9
Top = 1440
Width = 1215
End
Begin VB.CommandButton cmdfind
Caption = "查询"
Height = 495
Left = 1440
TabIndex = 8
Top = 1440
Width = 1215
End
Begin VB.TextBox Text2
BeginProperty Font
Name = "宋体"
Size = 10.5
Charset = 134
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 375
Left = 3960
TabIndex = 7
Top = 840
Width = 2775
End
Begin VB.ComboBox Combo4
Height = 300
Left = 2640
Style = 2 'Dropdown List
TabIndex = 6
Top = 840
Width = 1215
End
Begin VB.ComboBox Combo3
Height = 300
Left = 1440
Style = 2 'Dropdown List
TabIndex = 5
Top = 840
Width = 1095
End
Begin VB.TextBox Text1
BeginProperty Font
Name = "宋体"
Size = 10.5
Charset = 134
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 375
Left = 3960
TabIndex = 4
Top = 360
Width = 2775
End
Begin VB.ComboBox Combo2
Height = 300
Left = 2640
Style = 2 'Dropdown List
TabIndex = 3
Top = 360
Width = 1215
End
Begin VB.ComboBox Combo1
Height = 300
Left = 1440
Style = 2 'Dropdown List
TabIndex = 2
Top = 360
Width = 1095
End
Begin VB.CheckBox Check2
Alignment = 1 'Right Justify
Caption = "条件2"
Height = 375
Left = 480
TabIndex = 1
Top = 840
Width = 855
End
Begin VB.CheckBox Check1
Alignment = 1 'Right Justify
Caption = "条件1"
Height = 375
Left = 480
TabIndex = 0
Top = 360
Value = 1 'Checked
Width = 855
End
End
Attribute VB_Name = "frmFind"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim i As Integer
'定义6个模块变量,储存不同的SQL查询语句
Dim str1 As String, str2 As String, str3 As String, str4 As String
Dim str5 As String, str6 As String
Dim findtype1 As Integer, findtype2 As Integer '用于记录查询字段的类型,0为字符类型,1为非字符类型
Sub find() '子程序,用于存储查询不同的SQL语句
'如果查询的条件为like,则自动在查询的数据后加"%"
If Combo2.Text = "like" And Right(Text1.Text, 1) <> "%" Then
Text1.Text = Text1.Text + "%"
End If
If Combo4.Text = "like" And Right(Text2.Text, 1) <> "%" Then
Text2.Text = Text2.Text + "%"
End If
'语句1,条件1所要查询的字段类型为字符类型
str1 = "select * from bookrecord where " & Combo1.Text & " " & Combo2.Text & " '" & Text1.Text & "'" '字段为字符类
'语句2,条件下所在查询的字段类型为非字符类型
str2 = "select * from bookrecord where " & Combo1.Text & " " & Combo2.Text & Text1.Text '字段为数值类
'语句3,条件1所要查询的字段类型为字符类型
str3 = " and " & Combo3.Text & " " & Combo4.Text & " '" & Text2.Text & " '" '字段为字符类
' 语句4,条件1所要查询的字段类型为字符类型
str4 = " and " & Combo3.Text & " " & Combo4.Text & Text2.Text '字段为数值类
str5 = "select * from bookrecord where " & Combo3.Text & " " & Combo4.Text & " '" & Text2.Text & "'" '字段为字符类
'语句2,条件下所在查询的字段类型为非字符类型
str6 = "select * from bookrecord where " & Combo3.Text & " " & Combo4.Text & Text2.Text '字段为数值类
End Sub
Private Sub cmdExit_Click()
Unload Me
End Sub
Private Sub cmdfind_Click()
On Error GoTo handle '如果发生未知错误,则跳到handle语句后
find '调用子程序,返回SQL语句
If Check1.Value = 1 And Check2.Value = 0 Then '如果选择条件1
If Text1.Text = "" Then
MsgBox "查询数值不能为空"
Exit Sub
End If
If findtype1 = 0 Then '如果查询字段为字符类型
frmBookRecord.AdoBookRecord.RecordSource = str1
Else '查询字段为非字符类型
frmBookRecord.AdoBookRecord.RecordSource = str2
End If
End If
If Check2.Value = 1 And Check1.Value = 0 Then '如果选择条件1
If Text2.Text = "" Then
MsgBox "查询数值不能为空"
Exit Sub
End If
If findtype1 = 0 Then '如果查询字段为字符类型
frmBookRecord.AdoBookRecord.RecordSource = str5
Else '查询字段为非字符类型
frmBookRecord.AdoBookRecord.RecordSource = str6
End If
End If
If Check1.Value = 1 And Check2.Value = 1 Then '如果二个条件都选择
If findtype1 = 0 And findtype2 = 0 Then '如果二个条件所查询的字段均为字符类型
frmBookRecord.AdoBookRecord.RecordSource = str1 & str3
ElseIf findtype1 = 0 And findtype2 = 1 Then '条件1为字符类型,条件2为非字符类型
frmBookRecord.AdoBookRecord.RecordSource = str1 & str4
ElseIf findtype1 = 1 And findtype2 = 1 Then '二个条件都为非字符类型
frmBookRecord.AdoBookRecord.RecordSource = str2 & str3
Else '条件下为非字符类型,条件2为字符类型
frmBookRecord.AdoBookRecord.RecordSource = str2 & str4
End If
End If
frmBookRecord.AdoBookRecord.Refresh '刷新图书基本资料的ADO控件, 显示记录
With frmBookRecord.dgbookrecord '修改表格控件的列名
.Columns(0).Caption = "书号"
.Columns(1).Caption = "书名"
.Columns(2).Caption = "出版社"
.Columns(3).Caption = "作者"
.Columns(4).Caption = "单价"
.Columns(5).Caption = "库存数量"
.Columns(6).Caption = "折扣"
End With
Exit Sub
handle: '错误处理
MsgBox "发生错误，程序重建表内容", , "查询错误"
frmBookRecord.AdoBookRecord.RecordSource = " select * from BookRecord“ "
frmBookRecord.AdoBookRecord.Refresh
End Sub
Private Sub Combo1_Click()
Combo2.Clear '清队组合框2的内容
'如果条件1查询的字段为"SalesPrice",或者"Quantity"或者"Discount"(非字符类型)
If Combo1.Text = "SalesPrice" Or Combo1.Text = "Quantity" Or Combo1.Text = "Discount" Then
'条件1查询字段为非字符类型
findtype1 = 1
'组合框添加查询条件
Combo2.AddItem ">"
Combo2.AddItem "="
Combo2.AddItem "<"
Else '条件1查询字段为字符类型
findtype1 = 0
Combo2.AddItem "="
Combo2.AddItem "like"
End If
Text1.Text = ""
Combo2.ListIndex = 0 '显示组合框的第一项
End Sub
Private Sub Combo3_Click()
Combo4.Clear '清除组合框4的内容
'如果条件2查询的字段为"SalesPrice",或者"Quantity"或者"Discount"(非字符类型)
If Combo3.Text = "SalesPrice" Or Combo3.Text = "Quantity" Or Combo3.Text = "Discount" Then
findtype2 = 1
Combo4.AddItem ">"
Combo4.AddItem "="
Combo4.AddItem "<"
Else
findtype2 = 0
Combo4.AddItem "="
Combo4.AddItem "like"
End If
Text2.Text = ""
Combo4.ListIndex = 0
End Sub
Private Sub Form_Load()
'填充表字段到组合框
For i = 0 To frmBookRecord.AdoBookRecord.Recordset.Fields.Count - 1
Combo1.AddItem frmBookRecord.AdoBookRecord.Recordset.Fields(i).Name
Combo3.AddItem frmBookRecord.AdoBookRecord.Recordset.Fields(i).Name
Next
'显示第一项
Combo1.ListIndex = 0
Combo3.ListIndex = 1
End Sub
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -