VBA在Microsoft Access中生成永久控件 [英] VBA to generate permanent Controlls in Microsoft Access

问题描述

情况: 我从Outlook下载附件，然后根据一些条件将其导入到Access表中.

Situation: I download Attachments from Outlook and import them into Access tables based on a few criteria.

例如，我下载以下文件: SenderA_A_Timestamp.csv，SenderA_B_Timestamp.csv，SenderB_C_Timestamp.csv，SenderC_A_Timestamp.csv并加载更多内容.

For example, I download following files: SenderA_A_Timestamp.csv, SenderA_B_Timestamp.csv, SenderB_C_Timestamp.csv, SenderC_A_Timestamp.csv and loads more.

现在，我制作了一个表单，用户可以选择一个日期之后的所有C类型CSV文件，或者某个日期之后的所有类型.

Now I have made a form where the user can, for example, select all type C CSV-Files since a date or just all types, since a certain date.

群集，主机和卷表示文件类型. datetime指示上次更新的时间. Cl_Import，Hosts_Import和Volume_Import是不同文件所需的导入规范.

Cluster, Hosts and Volume represent the File types. The datetime indicates when it was updated last. Cl_Import, Hosts_Import and Volume_Import are the import specification needed for the different files.

问题: 现在，我想在音量"下添加一个新类型，并希望下次打开表单时将其保留在该位置.

Problem: I now want to add a new type, for example, under Volume and want to have it stay there when I open the Form the next time.

软件: -Microsoft Access 2016 -SQL查询 -展望2016 -VBA

Software: -Microsoft Access 2016 -SQL queries -Outlook 2016 -VBA

推荐答案

创建一个名为SwitchboardItems的表.
添加以下字段:

Create a table called SwitchboardItems.
Add these fields:

• ItemNumber(数字，主键)
• ItemText(文本)
• 命令(数字)
• 参数(文本)

ItemNumber应该是连续的(如果需要，请使用AutoNum).
ItemText是将显示在表单上的文本.
Command指示按下按钮时的操作.
Argument与该选项有关(例如，要打开的文件名).

ItemNumber should be sequential (use AutoNum if you want).
ItemText is the text as it will appear on the form.
Command indicate what to do when the button is pressed.
Argument is anything pertinent to that option (such as the file name to be opened).

创建一个空白的Continuous表单.
记录来源为:
SELECT * FROM SwitchboardItems WHERE [ItemNumber]>0 ORDER BY [ItemNumber];
ItemNumber 0是菜单的标题，因此在记录源中将被忽略.
在表单属性的Data选项卡上将Data Entry，Allow Additions，Allow Deletions，Allow Edits和Allow Filters转到No

Create a blank Continuous form.
The Record Source is:
SELECT * FROM SwitchboardItems WHERE [ItemNumber]>0 ORDER BY [ItemNumber];
ItemNumber 0 is the heading for the menu so is ignored in the record source.
Turn Data Entry, Allow Additions, Allow Deletions, Allow Edits and Allow Filters to No on the Data tab in the form properties

在表单的Detail部分中添加命令按钮和文本框.
将按钮的名称命名为Option1，并将文本框命名为OptionLabel1.
文本框的Control Source是ItemText.
在Form Header上添加标签，将其命名为Label1.

Add a command button and a textbox to the Detail section of your form.
Give the button the name of Option1 and the textbox OptionLabel1.
The Control Source for the textbox is ItemText.
Add a label to the Form Header name it Label1.

将以下代码添加到Form_Open事件中.它将菜单标题放在表单标题中:

Add the below code to the Form_Open event. It places the menu heading in the form header:

Private Sub Form_Open(Cancel As Integer)

    Me.Label1.Caption = DLookup("ItemText", "SwitchboardItems", "[ItemNumber]=0")
    Me.Requery

End Sub

将此代码添加到表单模块:

Add this code to the form module:

Private Sub Option_Click()

    Select Case Command
        Case 1
            'Add code to set references to Excel, etc...
            'Dim wrkbk As Object
            'Set wrkbk = workbooks.Open([Argument])
        Case 2
            MsgBox [Argument]
        Case 3
            DoCmd.OpenForm [Argument], acNormal
        Case 4
            DoCmd.OpenReport [Argument], acViewNormal
        Case 5

        Case 6
            DoCmd.Close
        Case 7
            DoCmd.OpenQuery [Argument]
        Case 8
    End Select

End Sub

每个Case语句反映SwitchboardItems表中的Command值，您应该编写每个操作的代码(打开工作簿，关闭数据库，运行SQL等).

Each Case statement reflects the Command value in the SwitchboardItems table and you should code what you want each to do (open workbooks, close the database, run SQL, etc).

最后，将此代码添加到Option1文本框和命令按钮的单击事件中:

Finally, add this code to the click events for the Option1 textbox and command button:

Private Sub Option1_Click()
    Option_Click
End Sub

Private Sub OptionLabel1_Click()
    Option_Click
End Sub

您完成的表格将类似于以下内容(我单击了 Other_Import 按钮，当 Command 为2时，该按钮显示了一个消息框):

Your finished form will look similar to this (I've clicked Other_Import button which displays a message box as Command is 2):

注意:，您还可以添加更多内容-例如，表中的最后一个字段作为上一次更新时间.

NB: There's a lot more you could add to this - the last updated time as an extra field in the table for example.

这篇关于VBA在Microsoft Access中生成永久控件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！