VBA to generate permanent Controlls in Microsoft A

Question

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

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.

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.

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.

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

Answer 1

Create a table called SwitchboardItems.
Add these fields:

• ItemNumber (Numeric, Primary Key)
• ItemText (Text)
• Command (Numeric)
• Argument (Text)

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).

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

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.

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

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).

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

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.