Using SELECT Statements in Code

[Guest]

Is it possible to assign values to variables, in code (such as the On Open
event) using a standard SELECT statement?

I'm aware that RunSQL only works for action queries, and so am at an impass.

The scenario is that I have a table, which either contains valid records, or
does not. I need to query the table, for valid records, and return a null or
a value. I'm thinking I may be able to use a record count, in place of the
Select statement, but at this point my brain is toast, and I can't seem to
come up with anything to use.

Any help would be appreciated.

 

[Guest]

Hey Shark, if we couldn't use SQL in code, we would all be in trouble!
Anyhow, you can use recordsets to accomplish a variety of tasks including
what you have hinted at.

As a suggestion, the more specific you are in your question, the better the
responses will be. In the mean time, search for recordset in the Access help
file or the message board for more info. Also, post some specifics about
what you want to accomplish and some one will more than likely help you out.

 

[German Saer]

As guidance:

strSQL="SELECT * FROM Anytable"
Set rs = currentdb.openrecordset(strSQL)
IF Not rs.EOF Then
....
Else
Msgbox "There is no records..."
End If
rs.Close
Set rs = Nothing

Before you close the recorset variable (rs) you can use to move from records
found

rs.MoveFirst
rs.MoveLast
rs.MoveNext
rs.MovePrevious

To know if End Of Recordset has been reached:

rs.EOF

To update:

rs.Edit
rs.Field.Value= AnyValue
....
rs.Update

To Add a New record:

rs.AddNew
....
rs.Update

To Delete:

rs.Delete

There is good set of tools to manipulate data by using recordsets generated
by a SQL statement.


Hope it helped

--
___________
Thanks,

German Saer
Orlando, FL