Syntax To run saved query and not see the results.

[NightWalker]

I have the following code i wrote. I use this query in many subroutines. I am trying to figure out how to write the syntax to execute the query without seeing the results in a datasheet. I got the query to work in the query builder. The name of the query is qryGetPartName. The reason i am wanting to be able to call the actual query is so if i have to change it i can change the one and all the code will work everywhere it is used. i want to replace the sql =

If Not IsNull(cboVendor.Value) Then
    varVendor = cboVendor.Value
'    Set rst = CurrentDb.OpenRecordset("qryGetPartName")
'    sql1 = rst
'    Debug.Print sql1
    sql = "SELECT qryVendorToPart.PartID, qryVendorToPart.PartNumber, qryVendorToPart.PartName, qryVendorToPart.VendorID " & _
          "FROM qryVendorToPart " & _
          "WHERE qryVendorToPart.VendorID =" & varVendor
    Set rst = CurrentDb.OpenRecordset(sql)
    cboPartNumber.RowSource = sql1
    cboPartNumber.Requery

[Gicu]

And why don't you use the query? Are you getting an error for missing parameters? Here is how I set recorsdsets based on saved queries:

Dim db as DAO.Database, rst as Dao.recordset, qdf as DAO.querydef, prm as Parameter

Set db=CurrentDb
Set qdf=db.QueryDefs("qryGetPartName")

For each prm in qdf.Parameters
   prm.value=Eval(prm.Name)
Next prm

Set rst=qdf.OpenRecordset

Cheers,

[NightWalker]

Gicu,

I am trying to use the actual query. I just have not figured out how to write the code to run the query instead of using the sql statement. I want to replace the select statement with the actual query (qryGetPartName). I tried running some code with the docmd.openquery but that runs the query and displays a datasheet view with the results and I don't want to see the results i want to use them in the rest of the subroutine. Thank you.

[Gicu]

But you are using a recordset which is what the code I gave you does. To get the SQL you can simply use strSQL=qdf.SQL:

Dim strSQL as string
Dim db as DAO.Database, rst as Dao.recordset, qdf as DAO.querydef, prm as Parameter

Set db=CurrentDb
Set qdf=db.QueryDefs("qryGetPartName")

For each prm in qdf.Parameters
   prm.value=Eval(prm.Name)
Next prm

Set rst=qdf.OpenRecordset

strSQL=qdf.SQL

[davegri]

This might be what you need. It will create or modify an existing query with the name and sql string passed to it.

'---------------------------------------------------------------------------------------
' Procedure : fcnMakeNamedQuery
' DateTime  : 9/26/2006 20:57
' Author    : davegri
' Purpose   : Attach new SQL property to an existing querydef. If the Query doesn't exist,
'               : create it with the passed SQL.
'---------------------------------------------------------------------------------------
'
Function fcnMakeNamedQuery(qName As String, strPassedSQL As String)
    Dim qthisQuery As DAO.QueryDef
    If DCount("Name", "MSysObjects", "[Name] = " & Chr$(39) & qName & Chr$(39)) = 0 Then
        Set qthisQuery = CurrentDb.CreateQueryDef(qName, strPassedSQL)
    Else
        Set qthisQuery = CurrentDb.QueryDefs(qName)
        qthisQuery.SQL = strPassedSQL
    End If
    Application.RefreshDatabaseWindow
    Set qthisQuery = Nothing
End Function

In your example, make the combobox rowsource qryGetPartName, not in code, but in the property window.
Then just modify the query with the function before using the combobox.

From your original post, you seem to think you need to run a query to use it. Not so, any more than you have to "run" a table to use it.

[NightWalker]

DaveGri,

From your original post, you seem to think you need to run a query to use it. Not so, any more than you have to "run" a table to use it.

How do i get the information from the query without running it? I have queries made and saved with my forms and reports. I went through and created lots of queries to replace all the instances of the red parts of my original post. I read that this is better practice to be able to make changes easier. When i double click on a query it runs and i get the information in a datasheet view. That information is what i need to be able to use in my code. I assumed that the Select SQL statement in my code runs to be able to get the information but it doesn't display it. Am I missing something here? I have no formal programming training so please forgive me if this is something i already should know. If you have something i could read to understand this better I would love to have the link or file. Thank you for your help. --Walker

[davegri]

DaveGri,



How do i get the information from the query without running it? I have queries made and saved with my forms and reports. I went through and created lots of queries to replace all the instances of the red parts of my original post. I read that this is better practice to be able to make changes easier. When i double click on a query it runs and i get the information in a datasheet view. That information is what i need to be able to use in my code. I assumed that the Select SQL statement in my code runs to be able to get the information but it doesn't display it. Am I missing something here? I have no formal programming training so please forgive me if this is something i already should know. If you have something i could read to understand this better I would love to have the link or file. Thank you for your help. --Walker

You can use a query name just like a table name, without invoking the SQL. I assume qryGetPartName's SQL is identical to the red SQL in your original post.
That code is a mystery to me as to what it is trying to do. In the form's property sheet you could set the rowsource of cboPartNumber to qryGetPartName and eliminate ALL that code.

[NightWalker]

I assume qryGetPartName's SQL is identical to the red SQL in your original post.

Yes, both of the SQL's are identical.

That code is a mystery to me as to what it is trying to do.

I am using the query to filter the items available in a second combobox based on which vendor is selected in the first combobox. I have vendors that send me multiple parts and some parts are made by multiple vendors.

In the form's property sheet you could set the rowsource of cboPartNumber to qryGetPartName and eliminate ALL that code.

I didn't think i could use the rowsource on the form for this process. I use this query in many locations to filter what is available to the user when they are looking for a part number and name.

[davegri]

I use this query in many locations

You mean this exact unchanging NAMED (qryGetPartName) query? Not the SQL but the actual NAMED query? Then by all means you can use it anywhere you want, just like it was a table.

[NightWalker]

Yes that exact named query. I have been trying to use it but i dont know the syntax to use it in place of the red text in my original post. I am sorry it took so long for you to understand what i was trying to do. I am not good with all of the terminology for what i am wanting to accomplish.

[davegri]

Read the last sentence in post #7 again.

[NightWalker]

I will try that. Thank you.