accessvba编程 第九章 vba使用技巧3.docx

1、accessvba编程 第九章 vba使用技巧3ACCESS-VBA编程 第九章 VBA使用技巧3第九章 VBA使用技巧3Treeview 控件的使用方法建立一个窗体，在窗体上放置如下控件：Treeview 控件：名称 Treeview1；Imagelist 控件：名称 Imagelist1，并在该控件中放置三张个性图片（3232），建立索引1、2、3；（方法：在Imagelist 控件上单击鼠标右键选择属性）Label 控件：名称分别为Lab（0）、Lab（1），Caption分别为“父节点：”、“子节点：”；Textbox 控件：名称分别为Txt（0）、Txt（1），text都为“”；co

2、mmandbutton 控件：名称为系统默认，Caption分别为“添加”、“展开”、“收起”、“排序”、“删除”、“退出”；将下列代码加入到代码框：Option ExplicitDim I As IntegerDim J As IntegerDim nodx As NodeDim CunZai As Boolean 定义变量Private Sub Command1_Click()If Txt(0).Text And Txt(1).Text Then 不允许建立零字节的父节点和子节点CunZai = FalseJ = TreeView1.Nodes.CountFor I = 1 To Tree

3、View1.Nodes.Count 检查新输入的父节点名称是否存在If TreeView1.SelectedItem.Children 0 ThenIf Txt(0).Text = TreeView1.Nodes(I).Text Then CunZai = TrueEnd IfNext IIf CunZai = True Then 若存在, 则在父节点下建立子节点Set nodx = TreeView1.Nodes.Add(Txt(0).Text, tvwChild, child & J,Txt(1).Text, 3)Else ,若不存在,则建立父节点和子节点Set nodx = TreeVi

4、ew1.Nodes.Add(, , Txt(0).Text, Txt(0).Text, 1)Set nodx = TreeView1.Nodes.Add(Txt(0).Text, tvwChild, child & J,_Txt(1).Text, 3)End IfTreeView1.RefreshElseIf Txt(0).Text = Then MsgBox 请输入父节点名称!, vbInformation, 警告！系统提示ElseIf Txt(1).Text = Then MsgBox 请输入子节点名称!, vbInformation, 警告！End IfEnd SubPrivate Su

5、b Command2_Click()For I = 1 To TreeView1.Nodes.CountTreeView1.Nodes(I).Expanded = True 展开所有节点Next IEnd SubPrivate Sub Command3_Click()For I = 1 To TreeView1.Nodes.CountTreeView1.Nodes(I).Expanded = False 收起所有节点Next IEnd SubPrivate Sub Command4_Click()TreeView1.Sorted = True 排列顺序End SubPrivate Sub Co

6、mmand5_Click()If TreeView1.SelectedItem.Index 1 ThenTreeView1.Nodes.Remove TreeView1.SelectedItem.Index 删除选定的节点End IfEnd SubPrivate Sub Command6_Click()End 退出程序End SubPrivate Sub Form_Load()TreeView1.LineStyle =TvwTreeLines 在兄弟节点和父节点之间显示线TreeView1.ImageList = ImageList1 链接图像列TreeView1.Style = tvwTre

7、elinesPlusMinusPictureText树状外观包含全部元素Set nodx = TreeView1.Nodes.Add(, , 蒲子明, 蒲子明, 1)建立名称为蒲子明的父节点,选择索引为1的图像Set nodx = TreeView1.Nodes.Add(蒲子明, tvwChild, child01, 收件箱, 3)在蒲子明父节点下建立收件箱子节点,选择索引为3的图像Set nodx = TreeView1.Nodes.Add(蒲子明, tvwChild, child02, 发件箱, 3)在蒲子明父节点下建立发件箱子节点,选择索引为3的图像CunZai = FalseEnd S

8、ubPrivate Sub TreeView1_Expand(ByVal Node As MSComctlLib.Node)Node.ExpandedImage = 2 节点被展开时,选择索引为2的图像End SubPrivate Sub TreeView1_NodeClick(ByVal Node As MSComctlLib.Node)If TreeView1.SelectedItem.Children = 0 Then 检查是否有子节点,0为无For I = 1 To TreeView1.Nodes.CountIf TreeView1.Nodes(I).Selected ThenMsgB

9、ox 您选择的是：“ & TreeView1.Nodes(I).FullPath & ”子节点！系统提示End IfNext IEnd IfEnd SubTreeView控件示例：Private Sub Form_Load()Dim cnn As New ADODB.Connection, rst As New ADODB.RecordsetDim nods As NodesDim mnode As NodeDim nodef As StringDim hh As StringSet cnn = CurrentProject.Connectionrst.Open select * from m

