SQL in VBA SELECT Problems

[Oscar_W]

Hi,
I am sure this is a simple error but I just can't get it.

I want to open a form with information based on today's date. I then want to have the ability to click buttons to advance or retard the date and then show the appropriate data for the new selected date.

I thought the best way would be to use a SQL Select in the form's On Open event VBA code and then increase or decrease the date in the SQL by clicking the buttons but I just can't get even the basic SQL to work.
The Datebase I am working on is (reasonably) complicated so I have created a very simple Datebase with just 1 table and one form to test the SQL. It still doesn't work ! Where am I going wrong please...

Option Compare Database

Private Sub Command20_Click()
Dim testsql As String
testsql = "SELECT * FROM Products WHERE ProductID = 14;"
DoCmd.RunSQL testsql
End Sub

Each time I click on the test button I get the error - "A RunSQL action requires an argument consisting of an SQL statement"
The Table and Form are both called "Products"

Thanks
Oscar

 

[modest]

RunSQL are used for action queries (Update/Delete/Append/Make-Table)

You cannot run SELECT queries using RunSQL
------------------------------------------------

The best way to achieve what you want is to just filter the form. Do a search on this.

 

[Oscar_W]

Modest,
Thanks very much for the prompt reply. You obviously know your stuff but it is at odds with this.....http://www.fontstuff.com/access/acctut15.htm. I will use filters as you suggest but can you explain what "fontstuff" are on about. Are they using a different version / code ?

I am using Access 2003

Thanks.

 

[modest]

The link you have is a good introduction to structured query language (SQL), which is the language used to edit, retrieve, update, and remove information to a table in a database.

If you look closely it is not at odds with what I was saying. When they were using the RunSQL function, they did not use the "SELECT" query. They just said you could put a query there. When they did use a SELECT query, notice (at the bottom in the error section) that they used the Docmd.OpenQuery function instead.

But what I was saying is that you don't need to use a query, you just need to filter, which is similar to the "WHERE" statement of a query.

 

[Oscar_W]

Thanks for your help. I have used Filter successfully on the test Db. Now to try and incorporate that into the more complicated one

 

[sergiomendes]

Hi,
I need help please.
I have a form that has a field called nmecbi and need to refer to the table and get the name associated with the BI. how to use the select command to do this?
the moment this way:
Private Sub nmecbi_Enter ()
Dim strSQL As String
The Dim rst DAO.Recordset
strSQL = SELECT [Name] FROM process WHERE [bi] = 'nmecbi '
MsgBox strSQL
'If Then rst.NoMatch
'MsgBox "This record does not exist"
'End If
End Sub

thanks for help