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.

Hi Rod,

How 'bout:

Private Sub grpValues_Click()

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strSQL As String

    strSQL = "SELECT Price As OptionPrice " & _
             "FROM RollerAmbienceSystem " & _
             "WHERE OptionNumber = " & Me.grpValues

    Set cnn = CurrentProject.Connection

    Set rst = New ADODB.Recordset
    rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

    Me.txtBlindOptionsTotal = rst!OptionPrice

End Sub

Bob's idea looks good. Will there be more than one record that meets that criteria?

Just for the record Rod, Run SQL command is only for Action Queries, eg; DELETE, DROP, CREATE, APPEND, UPDATE etc...

And finally, even easier would be...

Me.txtBlindOptionsTotal = DLookUp("OptionPrice","RollerAmbienceSystem","opti onNumber = " & me!ambiencerolleroptiongroup.value)

This will change with the option group. You can put this directly in the controlsource property of txtBlindOptionsTotal, or through the command button as before.

Good Luck!