Run a query from VBA

[hazeleyre23]

Hi,



I have a query in which I want to run from VBA.

The code in the query is:

SELECT DISTINCT tblScN_View.SN, tblSN_View.CPref Left(CPref ,PosOfFirstDigit("ABC123")-1) AS Expr1
FROM tblScN_View
WHERE (((tblSN_View.CPref)=Left([Forms]![Main]![txtCaseN],PosOfFirstDigit([Forms]![Main]![txtCaseN])-1)));

Therefore when a button is click in the form, I want it to bring up the results of this query.

Is this is possible....

Thanks

[ranman256]

Save the SQL as a query then run
docmd.openquery "qsMyQry"

[hazeleyre23]

sorry could you explain in a bit more detail, I am new to VBA Access

thank you

[NoellaG]

Hi,

the easiest way: save the query as qryMyQuery
and then use the code:
Docmd.Openquery "qryMyQuery"

[orange]

tblSN_View.CPref Left(CPref ,PosOfFirstDigit("ABC123")-1) AS Expr1

There is a space between the f and L
that will give an error.

Does the query run properly from the query window?

[NoellaG]

Not much to do than: start a new query in design mode, Create -> query design. Build your query in the design grid, or go to the SQL view and paste your SQL statement there.
Then create the button, and in the event properties On Click -> Event Procedure and there you type the code line

[hazeleyre23]

Hi Yes the query works fine in a query window, however just struggling how to get VBA to run this query for me

thanks