VBA Executing CODE from ComboBox

Question

I have a very complicated process that involves downloading several files from different shares, combining these files into worksheets, manipulating and calculating related information, and then exporting certain fields (and calculations) as reports to a number of Excel workbooks.

I have this process encoded so that I can click one button, and the whole process will be carried out from end to end. I have a series of text fields that function as “indicators” (red - this part did not pass, green - this part succeeded). As you can imagine, the code for the whole process is HUGE (32 pages when copying to MSWord) and is hard to digest when I have a problem.

I realized that I want to put the code in a table so that it is much more modular and understandable. I set the combo box with the action I want to take and the second combo box with the message / file / object I want to work with (for example, Delete - Table 2, Acquisition - File 1, Export - Report 4). I managed to create an SQL statement to do simple things like del * from tbl_test and execute them from combo boxes without any problems.

I need to know if there is a way to place in the table (fragment) a field of essentially a code fragment, and then execute this vba code when choosing suitable combos.

IE code for 'Acquire - File1' is completely VBA code; it displays a network drive, finds a file, downloads a file, and moves it to a directory.

IE code for "Scrub - tblMain_Part1" is a combination of vba and sql code; it checks for the presence of a file (vba), if it finds it, it deletes part of the main table (sql) and adds the contents of the found file (sql), and then updates the monitor, indicating that it (vba). If the file is not found, it changes the monitor window to red and updates the title of the command button (vba)

I am not a genius with vba, but I keep mine. The thought process I had was that if I could essentially get the code broken into manageable pieces in a table, I could call the smippets code in that order if I want to start the whole process, or I could just re-execute parts of the code as needed, by choosing a combination of actions and a report / file / object.

Any thoughts / ideas are welcome.

Answer 1

I think it would be better to break the code into Subs. The table you are working in will have a Sub-Name field and a blnSuccess field. Your code will loop if the table runs under each sub, and then updates blnSuccess based on any errors you receive. This will give you the requested result when you try to see what happened.

Answer 2

Consider the use of macros. You do not need a table. Also, consider porting your hard-coded SQL to queries.

Answer 3

I think you should not use a table, just create a module with different subsets for each operation. In the event of your button, after choosing a combo, I would make a case statement.

dim strOperation as string strOperation = me!selectionOne Select Case strOperation Case "delete": deleteTable(me!selectionTwo) Case "export": export(me!selectionTwo) case "acquire": acquire(me!selectionTwo) End Select 

Of course, you will have methods for getting, deleting and exporting written in the module, and what parameters you need for each operation.

This is just one idea of ​​many that you could use for this.

Answer 4

I was about to edit the original answer, but it seems to be disabled at another level.

I think it would be better to split the code into functions that return a string if there is an error. The table in which you are executing the loop will display the strFunction, strError, and strObject fields. Your code will loop, although the table controlling each function is based on the case argument, passing strObject as a string, and then updating strError based on any errors you receive. You can query the table after this process to see which records have errors in them.

If the button is called cmdRunAll, here is the code for it.

 Private Sub cmdRunAll_Click() On Error GoTo ErrHandler Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset("tblCode", dbOpenDynaset, dbSeeChanges) If Not rst.EOF Then With rst .MoveFirst Do While Not .EOF .Edit Select Case !strFunction Case "fExport" !strError = fExport(!strObject) End Select .Update .MoveNext Loop End With End If rst.Close Set rst = Nothing MsgBox "Processes complete" Exit Sub ErrHandler: Debug.Print Err.Description & " cmdRunAll_Click " & Me.Name Resume Next End Sub 

Here is a simple example function

 Public Function fExport(strTable As String) As String On Error GoTo ErrHandler Dim strError As String strError = "" DoCmd.TransferText acExportDelim, , strTable, "C:\users\IusedMyUserNameHere\" & strTable & ".txt" fExport = strError Exit Function ErrHandler: strError = Err.Description Resume Next End Function