running a SELECT SQL in VBA

Question

I would like to learn how to run a simple SELECT query in VBA and reference the fields returned by the query.

I have the statements below:

Dim MySQL as DAO.QueryDef

Set MySQL = CurrentDB.QueryDefs("  ")

MySQL.SQL = "SELECT [mess] FROM [etypes] WHERE [etypecode] = 'CURR';"

MySQL.Execute

I have 2 problems:

I don't know what goes here :    .QueryDefs("don't know what goes between the quotes")

but when and if I can get this to work, do I just reference the field returned from the query as MySQL.mess?

Answer 1

The Execute method executes an 'action' query; it does not open a query in datasheet view, which I assume is what you are attempting.  For this sort of 'on the fly' task I use the following function:

Public Function OpenTempQuery(strSQL As String)

    Dim qdf As DAO.QueryDef

    Static n As Integer

    n = n + 1

    ' delete temporary querydef object if exists

    On Error Resume Next

    CurrentDb.QueryDefs.Delete "Temp" & n

    Select Case Err.Number

        Case 0

        ' no error

        Case 3265

        ' temporary querydef does not exist

        ' ignore error

        Case Else

        ' unknown error

        MsgBox Err.Number, vbExclamation, "Error"

    End Select

    ' create temporary querydef object

    Set qdf = CurrentDb.CreateQueryDef("Temp" & n)

    CurrentDb.QueryDefs("Temp" & n).SQL = strSQL

    ' open query and then delete temporary querydef object

    DoCmd.OpenQuery "Temp" & n

    ' delete temporary querydef object

    CurrentDb.QueryDefs.Delete "Temp" & n

End Function

Call it like this:

OpenTempQuery "SELECT [mess] FROM [etypes] WHERE [etypecode] = 'CURR'"

Because the integer variable n is declared as Static, giving each temporary querydef object's name a distinct suffix, the function can be called multiple times while leaving each datasheet open.  This can be useful for ad hoc 'what if?' comparisons, which is why I wrote it in the first place.

Answer 2

You were close! All you need is to add the syntactically required quote marks since the field is of Text datatype. Unless there's some other need for the value from the form, though, you don't need to create a variable for it; you can just concatenate the form reference directly:

Set rst = CurrentDb.OpenRecordset("select [mess] from etypes where email_type ='" & Me!txtEmailType & "'")

This will construct a string like

select [mess] from etypes where email_type ='WORK'

HOWEVER... it looks like you're going all around the barn to do something pretty simple. Why open a recordset at all? What is the context of the code? If you just want to look up one value from one unique record, you can just use DLookUp:

TheAnswer = DLookUp("mess", "ETypes", "[email_type] = '" & Me!txtEmailType & "'")

Answer 3

Can I ask what you think is wrong with this syntax of the Set rst statement below?  Applying the same logic as before, but this time the value of email_type is not 'CURR", but instead a value chosen from a dropdown box by the user.  I ran the MsgBox command for my benefit just to see if the dropdown box object contained the correct value, and it does.  As the user, I have chosen the value of "NCURR" from the dropdown box. 

Dim rst As DAO.Recordset

MsgBox "txtEmailType " & Me!txtEMAILTYPE

Set rst = CurrentDb.OpenRecordset("select [mess] from etypes where email_type =txtEmailType")

I keep getting errors, among them 'Too few parameters.  Expected 1."

I have tried these combinations in the Change event of the object txtEmailType:

Set rst = CurrentDb.OpenRecordset("select [mess] from etypes where email_type =txtEmailType")

Set rst = CurrentDb.OpenRecordset("select [mess] from etypes where email_type =" & txtEmailType)

Set rst = CurrentDb.OpenRecordset("select [mess] from etypes where email_type =" & txtEmailType.value)

Set rst = CurrentDb.OpenRecordset("select [mess] from etypes where email_type =txtEmailType.value")

Set rst = CurrentDb.OpenRecordset("select [mess] from etypes where email_type =" + txtEmailType)

Set rst = CurrentDb.OpenRecordset("select [mess] from etypes where email_type = me!txtEmailType")

Set rst = CurrentDb.OpenRecordset("select [mess] from etypes where email_type =" & Me!txtEMAILTYPE)

Answer 4

Thank you for replying so quickly.  This solution was super fast and super easy.  It worked like a charm!  Thank you!  Why couldn't there be a simple explanation in the Access VBA Programming for Dummies book?  Live and learn.

Answer 5

Actually, if you looking to do a simple select, then you don't need nor want to use a query def.

The simple approach is thus  this:

Dim rst     As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("select [mess] from etypes where etypecde = 'CURR'")

Do While rst.EOF = False

   MsgBox "value of mess = " & rst!mess

   rst.MoveNext

Loop

rst.Close

The above will display the value of column mess for each row (so we hope there is only a few rows in the table that match curr)

Best regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada