I'm trying something new with an Access database and I need to know if this is even possible.

I have a table containing a list of forms, reports and procedures that is displayed by a list box on a form. The user selects an option from the list box, hits a command button ("Go") and the code runs for the selected object.

Here's the thing - the object names and types are held in the table. When I pull it into the code, the field is a variable of sorts holding the data that I need. With both the form and the report, using the variable is supported easily:

DoCmd.OpenForm rs!vName
-where rs is the recordset and vName is the name of the form.

Ditto with the reports. But how can I call a procedure the same way? Is it possible?


Here is the table format and the code, for reference.

tblVarious
vID = AutoNumber
vName = Text 'the name of the form/report/proc
vAlias = Text 'what's actually shown to the user in the list box
vType = Number '1 for form, 2 for report, 3 for proc

Private Sub Go_Click()
    Dim rs as recordset

    Set rs=CurrentDB.OpenRecordset("SELECT * FROM tblVarious WHERE vID = " & lstSelection, dbOpenSnapshot)
    Select Case rs!vType
    Case 1 'Form
        DoCmd.OpenForm rs!vName
    Case 2 'Report
        DoCmd.OpenReport rs!vName, acViewPreview
    Case 3 'Procedure
        'How to do this??
    End Select

End Sub

How many procedures are you talking about here? You could always do a Select Case again. Something like:

...
Case 3
   Select Case rs!vName
     Case "FirstProcName"
        Run first procedure
     Case "SecondProcName"
        Run Second Procedure
     ...
   End Select
End Select

Would that work?

mmcdonal

Look it up at: http://wrox.books24x7.com

It works, but it defeats the purpose of trying something new. I guess in the end it just means more code by doing it the traditional way, but I would like to see if anyone even thinks this is possible.