SQL from Query to code

[cneill]

Below is the sql from my query

SELECT tblAccountSchemes.SiteID, tblAccountSchemes.SchemeID, TblScheme.Scheme, TblScheme.SortOrder
FROM TblScheme INNER JOIN tblAccountSchemes ON TblScheme.SchemeID = tblAccountSchemes.SchemeID
WHERE (((tblAccountSchemes.SiteID)=[Forms]![FrmMainAccountSchemes]![txtNodeKey]))
ORDER BY tblAccountSchemes.SiteID, TblScheme.SortOrder;

I want it to run from code I tried using
Docmd.Runsql but I could not get it to work can some one show me where I am going wrong

 

[ProgramError]

Docmd.Runsql only works with action queries. Select is not an action query.


Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[VBACT]

You are missing a few arguments in the Docmd command.

In your code declare the sql above as strSQL. then say Docmd.runsql and the required arguments

 

[Remou]

I agree with ProgramError. cneill it is best to say what you wish to achieve.

 

[Golom]

Think of "SELECT ..." as a function** in VB. It returns a value (specifically, it returns a recordset which will contain zero or more records.) You must use a construct that provides a place to store that returned value.

Something More like

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(... Your SELECT Statement ...)

** - Apologies to the purists. It's only an analogy ... not to be taken too literally.