CODEWITHSUNDEEP
ms-access
Steven Crandall
Steven Crandall

Reputation: 43

VBA Executing CODE from a ComboBox

I have a very complex process that involves downloading a number of files from different shares, concatenating those files into working tables, manipulating and calculating related information, and then exporting specific fields (and calculations) as reports into a number of Excel workbooks.

I have this process coded so that I can click one button and the entire process will execute end to end. I have a series of text boxes that function as 'indicators' (red - this part failed, green - this part succeeded). As you can imagine, the code for the entire process is HUGE (32 pages when copied into MSWord) and difficult to weed through when I have a problem.

I got it into my mind that I wanted to put the code into a table so that it was much more modular and easier to deal with. I have setup a combo box with the action that I want to take and a second combo box with the report/file/object that I want to work with (ie Delete - Table 2, Acquire - File 1, Export - Report 4). I have been successful at creating the SQL statement to do simple things like del * from tbl_test and execute that from the combo boxes without any issue.

What I need to know is if there is a way to put what is essentially a code snippet into the table (memo field) and then have that vba code execute when I select the matching combos.

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

IE the code for 'Scrub - tblMain_Part1' is a combination of vba and sql code; it checks for the existence of a file (vba), if it finds it, it deletes a portion of the main table (sql) and appends the contents of the file it finds (sql), then it updates the monitor to indicate that it is completed (vba). If the file is not found, it changes the monitor box to red and updates a command button caption (vba)

I am NOT a genius with vba, but I hold my own. The thought process I had was that if I can essentially get the code broken into managable chunks in the table, I could call the code smippets in order if I want to run the entire process, or I could just re-execute portions of the code as needed by selecting the action and report/file/object combination.

Any thoughts/ideas are appreciated.

Upvotes: 4

Views: 783

Answers (4)

HelloW
HelloW

Reputation: 1617

I was going to edit the original answer but this seems to be off on a different tack....

I think it would be best to split the code into functions that return a string if there is an error. The table you loop through would have a strFunction,strError and strObject fields. Your code would loop though the table running each function based on the case statement while passing the strObject as a string and then updating strError based on any errors you receive. You could 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 sample 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

Upvotes: 0

Scotch
Scotch

Reputation: 3226

I think that you shouldn't use a table, just create a module with different subs for each operation. On your button event, after the combo selections, I would do 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'd have your acquire, delete, and export methods written in a module and have whatever parameters you need for each operation there.

This is just one idea of many that you could use to approach this.

Upvotes: 1

Beth
Beth

Reputation: 9607

Consider using macros. You shouldn't need a table. Also, consider moving your hard-coded SQL to queries.

Upvotes: 1

HelloW
HelloW

Reputation: 1617

I think it would be best to split the code into Subs. The table you loop through would have a Sub-Name field and a blnSuccess field. Your code would loop though the table running each sub and then updating blnSuccess based on any errors you receive. This would give you queryable result set when you try to see what happened.

Upvotes: 2

Related Questions