Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 30th, 2004, 02:32 AM
Registered User
Join Date: Jul 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
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


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.

Old July 30th, 2004, 03:59 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

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

Old July 31st, 2004, 02:55 PM
Authorized User
Join Date: Oct 2003
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts

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!

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using DoCmd.RunSQL to return values jscully Access VBA 10 August 21st, 2017 12:58 PM
Docmd.runsql anne.burrows VB How-To 2 October 25th, 2006 08:21 AM
DoCmd.RunSQL dates and time TarkaDahl Access VBA 3 May 11th, 2006 11:19 AM
Removing the MsgBox in DoCmd.RunSQL Update arfa Access 2 March 24th, 2006 08:05 PM
Help with DoCmd.RunSQL command ricmar Access VBA 3 July 21st, 2004 03:32 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.