Passing variables from Form to Query

Posted on 2012-08-20
Last Modified: 2012-08-20
I would like to have a form with fields in which I can enter strings (which will be used as criteria in queries) and when a button below is clicked, it will step through a number of queries (macro) inserting the appropriate variables into the appropriate query criteria.

Any answers/links/tutorials/keywords to search for would help.

Question by:dgran
    LVL 47

    Accepted Solution

    Depending on what you have in mind, the simplest method is to refer directly to the form in your query.  For example, you have a control txt_StartDate on your form, then in your query you could use a syntax like:

    SELECT * FROM yourTable WHERE [StartDate] > Forms!YourFormName!txt_StartDate

    Problem is that this syntax might not work because Access/Jet might interpret the form value as a string, rather than a date, so you might need to explicitly declare the variable.

    PARAMETERS [Forms]![YourFormName]![txt_StartDate] DateTime;
    SELECT * FROM yourTable WHERE [StartDate] > Forms!YourFormName!txt_StartDate

    Or you might want to simply evaluate the value of the textbox:

    SELECT * FROM yourTable WHERE [StartDate] > Eval(Forms!YourFormName!txt_StartDate)

    Author Comment

    I'm getting an error on this SQL:

    SELECT TOP [Forms]![Form1]![Quantity] * INTO DataPull
    FROM [Table1]
    ORDER BY Rnd([ID]);

    I'm trying to subsitute the 5100 below with a variable from the form but keep getting an error.

    SELECT TOP 5100 * INTO DataPull
    FROM [Table1]
    ORDER BY Rnd([ID]);

    Error States: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

    I've triple checked the spelling and it is correct.  Any other ideas?
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    Sorry, that is one place that you cannot use a variable.

    If you want to do something like that, you are going to have to write the SQL on the fly, in the click event of a button or something like that.

    Private sub cmd_QueryUpdate_Click

        dim strSQL as string

        strSQL = "SELECT TOP " & me.Quantity & " * INTO DataPull " _
                   & "FROM [Table1] ORDER BY Rnd([ID]);"
        currentdb.Execute strSQL, dbFailOnError

    End Sub

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now