[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

Passing variables from Form to Query

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 substitute 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
dgran
Asked:
dgran
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I don't think you can put a access form value into the SQL query like that ...
but I presume I will be proven wrong
0
 
mbizupCommented:
angeliii is correct...

You can, however do this in visual basic - behind a command button for example:


Dim strSQL as string
strSQL =  "SELECT TOP " & [Forms]![Form1]![Quantity] & " * INTO DataPull FROM [Table1] ORDER BY Rnd([ID])"

Open in new window



And then use the SQL as the recordsource for a report, form etc:

Me.Recordsource = strSQL

Open in new window

0
 
DoveTailsCommented:
If you dealing with the one form...try  

  Me.Quantity.value

...  you can test your value with a message box:   MsgBox Me.Quantity.Value
0
 
dgranAuthor Commented:
I have the value in a form.  When I click the button, I have it run a specific query.  I'm wanting to pass the variable through to that query somehow.  Can you attach an .accdb file with an example of how to implement the VB code?
Thanks!
0
 
mbizupCommented:
Oh - I just noticed that your query is an append query... so forget what I said about using it as a form's recordsource.

I don't have your tables, etc to work this into a sample... but please do this in your own database:

1.  In your form's design view, add a new command button to demo this method.

2.  In the command button's property sheet, on the Events tab, click the "..." next to On Click and then select Code Builder.

3.  Place this code exactly as I have posted it in between the Sub and End Sub lines:

Dim strSQL as string
strSQL =  "SELECT TOP " & [Forms]![Form1]![Quantity] & " * INTO DataPull FROM [Table1] ORDER BY Rnd([ID])"
Currentdb.Execute strSQL, dbFailOnError 

Open in new window



4. (optional)  If the form your command button is on is Form1, you can simplify it to this:
Dim strSQL as string
strSQL =  "SELECT TOP " & Me.Quantity & " * INTO DataPull FROM [Table1] ORDER BY Rnd([ID])"
Currentdb.Execute strSQL, dbFailOnError 

Open in new window

... and test the new button
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now