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.

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?

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