Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: use record from table in vb-sub (howto)

    Hello all,

    I have no real experience with access and witj VB just some trial-and-error experience. I have been looking on the internet to a solution to my problem
    described below.

    Any help or hint are highly appreciated!!

    I set up a simple table with columns C1 and C2

    now when I create a form, its no problem getting the table in a listbox.
    It's also no problem to create/delete/update records using DoCmd.RunSQL

    Now here is my (hopefully simple) problem. I added a button on the form
    and on the clickevent I want to execute some VB. Inside the VB-sub

    I want to store a specific record in a variable... and I cant find out how

    So what I want is sommy like:

    field1 = DoCmd.RunSQL "SELECT C2 FROM table WHERE C1='b'"
    field1 = field1 & "another string"
    DoCmd.RUNSQL "UPDATE table SET C2=" & field1 & " WHERE C1='b'"


    it would also be okay if it would put the matching records in some
    list or so.

    thanks for your help,

    Bruno

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    SELECT returns an arbitrary number of records: from 0 to millions. there is no way for access to plan to store an arbitrary chunk of data in a variable!

    domain aggregate functions return a single value or you could select into a recordset and read the value from the recordset.

    check out domain aggregate DLOOKUP() in help/this site/google: it goes like this for your first case:
    field1 = DLOOKUP("C2", "table", "C1='b'")

    also check out the other domain aggregates like DMAX(), DMIN() etc

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    dim rs as dao.recordset
    set rs= currentdb.openrecordset("SELECT C2 FROM [table] WHERE C1='b'")
    do while not rs.eof
    rs.edit
    rs!c2 = rs!c2 & "another string"
    ' or: myvariable = rs!c2
    rs.update
    rs.movenext
    loop
    rs.close
    set rs=nothing

    - typed, not tested
    or you could probably execute it with docmd.runsql or

    currentdb.execute "update [table] set c2 = c2 & " & "another string" & " WHERE C1='b'"
    Roy-Vidar

Posting Permissions

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