Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: How to assign single value query to a variable in VBA

    Hello,

    I have the following query that return the sum of prices.

    "SELECT Sum(PartsList.CustomerPrice)AS Expr1 FROM PartsList INNER JOIN Items_Quote ON PartsList.ItemNumber = Items_Quote.ItemNumber WHERE (((Items_Quote.QuoteNumber)=" & QuoteRef & ")))"

    I was not able to use the record set to read the value Expr1 and to assign it to a variable in the code.

    Any help will be highly appreciated.

  2. #2
    Join Date
    Jan 2012
    Posts
    1
    Are you sure the recordset returned a record? It looks like you have an extra ")" in the query. What does the rest of your code look like?

  3. #3
    Join Date
    Jan 2012
    Posts
    2

    It works now

    Thanks for your reply. It works now after updating the SQL statement. I found more syntax errors in it. But before the modification I was getting an error that that the object is not supported in the red code below:

    Public Sub QuoteValue()
    Dim dbs As DAO.Database
    Dim SumSQLtext As String
    Dim QuoteRef As String
    Dim rst As DAO.Recordset
    Dim QuoteValue

    QuoteRef = Forms("enter items").Controls("text75")
    SumSQLtext = "(SELECT Sum(PartsList.CustomerPrice)AS Expr1 FROM PartsList INNER JOIN Items_Quote ON PartsList.ItemNumber = Items_Quote.ItemNumber WHERE ((Items_Quote.QuoteNumber)= '" & QuoteRef & "'))"

    Set dbs = CurrentDb

    'Get the sum of quotation items

    Set rst = dbs.OpenRecordset(SumSQLtext)

    Do While Not rst.EOF
    QuoteValue = (rst![Expr1])

    rst.MoveNext
    Loop

    rst.Close
    Forms("enter items").Controls("text77") = QuoteValue
    dbs.Close


    Forms![Enter Items].[Query1 subform1].Form.Requery
    End Sub

Posting Permissions

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