Passing variables from Form to Query

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.

Thanks!
dgranAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
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)
0
 
dgranAuthor Commented:
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?
0
 
Dale FyeCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.