Running SQL from vba

[josie]

I want to run an SQL select statement from a general module, activate on a button click event.
This is my code;

'parameter query to display stock items based on selected distributor
Public Sub DoSQL()
Dim SQL As String

SQL = "SELECT tblStock.DistributorNo FROM tblStock WHERE (((tblStock.DistributorNo)=[enter distributor no])) ORDER BY tblStock.[enter field to order by];"
DoCmd.RunSQL SQL

End Sub

but when I run it a get a needs sql object error - huh'??

I thought I had specified the object - the sql variable.....
Any suggestions??
thanks, josie

 

[pbaldy]

You can only use RunSQL on an action query (append, update, delete...). What is it you're trying to do? From the sound of it, I might set the source of a subform to that SQL to display them to the user.

 

[josie]

Hi Paul,
I am familiar with you as you were kind enough to help solve a previous problem for me - thanks again

What I am trying to do is set up a select statement that when you click a button runs a query to selects records from a stock table. This statement needs to have a 'where' clause that accepts user data for a distributor id as well as an 'orderby' criteria that allows the user to specify a field from the stock table to sort the query on. All going well on clicking 'the button' the query should open in a datasheet view.

Then my part two of this (if I can get that far) is to then ask the user do they want to run another query and if so allow them to re-run the above or if not then end.

But figured I would go with step 1 first and then if I can get it come back to step 2.

Have I clarified or confused :~

josie

 

[namliam]

What I am trying to do is set up a select statement that when you click a button runs a query to selects records from a stock table. This statement needs to have a 'where' clause that accepts user data for a distributor id as well as an 'orderby' criteria that allows the user to specify a field from the stock table to sort the query on. All going well on clicking 'the button' the query should open in a datasheet view.

You can build the SQL in vba, but then use something like:
currentdb.Querydefs("YourQuery").SQL = mySQL

Now "YourQuery" contains the SQL you build in the variable mySQL, you can open it using the Docmd.OpenQuery method.

Then my part two of this (if I can get that far) is to then ask the user do they want to run another query and if so allow them to re-run the above or if not then end.

You will only have 1 query to work with, unless you actually create a new query for each one... but that will get messy real quick.
So running a second query will probably depend upon closing the first one, then allowing the user to run another query.

 

[Banana]

To expand on namliam's comment, you would see faster results if you just create a query normally (e.g. with a query builder), then use parameters to fill in the blanks. If you right-click in the query builder, you should be able to select "Parameters", then insert in two variables and its data type. In the criteria then, type in the variable that will hold the data. Let us suppose you call the variables:

lgDistributorID (Long Integer)

(I'm not sure whether it's possible to create a parameter for OrderBy as I never had to do that before)

Then in VBA:

dim qdf as DAO.querydef

Set qdf = CurrentDb.QueryDefs("NameOfYourQuery")

With qdf
   .lgDistributorID = Me.SomeControlOnForm
   'Repeat for other variables
End With

Me.MySubForm.Form.Recordsource=qdf.SQL

 

[namliam]

(I'm not sure whether it's possible to create a parameter for OrderBy as I never had to do that before)

Nope... Not possible... Which is why I suggested creating the complete SQL in VBA.

 

[boblarson]

If you create a form for displaying the query, you can display it in Datasheet format which will look like the query view. And then you can just set the OrderBy and any filters by using code and not have to rebuild the whole SQL string via code.

 

[namliam]

If you create a form for displaying the query, you can display it in Datasheet format which will look like the query view. And then you can just set the OrderBy and any filters by using code and not have to rebuild the whole SQL string via code.

Offcourse there is more than one way to skin a cat isnt there??

 

[josie]

Thanks all for replies... there are a few to sort thru.
Another late night with the vba and I'll let you all know how I go.
Again, cheers for the pointers - much appreciated