?? 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 + -