Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    Canberra, ACT Australia
    Posts
    53

    Unanswered: docmd.runsql "select

    I am attempting to select a field from a table in order to put it onto a form to use in a later calculation.

    the code I am using is as follows, where:
    table name = RollerAmbienceSystem
    table field name = price
    OptionPrice has been dimd as a variant
    rollerAmbiencesystem.optionNumber has a value of 1,2 or 3 in the table
    me!ambiencerolleroptiongroup.value comes from the value of the option group on the form
    and
    me!blindoptionstotal is the field on the form where the price is to show up.

    so that, for example, if option 2 is chosen then the price of $450.00 will show.


    DoCmd.RunSQL "SELECT RollerAmbienceSystem.price as optionPrice " & _
    "FROM RollerAmbienceSystem " & _
    "WHERE RollerAmbienceSystem.optionNumber = me!ambiencerolleroptiongroup.value;"

    Me!Blindoptionstotal = optionprice


    MY PROBLEM:

    I get a run time error 2342:
    A RunSQL action requires an argument consisting of an SQL statement.

    I don't think I am trying anything to tricky. I just can't see where the error is.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it wont fly like that i'm afraid.

    the simple part of your problem is an SQL error:
    "SELECT RollerAmbienceSystem.price as optionPrice " & _
    "FROM RollerAmbienceSystem " & _
    "WHERE RollerAmbienceSystem.optionNumber = " & me!ambiencerolleroptiongroup.value & ";"
    will concatenate in the value of the option group (instead of the literal text "me!ambiencerolleroptiongroup.value" as in your version.

    but that's only part of the problem. SELECT is one thing, but where do you want A to stuff the selected records?? you can use .runsql for action queries ("DELETE * FROM tblXxxx") cos they don't return any records, but SELECT needs somewhere to put the return.

    you could use a form/list/combo... they hold multiple records.
    form.recordsource = yourSQL
    list/combo.rowsource = yourSQL

    you could use a recordset.
    in DAO-speak:
    dim dabs as dao.database
    dim recs as dao.recordset
    set dabs = currentdb
    set recs = dabs.openrecordset(yourSQL)...and then play with the recordset

    alternative is to use a domain aggregate function: by definition these return a single value, so you can stuff it into a variable.

    aValue = DLOOKUP("price", "RollerAmbienceSystem", "RollerAmbienceSystem.optionNumber = " & me!ambiencerolleroptiongroup.value)

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jul 2003
    Location
    Canberra, ACT Australia
    Posts
    53
    many thanks. Used the latter option successfully - didn't think to use that (obviously).

    Also, with the initial SQL I thought (apart from the syntax error) that the A select would place the .price value into the optionprice variant that I'd dimd. So it would 'stuff' $450 eg into optionprice.

    Cheers

Posting Permissions

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