3065 run time error ...cannot execute a SELECT query

[kdre]

Hi,

I'm new to VBA and cannot figure this one out. I'm sure it's quite simple and is probably a rookie error, so I'd appreciate any help.

I'm executing the following code and get the 3065 run time error when I attempt it. The text of the error is "cannot execute a SELECT query". What I'm trying to do is obtain a value from a different table based on a value of a control in the current form.


Dim ls_holdamt
Dim rsmt As DAO.Recordset, dbs As DAO.Database

Set dbs = CurrentDb

sqlstring = "SELECT Bondclass!bondamount AS [ls_holdamt] FROM Bondclass WHERE Bondclass!class = Me!Combo83.Value;"

dbs.Execute sqlstring


Thanks for any suggestions!!

Kathy in Colorado

 

[fmbpcsup]

try if this alteration will work:

sqlstring = "SELECT Bondclass.bondamount AS [ls_holdamt] FROM Bondclass WHERE Bondclass.class = " & Me!Combo83.Value

Your combo83 value has to be added to the string by concatenation otherwise it won't find anything in the class field whose contents are exactly "Me!combo83.value"

 

[kdre]

I understand your suggestion, but it did not help. I still get the same error. I also removed Me!Combo83.Value
and replaced it with "B299" (in double quotes), which is a valid class value on the Bondclass table. Still received the same error.

Any other ideas? Or, there must be other ways to retrieve data from a different table within the same database while in a form. I'm very open to suggestions here.....

Kathy

 

[fmbpcsup]

from access help:"The Execute method is valid only for action queries. If you use Execute with another type of query, an error occurs. Because an action query doesn't return any records, Execute doesn't return a Recordset. "

maybe you want to open the recordset and work with the record(s) your sql statement obtains. so, replace the execute line with:

set rsmt=dbs.openrecordset(sqlstring, dbopendynaset)
rsmt.movefirst 'make first record current
...
<add code to use data from record>
...
rsmt.close 'close the recordset when done with it

 

[KF]

TRY THIS

Dim strSQL as String

strSQL= &quot;SELECT bondamount AS [ls_holdamt] FROM Bondclass WHERE class = &quot; & Me!Combo83.Value

DoCmd.RunSQL strSQL

 

[Guest_imported]

db.execute only runs action queries. Either create a recordset:
set rs = currentdb.openrecordset(strSQL)

or

use dlookup (gets 1st value)