SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I get a "Compile error Expected case" once I try and more off this SQL statement. It works in a query in SQL view, but not in my code.

Private Sub Command33_Click(

SELECT [tbItemDetail].[ItmNum], [tbItemDetail].[Desc], [tbItemDetail].[Price], [tbItemDetail].[Packing], [tbItemDetail].[UOM] FROM tbItemDetail WHERE ((([tbItemDetail].[ItmNum]) Like [cbItem]))

End Sub
 
2 key points
1. You cannot run SQL in VB just by typing in the SQL
statement. You have to use DoCmd.RunSQL
2. DoCmd.RunSQL achieves nothing when the SQL is a SELECT
query as you cannot specify anywhere to place the results
of the query. It is intended for INSERT, UPDATE and DELETE.

Hope That Helps
Gerald Stanley MCSD
-----Original Message-----
I get a "Compile error Expected case" once I try and more
Click to expand...
off this SQL statement. It works in a query in SQL view,
but not in my code.
Private Sub Command33_Click()

SELECT [tbItemDetail].[ItmNum], [tbItemDetail].[Desc],
Click to expand...
[tbItemDetail].[Price], [tbItemDetail].[Packing],
[tbItemDetail].[UOM] FROM tbItemDetail WHERE
((([tbItemDetail].[ItmNum]) Like [cbItem]));
 
I get a "Compile error Expected case" once I try and more off this
SQL statement. It works in a query in SQL view, but not in my
code.

Private Sub Command33_Click()

SELECT [tbItemDetail].[ItmNum], [tbItemDetail].[Desc],
[tbItemDetail].[Price], [tbItemDetail].[Packing],
[tbItemDetail].[UOM] FROM tbItemDetail WHERE
((([tbItemDetail].[ItmNum]) Like [cbItem]));

End Sub
Click to expand...

What do you expect to happen here?
You wrote a Select SQL statement in a command button event.
Period.
Did you expect this do something?
It won't. You didn't tell it to do anything.

It is the equivalent of just writing the word
Swim
in the code. You'll get a compile error.

1) If you expected this to run a query, you would have to tell it to
do so, and the SQL must be a string (within quotes).
DoCmd.RunSQL "Select ... etc."

2) However, you cannot use RunSQL to run a Select Query (it's only
for Action queries) as there is no vehicle in which to display the
selected records.

3) It works in a query because the query is a vehicle which can
display the records returned.

If you need to see these records in a query, use the above SQL in a
query. Save the query. Then from the command button click event:
DoCmd.OpenQuery "QueryName"

4) If the above SQL was meant as a Report or Form Record Source then
you have to tell it that (Note the quotes around the SQL statement):

DoCmd.OpenReport "ReportName", acViewDesign
Reports!ReportName.RecordSource = "Select ... etc."
DoCmd.Close acReport, "ReportName", acSaveYes
DoCmd.Open Report "ReportName", acViewPreview

Hope this has helped.
 
I get a "Compile error Expected case" once I try and more off this SQL statement. It works in a query in SQL view, but not in my code.

Private Sub Command33_Click()

SELECT [tbItemDetail].[ItmNum], [tbItemDetail].[Desc], [tbItemDetail].[Price], [tbItemDetail].[Packing], [tbItemDetail].[UOM] FROM tbItemDetail WHERE ((([tbItemDetail].[ItmNum]) Like [cbItem]));

End Sub
Click to expand...

VBA and SQL are two different languages! You can't just put SQL text
into a Click event.

What do you want to happen when you click Command33 (and why haven't
you renamed the button to something meaningful)?
 
Back
Top