Compile Error: Case Expected

Question

I am new to Access 2010. I'm trying to do something very simple: Attach an event to a field on a form. For test purposes only, I'm just entering this code:

DoCmd.RunSQL

Select * from Employees

End Sub

No matter what I do (put ; after the lines, or whatever), I keep getting this same error:  

Compile Error:

Expected Case.

I have followed the exact syntax for the Select statement in the Access manual, and even cut and pasted from examples on the Internet, but it keeps giving me this error, highlighting the *.

I've even tried selecting specific field names from the file, following the syntax example, and I still get the same error. What am I doing wrong? Or, what is the problem?

Answer 1

If you want to see how to create and open a query, the following is a simple example:

Private Sub cmdMyButton_Click()

    CreateAndOpenQeuryDef

End Sub

Sub CreateAndOpenQueryDef()

    Dim strSQL As String

    Dim strQueryDef As String

    strSQL = "SELECT * FROM Employees"

    strQueryDef = "sel_Employees"

    'Create a new QueryDef object which can be seen and managed in the Navigation Pane.

    CurrentDb.CreateQueryDef strQueryDef, strSQL

    'Open the new QueryDef

    DoCmd.OpenQuery strQueryDef, acViewNormal

End Sub

Answer 2

sub firstRun()

Msgbox "hello word！"

End Sub

try this

Answer 3

Another point here is that you don't have fields on a form. You have controls that may or may not be bound to a field in a table. This is a subtle but important distinction. 

As noted the correct syntax is

DoCmd.RunSQL "SQL statement as a string"

But even better is 

CurrentDB.Execute "SQL stmt"

Again, only for Action queries.

Your problem is not attaching the event to a control, but in using incorrect syntax for the event.

Answer 4

SQL is one language; VBA is a different one. You can't mix them in a procedure; it's as if I plötzlich auf Deutsch antwortet. 

You can create a string variable or a quote delimited string constant and use it as a query, but as Sandro says, you can only Execute an action query (Make Table, Delete, Append etc.) You can create a Querydef object and open it as a datasheet if you wish but that would not be something I'd ever do in a production database - users shouldn't have to deal with query datasheets, since Forms are much more useful.

Could you explain what real-life task you're trying to accomplish?

Answer 5

ciao Jerry,

runSql is the method for action queries only.

for select statement you should open a recordset.

besides, sql statement needs to be wrapped by quote "select...."

Explain better what you need to do...

ciao, Sandro.