10、enu order by 菜单号, cnn, adOpenStaticrst.MoveFirstDo While Not rst.EOFnodef = rst!菜单号If IsNull(rst!上级菜单) ThenSet mnode = TreeView0.Nodes.Add(, , rst!菜单号, rst!菜单名, 1, 2)Elsenodef = rst!上级菜单Set mnode = TreeView0.Nodes.Add(nodef, tvwChild, rst!菜单号, rst!菜单名, 3, 4)End Ifrst.MoveNextLoopSet rst = NothingWit

11、h TreeView0.Nodes(1).Expanded = TrueEnd WithEnd SubPrivate Sub TreeView0_NodeClick(ByVal Node As Object)Dim varx As Variantvarx = DLookup(记录, menu, 菜单名= & & Node & )Me.记录 = varxEnd Sub如果盘中不存在文件test.dll，则退出数据库if dir(c:windowstest.dll)= thendocmd.quitend if使用 Shell 函数来完成一个用户指定的应用程序。使用 Shell 函数来完成一个用户指

12、定的应用程序。在 MacIntosh 上，默认的驱动名为 “HD” ，路径名称的每部分由冒号而非反斜线分隔。相似地，您可以指定 Macintosh 文件夹而非 Windows. 将第二个参数值设成 1，可让该程序以正常大小的窗口完成，并且拥有焦点。Dim RetValRetVal = Shell(C:WINDOWSCALC.EXE, 1) 完成Calculator。Shell(C:WINDOWShh.exe c:a.chm, vbNormalFocus)hh.exe 是打开chm的程序文件。chm是帮助文件对外部文件管理Set fs = CreateObject(Scripting.FileS

13、ystemObject) 设置系统计算机的驱动器、文件夹和文件记录集fs.CopyFile c:12345.txt, c:abcde.txt 拷贝文件或：filecopy c:a.mdb,d:b.mdbfs.DeleteFile c:12345.txt 删除刚拷贝的文本文件打开外部数据库Private Sub Command5_Click()Dim aobject As String定义对象变量Set aobject = openobject(e:学生规范考查.mdb, True, False)打开名为学生规范考查.mdb的库End Sub提示用户插入软盘如果驱动器中没有软盘则会出现错误，程序

