SQL statement problem!

[AccessPractice]

Hi
I want to use select query to show the history of transactions of each TapeID.
I wrote the code in VBE as

Private Sub cmdHistory_Click()
    Dim SQL As String
    SQL = "SELECT tblTapeTransaction.Date,tblTapeTransaction.Time, tblTapeTransaction.AddOrSubtract " & _
            "FROM tblTapeTransaction WHERE (tblTapeTransaction.TapeID=[Forms]![frmTape]![TapeID]);"
    DoCmd.RunSQL SQL
End Sub

the code is error and VB error shows
"Run-Time error '2342':
A RunSQL action requires an argument consisting of an SQL statement."


But I already have had an SQL and I can't find the error of my code.
Anyone please advise.
Thank you

[ranman256]

you don't need to write any code. just make a query.
(you cant use form paths in code like this, but you can in a query)
The query builder will prevent errors.

[JoeM]

A few other things:
- Don't use reserved words like "SQL" as variables. This can cause ambiguity/confusion/errors. I often preface my variables with the prefix "my", i.e. "mySQL". Other people will use data type prefixes, i.e. "strSQL".
- Running a Select query in VBA doesn't really do anything. If you want to return the results to the screen, you will want to assign the SQL code to a query object, and open that query object. Typically, you would use the RunSQL command on an Action Query that updates records.

[ssanfu]

"Date" and "Time" are also reserved words and shouldn't be used as object names (your field names).
As stated, the RunSQL command (and Execute) only run Action Queries (Append, Update and Delete).


Also, you need to concatenate the value from [Forms]![frmTape]![TapeID] to the SQL string.
If "TapeID" is a number, thenit would look something like:

"FROM tblTapeTransaction WHERE (tblTapeTransaction.TapeID= " & [Forms]![frmTape]![TapeID] & ");"

[AccessPractice]

Thank you! I built a query named qryTapeHistory which has a criteria "=[Forms]![frmTape]![txtTapeID]" in the "TapeID" field
on QBE grid and a command button that used the following code:

Private Sub cmdHistory_Click()
    DoCmd.OpenQuery "qryTapeHistory", , acReadOnly
End Sub

It works but I want to show the query by a dialog form view. The query that is shown is in a full view.
How to?

[JoeM]

It works but I want to show the query by a dialog form view. The query that is shown is in a full view.

You are opening a Query, not a Form.

If you want to mess around with different Form views, use the Query as the Data Source for a Form, and then open that Form.

[AccessPractice]

Hi JoeM,
Yes. I have tried to build a form named "frmTapeHistory" that has "qryTapeHistory" as a data source.
And I wrote

Private Sub cmdOpenFrmTapeHistory_Click()
    DoCmd.OpenForm "frmTapeHistory", , , , acFormReadOnly, acDialog
End Sub

to open the form. (the command button's on click event is in the main form "frmTape")
But the form intentionally defaulted in Datasheet view is blank.
I think the problem is the query "qryTapeHistory" which has the criteria
"=[Forms]![frmTape]![txtTapeID]" in the "TapeID" field on QBE grid.
I think that the criteria is wrong.
The query does not link with the TapeID that I want to see.
And I don't know how to write to link it.
Please excuse me. I am a beginner.

[ssanfu]

Work through these tutorials: http://www.rogersaccesslibrary.com/forum/forum46.html

They will help.....