Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178

    Angry Unanswered: select statement

    i'm trying to create a select statement in VBA. I don't want to create it as a query. I know in Java you create recordsets and stuff to handle the information from the select statement. How would I do this in VBA?
    the resulting values need to be summed / averaged. yes the values are numeric.

    pls help
    ActionAnt

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    dim rs as DAO.Recordset
    set rs = currentdb.openrecordset("SQL HERE")

    those 2 will create and open the recordset for you in VBA. You can the do stuff like
    Do while not rs.EOF

    rs.movenext
    loop

    and such...
    Tip:
    Create the query you want in the query builder and copy past the SQL in there (unless offcourse your an experienced SQL-er)

    Regards

  3. #3
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    Originally posted by namliam
    dim rs as DAO.Recordset
    set rs = currentdb.openrecordset("SQL HERE")

    those 2 will create and open the recordset for you in VBA. You can the do stuff like
    Do while not rs.EOF

    rs.movenext
    loop

    and such...
    Tip:
    Create the query you want in the query builder and copy past the SQL in there (unless offcourse your an experienced SQL-er)

    Regards
    thanks but that didn't really work
    tell you what i want to do...

    my select statement selects numeric values and sums them at the same time. All I want to do is take that one value and use it in an IF statement and/ or display it on a form.
    Can I do this without using a recordset?
    if not, how do i retrieve the value?

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    You dont. You have to use a recordset

    First doing the 2 lines dim, set rs
    rs!YourFieldName will return the value
    rs.Fields("YourFieldName") will do the same

    you can then use that in any if or whatever.

    If rs.fields("YourFieldName") = 1 then

    else

    endif

    Remember to include
    rs.close
    and
    set rs = nothing
    after you no longer need the recordset.

    Hope that IS what you want.

    Regards

  5. #5
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    Originally posted by namliam
    You dont. You have to use a recordset

    First doing the 2 lines dim, set rs
    rs!YourFieldName will return the value
    rs.Fields("YourFieldName") will do the same

    you can then use that in any if or whatever.

    If rs.fields("YourFieldName") = 1 then

    else

    endif

    Remember to include
    rs.close
    and
    set rs = nothing
    after you no longer need the recordset.

    Hope that IS what you want.

    Regards
    thanks that will help a lot
    Access doesn't recognise ADO.recordset
    if I use Dim rs As recordset
    then it works
    but I then get a Type Mismatch error on the following:
    Set rs = CurrentDb.OpenRecordset("Select * From Input")

    any suggestions

  6. #6
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Its DAO not ADO, if it doesnt recognize that (DAO) but does recognize ADO then try (from the menu when in VBA)
    Tools => references
    Add the Microsoft DAO 3.6 library (or the proper version you have on your machine)

    Regards

  7. #7
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    Originally posted by namliam
    Its DAO not ADO, if it doesnt recognize that (DAO) but does recognize ADO then try (from the menu when in VBA)
    Tools => references
    Add the Microsoft DAO 3.6 library (or the proper version you have on your machine)

    Regards
    thank you so much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •