Problem using SQL command in RecordSet

[alexfc]

Hi there,

My doubt is about how to do a SQL command and store it in a variable.Like this:

Private Sub main()
Dim BD As DAO.Database 'Banco de Dados
Dim content As DAO.Recordset 'Tabela

Set BD = OpenDatabase("P:\Access\db1.mdb", False, True)
Set content = BD.OpenRecordset("select * from COTACAO where cod_item =" & cod)

End Sub

The objective is to put the select in the variable "content".I dont know if the sintax is right...but its guetting me an error.So,how could I do this?

[hansup]

I dont know if the sintax is right...but its guetting me an error.Unfortunately you told us neither the text of the error message nor which line in your code triggers the error. So I'll suggest you revise your procedure like this:

Private Sub main()
Dim BD As DAO.Database 'Banco de Dados
Dim content As DAO.Recordset 'Tabela
Dim strSql As String
strSql = "select * from COTACAO where cod_item =" & cod
Debug.Print strSql
Set BD = OpenDatabase("P:\Access\db1.mdb", False, True)
Set content = BD.OpenRecordset(strSql)
' do what you want with the recordset
content.Close
Set content = Nothing
BD.Close
Set BD = Nothing
End Sub

Storing the completed text of the SELECT statement in a variable allows you to display it in the Immediate window with Debug.Print. So you can go to the Immediate window (Ctrl+g keyboard shortcut) to view it. If you don't see anything wrong with it, you can test it by copying the text and pasting it into SQL View of a new Access query. When you try to run the query, Access will let you know whether the db engine thinks it's valid.

The reason I've focused on the SELECT statement is because you're using something called "cod" to build it, and we have no idea what "cod" is or where it comes from. However, we don't know that the OpenRecordset line is the error trigger. Perhaps the previous line, OpenDatabase, is where the error happens.

Also, as standard practice, you should include Option Explicit in the Declarations section of your code module. Then select Debug->Compile from the VB editor's main menu. The compiler will alert you to any problems it finds in your code ... such as syntax errors or undeclared variables. Attempting to trouble-shoot code without first performing those steps is basically a waste of time.

[Norie]

The syntax looks fine.

If you are getting an error it could be a problem with the SQL.

[alexfc]

Thanks for all the help.The problem was solved by changing a little the sintax :
<LI style="FONT-STYLE: normal; FONT-FAMILY: 'Courier New', Courier, monospace; COLOR: black; FONT-WEIGHT: normal" itxtNodeId="26" itxtHarvested="0">sql = "select * from COTACAO where cod_item ='" & cod & "'"
Set content = BD.OpenRecordset(sql)