?? 萬能查詢frm.frm
字號(hào):
VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Begin VB.Form Form1
Caption = "Form1"
ClientHeight = 7230
ClientLeft = 60
ClientTop = 345
ClientWidth = 8505
LinkTopic = "Form1"
ScaleHeight = 7230
ScaleWidth = 8505
StartUpPosition = 3 '窗口缺省
Begin VB.CommandButton Command5
Caption = "Command5"
Height = 255
Left = 2040
TabIndex = 7
Top = 2160
Width = 1215
End
Begin VB.CommandButton Command4
Caption = "Command4"
Height = 255
Left = 2040
TabIndex = 6
Top = 1800
Width = 1215
End
Begin VB.CommandButton Command3
Caption = "Command3"
Height = 375
Left = 2040
TabIndex = 5
Top = 1200
Width = 1095
End
Begin VB.CommandButton Command2
Caption = "Command2"
Height = 375
Left = 2040
TabIndex = 4
Top = 720
Width = 1215
End
Begin VB.CommandButton Command1
Caption = "Command1"
Height = 375
Left = 1920
TabIndex = 3
Top = 120
Width = 1215
End
Begin VB.TextBox Text1
Height = 495
Index = 0
Left = 120
TabIndex = 2
Text = "Text1"
Top = 600
Width = 1455
End
Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1
Height = 2175
Left = 960
TabIndex = 0
Top = 2760
Width = 3255
_ExtentX = 5741
_ExtentY = 3836
_Version = 393216
End
Begin VB.Label Label1
Caption = "Label1"
Height = 375
Index = 0
Left = 120
TabIndex = 1
Top = 120
Width = 855
End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'##############################################################################################
'##### 功能:智能查詢、模擬查詢當(dāng)前目錄下*.mdb數(shù)據(jù)庫文件， #####
'##### 可把查詢結(jié)果用電子表格打開編輯，也可把打印查詢結(jié)果。 #####
'##### 浙江磐安文化 #####
'##### E:zzwwbb2008@163.com #####
'##############################################################################################
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'1、加入Label1和Text1二控件，設(shè)置其Index均為0 '
'2、加入Command1Command2、Command3、Command4、Command5按鈕 '
'3、加入MSFlexGrid1 '
'4、引用Microsoft Scriping Runtime '
'5、引用Microsoft DAO 3.60 Object Library，其中Access97為Microsoft DAO 3.51 Object Library '
'6、引用Microsoft Excel 9.0 Object Library，其中Excel97為Microsoft Excel 8.0 Object Library '
'再用以下代碼完全覆蓋 '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim MyAppPath As String '數(shù)據(jù)庫路徑
Dim MyRecordsetName As String '數(shù)據(jù)表名稱
Dim MyDatabasePathAndName As String '數(shù)據(jù)庫路徑及名稱
Dim s As String '暫存由用戶界面所輸入的SQL語句
Dim MyTxt As String '寫入的是“查詢.txt”還是“打印.txt”
Dim RstFieldsType() As Integer '記錄字段的類型
Dim MyDatabaseFieldsCount As Integer '數(shù)據(jù)表字段數(shù)
Dim MyDatabaseRecordCount As Double '數(shù)據(jù)表記錄數(shù)
Dim FieldLookValue As Boolean '核對(duì)數(shù)據(jù)類型
Dim MyViewBoolean As Boolean '預(yù)覽否
Private Sub Form_Load()
'初始化
If Right$(App.Path, 1) <> "\" Then
MyAppPath = App.Path & "\"
Else
MyAppPath = App.Path
End If
If Dir(MyAppPath + "*.mdb") = "" Then
MsgBox "當(dāng)前位置沒（*.mdb）數(shù)據(jù)庫文件，請(qǐng)?jiān)黾樱。。?quot;, 48, "差錯(cuò)信息！！！"
Unload Me
Exit Sub
End If
'歷遍當(dāng)前目錄下的數(shù)據(jù)庫（暫時(shí)少于20個(gè)）供其選擇，若只有1個(gè)，直接用之。
Dim MyMdb(1 To 20) As String
i = 1
MyFile = Dir(MyAppPath + "*.mdb")
Do Until MyFile = "" Or i > 20
MyMdb(i) = MyFile
'MsgBox MyMdb(i)
MyFile = Dir
i = i + 1
Loop
If i - 1 > 1 Then '當(dāng)前目錄下存在多個(gè)Mdb數(shù)據(jù)庫時(shí)，供其選擇！
Dim MyInputBox As String
For j = 1 To i - 1
MyInputBox = MyInputBox & j & "、“" & MyMdb(j) & "”" & Chr(13)
Next
MyInputBox = InputBox(MyInputBox & Chr(13) _
& "——請(qǐng)?jiān)谙旅孑斎霐?shù)據(jù)庫的序號(hào)——" _
, "當(dāng)前目錄下有多個(gè)數(shù)據(jù)庫，請(qǐng)選擇數(shù)據(jù)庫！")
If StrPtr(MyInputBox) = 0 Then '用戶取消操作
MsgBox "你選擇取消操作！！！", 48, "提示信息！！！"
Unload Me
Exit Sub
ElseIf Not IsNumeric(MyInputBox) Then
MsgBox "你輸入的不是數(shù)字！！！", 48, "提示信息！！！"
Unload Me
Exit Sub
ElseIf MyInputBox < 1 Or MyInputBox > i Then
MsgBox "你輸入的數(shù)字不在范圍內(nèi)！！！", 48, "提示信息！！！"
End If
MyDatabasePathAndName = MyAppPath + MyMdb(MyInputBox)
ElseIf i - 1 = 1 Then
MyDatabasePathAndName = MyAppPath + Dir(MyAppPath + "*.mdb")
End If
Dim dbs As Database, rst As Recordset
Set dbs = OpenDatabase(MyDatabasePathAndName)
'歷遍當(dāng)前數(shù)據(jù)庫下的數(shù)據(jù)表（暫時(shí)少于20個(gè)）供其選擇，若只有1個(gè)，直接用之。
Dim MyRs(1 To 20) As String
i = 1
For Each j In dbs.TableDefs
MyRecordsetName = j.Name
If Left(MyRecordsetName, 4) <> "MSys" Then ' Exit For '查找數(shù)據(jù)表名稱
MyRs(i) = MyRecordsetName
'MsgBox MyRs(i)
i = i + 1
End If
Next
If i - 1 > 1 Then '數(shù)據(jù)庫下存在多個(gè)數(shù)據(jù)表時(shí)，供其選擇！
MyInputBox = ""
For j = 1 To i - 1
MyInputBox = MyInputBox & j & "、“" & MyRs(j) & "”" & Chr(13)
Next
MyInputBox = InputBox(MyInputBox & Chr(13) _
& "——請(qǐng)?jiān)谙旅孑斎霐?shù)據(jù)表的序號(hào)——" _
, "當(dāng)前數(shù)據(jù)庫下有多個(gè)數(shù)據(jù)表，請(qǐng)選擇數(shù)據(jù)表！")
If StrPtr(MyInputBox) = 0 Then '用戶取消操作
MsgBox "你選擇取消操作！！！", 48, "提示信息！！！"
Unload Me
Exit Sub
ElseIf Not IsNumeric(MyInputBox) Then
MsgBox "你輸入的不是數(shù)字！！！", 48, "提示信息！！！"
Unload Me
Exit Sub
ElseIf MyInputBox < 1 Or MyInputBox > i Then
MsgBox "你輸入的數(shù)字不在范圍內(nèi)！！！", 48, "提示信息！！！"
End If
MyRecordsetName = MyRs(MyInputBox)
ElseIf i - 1 = 1 Then
MyRecordsetName = MyRs(1)
End If
Set rst = dbs.OpenRecordset(MyRecordsetName)
MyDatabaseFieldsCount = rst.Fields.Count '字段數(shù)
MyDatabaseRecordCount = rst.RecordCount '記錄數(shù)
With MSFlexGrid1
.Cols = MyDatabaseFieldsCount
.Rows = 2
.AllowUserResizing = flexResizeColumns '允許用戶調(diào)整列寬
.Row = 0
.Col = 0
.FixedCols = 0
.Left = 360
.Width = (Screen.Width - MSFlexGrid1.Left) * 0.98
.ToolTipText = "單擊數(shù)據(jù)網(wǎng)格時(shí)，將在條件輸入處顯示當(dāng)前行數(shù)據(jù)；雙擊數(shù)據(jù)網(wǎng)格時(shí)，將按列排序！"
End With
For i = 1 To MyDatabaseFieldsCount - 1
Load Label1(i)
Label1(i).Visible = True
Load Text1(i)
Text1(i).Visible = True
Next
ReDim RstFieldsType(0 To MyDatabaseFieldsCount - 1)
For jj = 1 To Int((MyDatabaseFieldsCount + 4) / 5)
For ii = 1 To 5
i = (jj - 1) * 5 + (ii - 1)
If i < MyDatabaseFieldsCount Then
iiiii = Screen.Width / Screen.TwipsPerPixelX / 800 '按800*600的屏幕像素進(jìn)行伸縮
With Label1(i)
.Caption = rst.Fields(i).Name
.Height = 250 * iiiii
.Top = 120 * iiiii + (jj - 1) * 600 * iiiii
.Width = (12000 - 360 * 2) * iiiii / 5
.Left = 360 * iiiii + (12000 - 360 * 2) * iiiii / 5 * (ii - 1)
.Alignment = 2
End With
With Text1(i)
.Text = ""
.Height = 300 * iiiii
.Top = 350 * iiiii + (jj - 1) * 600 * iiiii
.Width = (12000 - 360 * 2) * iiiii / 5
.Left = 360 * iiiii + (12000 - 360 * 2) * iiiii / 5 * (ii - 1)
.ToolTipText = "你若在“" _
& Label1(i).Caption _
& "”處輸入條件，按“查詢”按扭或者按回車后將顯示結(jié)果！！！"
End With
RstFieldsType(i) = rst.Fields(i).Type
With MSFlexGrid1
.ColWidth(i) = 11208 * iiiii / MyDatabaseFieldsCount
.Col = i: MSFlexGrid1.Text = rst.Fields(i).Name
End With
End If
Next
Next
With Me
.Left = 0
.Top = 0
.Height = Screen.Height
.Width = Screen.Width
.Caption = "數(shù)據(jù)庫（" _
& Dir(MyDatabasePathAndName) _
& "）————" & "表（" _
& MyRecordsetName _
& "）————查出" & 0 & "條記錄"
End With
With Command1
.Left = 3460
.Caption = "電子表格形式打開(&O)"
.Top = 450 + Text1(MyDatabaseFieldsCount - 1).Top
.Height = 495: Command1.Width = 1215
.ToolTipText = "按此按扭，將在以電子表格形式打開查詢結(jié)果！！！"
.Enabled = False
End With
With Command2
.Left = 4780
.Caption = "打印報(bào)表(&P)"
.Top = Command1.Top
.Height = 495
.Width = 1215
.ToolTipText = "按此按扭，將打印報(bào)表！！！打印前，先調(diào)整數(shù)據(jù)網(wǎng)格各列的合適寬度！！！"
.Enabled = False
End With
With Command3
.Left = 6100
.Caption = "查詢(&F)"
.Top = Command1.Top
.Height = 495
.Width = 1215
.ToolTipText = "按此按扭，將在下面顯示查詢結(jié)果！！！"
End With
With Command4
.Caption = "清除(&D)"
.Left = 7420
.Top = Command1.Top
.Height = 495
.Width = 1215
.ToolTipText = "按此按扭，將清除輸入條件！！！"
.Enabled = False
End With
With Command5
.Left = 8740
.Caption = "退出(&Q)"
.Top = Command1.Top
.Height = 495
.Width = 1215
.ToolTipText = "按此按扭，將退出程序！！！"
End With
With MSFlexGrid1 '動(dòng)態(tài)調(diào)整頂端與高度
.Top = 650 + Command1.Top
.Height = (Screen.Height - MSFlexGrid1.Top) * 0.85
End With
rst.Close
dbs.Close
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號(hào)
Ctrl + =
減小字號(hào)
Ctrl + -