Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 11th, 2008, 02:02 PM
Registered User
Join Date: Mar 2008
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Procedure name in variable

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.

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
Old March 12th, 2008, 06:26 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

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?


Look it up at: http://wrox.books24x7.com
Old March 12th, 2008, 10:26 AM
Registered User
Join Date: Mar 2008
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts

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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Use Temp Table Variable in Stored Procedure rweide SQL Server ASP 2 April 15th, 2011 03:50 PM
pass java variable to xsl variable kathy1016cats XSLT 1 June 14th, 2006 06:23 PM
Object Variable or With Block Variable Not Set Iashia06 Access 1 May 22nd, 2006 10:24 AM
Passing Variable MS Proj to SQL Stored Procedure Abaxt Access VBA 2 July 7th, 2005 11:05 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.