How to prompt user for parm value from module

Posted on 2006-03-27
Medium Priority
Last Modified: 2011-10-03
I've found stuff all over the Internet that hits around my question, but nothing that directly addresses the issue.  I have a module that I'm using to output a text file.  Part of the data that is output is going to come from a parameter query I've built.  My goal is to be able to simply run the module and be prompted for the parameter.  Most of what I've read deals with getting the parameter value from a form, but in this case, a form is neither necessary nor desired.  The module and db will only ever be used/accessed by myself and my boss.  I've fiddled with the code (both DAO and ADO methods) to open the query and pass it the parameter value, but I keep running into errors.  The primary one being "Object variable or With Block variable not set."  Below is the current state of my code.

    Dim cmd As New ADODB.Command
    Dim rst As New ADODB.Recordset
    Dim param As ADODB.Parameter
    'Dim qry As DAO.QueryDef
   ' Dim db As DAO.Database
    'Dim parm As DAO.Parameter
    'Dim rst As DAO.Recordset

    Set cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "GET_TMFR_DATA"      'name of param query
    For Each param In cmd.Parameters
        param.Value = Eval(param.Name)
    Next param
    Set rst = cmd.Execute(cmd.CommandText, param)

    'Set qry = db.QueryDefs("GET_TMFR_DATA")
    'qry.Parameters(0) = "FS06"
    'Set rst = qry.OpenRecordset

The lines that are commented out are the DAO method which yields the error "Object variable or With Block variable not set.
The ADO method yields the error "Invalid SQL statement; expected DELETE, INSERT, PROCEDURE, SELECT, or UPDATE statement"
Question by:xhat
  • 2
LVL 27

Accepted Solution

jjafferr earned 500 total points
ID: 16303417
use the Inputbox

YourParm =inputbox("what is the value you want to enter")

then YourParm have the value you want , and you can use it in your code.


Author Comment

ID: 16304607
Of course! How obvious.  Thanks for showing me the forest. The trees were in the way.
LVL 27

Expert Comment

ID: 16304634
it must have been foggy too :o)

you are welcome,
thanks for the points and the grade


Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question