Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Using SELECT Statements in Code

Discussion in 'Information Technology' started by Sharkbyte, Nov 11, 2005.

  1. Sharkbyte

    Sharkbyte
    Expand Collapse
    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.
     
  2. Loading...


  3. xRoachx

    xRoachx
    Expand Collapse
    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.

    "Sharkbyte" wrote:

    > 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.
     
  4. German Saer

    German Saer
    Expand Collapse
    Guest

    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


    "xRoachx" <xRoachx@discussions.microsoft.com> wrote in message
    news:7F1F486F-000C-4914-A65B-8E2499D6BC07@microsoft.com...
    > 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.
    >
    > "Sharkbyte" wrote:
    >
    > > 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.
     

Share This Page