Utterly baffled by something so easy.

[Chunk]

I cant look at this code any longer.

Why doesnt this work:

(In a buttons on click event)

DoCmd.RunSQL "SELECT product_name FROM Product;"

When I enter it in sql view of a new query, it runs perfectly.

 

[tkpstock]

I cant look at this code any longer.

Why doesnt this work:

(In a buttons on click event)

DoCmd.RunSQL "SELECT product_name FROM Product;"

When I enter it in sql view of a new query, it runs perfectly.

The RunSQL command is used for running Action queries - you have a select query which is only used for returning a recordset.

 

[Chunk]

Right I now understand why it doesnt work.

I can see how docmd.openquery works, however, my query returns a single value. How can i store the result of the query into a variable in vb?

 

[Chunk]

I have a more complex query which returns one value. I simplified it to try and work out why it wasnt working.

Is there no way to exectue a query and save the result in a variable in vb?

Im so used to doing this in pl/sql, that its very frustrating not being able to just do it in access and get on with it.

Is there no way to do it?

 

[modest]

Private Sub Command0_Click()
    Dim db As DAO.Database                      'declare variable to hold db/workspace name
    Dim qdf As DAO.QueryDef                     'declare variable to hold query info
    Dim rs As DAO.Recordset                     'declare variable to reference query result table
    
    Dim strSQL As String                        'declare sql string variable
    Dim queryName As String                     'declare query name variable
    Dim table As String                         'declare table name variable
    
    
    Set db = CurrentDb()                        'Set name of database
    queryName = "tempQuery"                     'Set name of temporary query
    table = "[Table Name]"                      'Set name of the table
    
    strSQL = "SELECT * FROM " & table & " WHERE [field name]='" & Me.input.Value & "';"
    
    'If Not QueryExists(queryName) Then
        'Set qdf = db.CreateQueryDef(queryName)
    'Else
        Set qdf = db.QueryDefs(queryName)
    'End If
    
    qdf.SQL = strSQL                            'Match the SQL to the query name
    
    Set rs = db.OpenRecordset(queryName)
    [B]Me.[Where I want to set value on form] = rs![Table Field Name][/B]
End Sub

Must include references to DAO.

And put this in a module:

Public Function QueryExists(queryName As String) As Boolean
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set db = CurrentDb
    QueryExists = False
    
    For Each qdf In db.QueryDefs
        If qdf.Name = queryName Then
            QueryExists = True
            Exit Function
        End If
    Next qdf
End Function