SQL Statement

[Playa]

Is it possible to use SQL Statements in your code, for example...

SELECT *
FROM table
Where criteria;

And if so how do i go about this?

 

[Dan Artuso]

Hi,
Yes you can. How you go about it depends on what you want to
do with the results.
Typically you would open a recordset using your SQL statement.

Dim rs As DAO.Recordset
Dim strSql as String

strSql = "Select * from someTable Where ......"

Set rs = CurrentDb.OpenRecordset(strSql)

 

[John Vinson]

Is it possible to use SQL Statements in your code, for example...

SELECT *
FROM table
Where criteria;

And if so how do i go about this?

You can't directly: SQL is one language, VBA is a different one. SQL
statements are not recognized by the VBA compiler.

What you can do is store SQL strings in a string variable, and then
use various DAO or ADO methods to execute the queries or open
recordsets based upon them. See Dan's reply for more details.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Playa]

How would I compare those results with a text box. I want I want to do is
see if the DrawingNum inputted is in the table. Here is what I have...

Dim rs As DAO.Recordset
Dim SQL As String

SQL = "SELECT DrawingNum & FROM tbl_DrawingsAndData"

Set rs = CurrentDb.OpenRecordset(SQL)

If Me.DrawingNum.Text = rs Then
MsgBox "The Drawing Number Already Exisits"
End If

but this does not work.

 

[Dan Artuso]

Hi,
In that case, you could simply use the DLookup() function.
Please read up on recordsets in Help if you want more info on them.

If Not IsNull(DLookup("[DrawingNum"],"tbl_DrawingsAndData", & _
"DrawingNum = " & Me.DrawingNum)) Then
MsgBox "The Drawing Number Already Exisits"
End If