14、应提供没有软盘的信息：Sub InsertDisk()On Error Resume NextIf IsError(MyFile=Dir(“a:”,vbVolume)=True ThenMsgBox “驱动器中没有软盘，请插入软盘！”Exit SubEnd IfEnd Sub向表中加新字段CurrentDb.Execute Alter Table 表名 Add Column 新字段名 Char(13)自定义函数 IsYlwjcct(窗体名) （如果指定的窗体打开，返回True）Function IsYlwjcct(ByVal strFormName As String) As BooleanC

15、onst conObjStateClosed = 0Const conDesignView = 0If SysCmd(acSysCmdGetObjectState,acForm,strFormName) conObjStateClosed ThenIf Forms(strFormName).CurrentViewconDesignView ThenIsYlwjcct=TrueEnd IfEnd IfEnd Function删除当前数据库的表的字段CurrentDb.Execute Alter Table 名表 Drop Column字段名使主程序窗口的X失效Private Declare Fu

16、nction GetSystemMenu Lib User32 (ByVal hwnd As Long, ByVal bRevert As Long) As LongPrivate Declare Function DeleteMenu Lib User32 (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As LongPrivate Sub FORM_Load()Const MF_BYCOMMAND = &H0&Const SC_CLOSE = &HF060Dim hMenu As LonghMenu

17、= GetSystemMenu(Application.hWndAccessApp, 0)Call DeleteMenu(hMenu, SC_CLOSE, MF_BYCOMMAND)End Sub打开模块DoCmd.OpenModule 设置启用禁用shift, 隐藏当前活动窗体me.Form.Visible=True隐藏主窗口Global Const SW_HIDE = 0Global Const SW_SHOWNORMAL = 1Global Const SW_SHOWMINIMIZED = 2Global Const SW_SHOWMAXIMIZED = 3 使用举例 最大化 Acces

18、s 窗口 ?fSetAccessWindow(SW_SHOWMAXIMIZED) 最小化 Access 窗口 ?fSetAccessWindow(SW_SHOWMINIMIZED) 隐藏 Access 窗口 ?fSetAccessWindow(SW_HIDE) 正常显示 Access 窗口 ?fSetAccessWindow(SW_SHOWNORMAL)Option Compare DatabasePrivate Declare Function apiShowWindow Lib user32 Alias ShowWindow (ByVal hwnd As Long, ByVal nCmdS

19、how As Long) As LongFunction fSetAccessWindow(nCmdShow As Long)Dim loX As LongDim loForm As FormOn Error Resume NextloX = apiShowWindow(hWndAccessApp, nCmdShow)Err.ClearfSetAccessWindow = (loX 0)End FunctionPrivate Sub Form_Load()Dim yhsfm As Stringyhsfm = CurrentUser()If yhsfm ylw ThenDim XX = fSet

20、AccessWindow(0)End IfEnd sub在一个窗体中执行另一窗体的子程序来源：爱赛思应用俱乐部 huanghaiDoCmd.OpenForm 窗体2Call Forms(窗体2).aaa禁用主窗口最大化和最小化按钮声明Private Declare Function GetSystemMenu Lib user32.dll _(ByVal hwnd As Long, ByVal bRevert As Long) As LongPrivate Declare Function RemoveMenu Lib user32.dll _(ByVal hMenu As Long, ByV

21、al uPosition As Long, ByVal uFlags As Long) As Long使用Private Sub Form_Load()Dim hSysMenu As LongDim retval As LonghSysMenu = GetSystemMenu(hWndAccessApp, 0)retval = RemoveMenu(hSysMenu, &HF120, &H0)hSysMenu = GetSystemMenu(Me.hwnd, 0)retval = RemoveMenu(hSysMenu, &HF120, &H0)End Sub让主窗口最大化和最小化按钮消失声明

22、：Private Declare Function SetWindowLong Lib user32 _Alias SetWindowLongA (ByVal hwnd As Long, ByVal _nIndex As Long, ByVal dwNewLong As Long) As LongPrivate Declare Function GetWindowLong Lib user32 _Alias GetWindowLongA (ByVal hwnd As Long, ByVal _nIndex As Long) As LongConst WS_MINIMIZEBOX = &H200

23、00Const WS_MAXIMIZEBOX = &H10000Const GWL_STYLE = (-16)使用：Private Sub Form_Load()Dim lWnd As LonglWnd = GetWindowLong(hWndAccessApp, GWL_STYLE)lWnd = lWnd And Not (WS_MINIMIZEBOX)lWnd = lWnd And Not (WS_MAXIMIZEBOX)lWnd = SetWindowLong(hWndAccessApp, GWL_STYLE, lWnd)End Sub计时器触发Me.Text4.Value = Now(

24、)隐藏当前激活的工具条：Dim dqgjt As VariantSet dqgjt = CommandBars.ActiveMenuBardqgjt.Enabled = False显示和隐藏自定义的工具条DoCmd.ShowToolbar 你的工具条名称, acToolbarYesDoCmd.ShowToolbar 你的工具条名称, acToolbarNo隐藏主程序窗口：(详见示例库)Option Compare DatabaseOption ExplicitPrivate Const SW_HIDE = 0Private Const SW_SHOWNORMAL = 1Private Decl

25、are Function apiShowWindow Lib user32 _Alias ShowWindow (ByVal hWnd As Long, _ByVal nCmdShow As Long) As LongPrivate Sub Command0_Click()If Me.Command0.Caption = 隐藏窗体 ThenMe.Command0.Caption = 显示窗体Call apiShowWindow(hWndAccessApp, SW_HIDE)DoCmd.RestoreElseMe.Command0.Caption = 隐藏窗体Call apiShowWindow

26、(hWndAccessApp, SW_SHOWNORMAL)DoCmd.Close acForm, frm_mainDoCmd.ShowToolbar 菜单栏, acToolbarYesDoCmd.RestoreEnd IfEnd Sub主窗口最小化：DoCmd.RunCommand acCmdAppMinimize用代码打开窗体中选项卡控件的某页Me.选项卡控件名.Pages(n).SetFocus其中n是要打开的页号（页号是从0开始的）对不同视图中对象的标题进行设置使用 Caption 属性可以对不同视图中对象的标题进行设置，为用户提供有用的信息：字段标题用于指定通过从字段列表中拖动字段而创建的控件所附标签上的文本，并作为表或查询“数据表”视图中字段的列标题。窗体标题用于指定在“窗体”视图中标题栏上显示的文本。报表标题用于指定在“打印预览”中报表的标题。按钮和标签标题用于指定在控件中显示的文本。String 型，可读写。expression.Captionexpression 必需。返回“Applies To”列表中的一个对象的表达式。