SQL won't run

[Guest]

I have a SQL statement that is supposed to take the selection of a listbox
(lstTitle) and display all the records in tblSongs that match the criteria.
In the following code, I get a message that it cannot execute the strSQL
statement. I also tried the docmd.opentable "tblSongs" before trying to
execute the SQL statement.

I also tried every combination of single and double quotes around the
strTitle part of the SQL statement and connot get it to work. Can someone
point out what it wrong with the code? Thank you.

Private Sub cmdFindAlbum_Click()

Dim strSQL As String
Dim strTitle As String
strTitle = Me.lstTitle
strSQL = "Select * FROM tblSongs WHERE tblSongs.[Album] = " & Chr(34) &
strTitle & Chr(34) & Chr(59) & ""
Debug.Print strSQL
CurrentDb.Execute strSQL 'I get the error message here.

End Sub

 

[Marshall Barton]

I have a SQL statement that is supposed to take the selection of a listbox
(lstTitle) and display all the records in tblSongs that match the criteria.
In the following code, I get a message that it cannot execute the strSQL
statement. I also tried the docmd.opentable "tblSongs" before trying to
execute the SQL statement.

I also tried every combination of single and double quotes around the
strTitle part of the SQL statement and connot get it to work. Can someone
point out what it wrong with the code? Thank you.

Private Sub cmdFindAlbum_Click()

Dim strSQL As String
Dim strTitle As String
strTitle = Me.lstTitle
strSQL = "Select * FROM tblSongs WHERE tblSongs.[Album] = " & Chr(34) &
strTitle & Chr(34) & Chr(59) & ""
Debug.Print strSQL
CurrentDb.Execute strSQL 'I get the error message here.

End Sub

The trouble with this approach is that the Execute method
ony runs action queries. It does not open a query in sheet
view. AFAIK, there is no way to open a Select SQL statement
in sheet view.

The standard way to accomplish this kind of thing is to
create a form (or report) to display the table's data. Then
you can use the OpenForm method's WhereCondition argument to
display the filtered dataset. Once the form exists, your
code to open it would look like:

Private Sub cmdFindAlbum_Click()
Dim strWhere As String
Dim strTitle As String
strTitle = Me.lstTitle
strWhere = "Album = " & Chr(34) strTitle & Chr(34)
Debug.Print strSQL
DoCmd.OpenForm "NameOfForm", , ,strWhere
End Sub

 

[Guest]

You are probably getting an error 3065. You can't execute a Select query.
That is because you can't. Here is what you need:

instead of:

CurrentDb.Execute strSQL 'I get the error message here.

Try:

set rst = Currentdb.openrecordset(strSQL)

 

[Guest]

You can't execute a select query, you can execute Append, Edit, Create sql
but not select.
to do so, save it as a query and the run the command, docmd.openquery.

try this code:
' create a an empty query called = GlobalQuery
Dim DBS As Database, rst As Recordset, SqlStr As String
Dim strSQL As String, strTitle As String

Set DBS = CodeDb

strTitle = Me.lstTitle
strSQL = "Select * FROM tblSongs WHERE tblSongs.[Album] = " & Chr(34) &
strTitle & Chr(34) & Chr(59) & ""

DBS.QueryDefs("GlobalQuery").SQL = strSQL
docmd.openquery "GlobalQuery"
' assuming that the SQL is right

 

[Guest]

And one more....

If the list box and the button are on the search form, you could set the
record source and requery the form.

Private Sub cmdFindAlbum_Click()
Dim strSQL As String
Dim strTitle As String
strTitle = Me.lstTitle
strSQL = "Select * FROM tblSongs WHERE tblSongs.[Album] = " & Chr(34) &
strTitle & Chr(34) & Chr(59) & ""
Debug.Print strSQL
Me.RecordSource = strSQL
Me.Requery

End Sub