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.

 

[Uncle Gizmo]

Place your code in a query, (as the query SQL) and then call that query Something like this:

Bare in mind that the command button will generate the following code for you!

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

    Dim stDocName As String

    stDocName = "Query1"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command1_Click:
    Exit Sub

Err_Command1_Click:
    MsgBox Err.Description
    Resume Exit_Command1_Click
    
End Sub

 

[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?

 

[Pat Hartman]

The query as it's written will return multiple values since it has no selection criteria.

The best way to obtain lookup values is to use a query that joins the main table to the lookup tables as the RecordSource for the form.

You can use DLookup() as an alternative to look up a single value from a single record but this is the least efficient way.

 

[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

 

[Pat Hartman]

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

There are several methods. You can see three of them in this sample db.