Retrieving a Value from a Query

[glnbnz]

Hello,
I am trying to retrieve a value from a query. I have a form and when I
press a command button I would like the code to open a query, take the value
and place it into a variable then close the query.

Here is my code so far:

Dim sngValue As Single

DoCmd.OpenQuery "qryMyQuery", acViewNormal

sngValue = [Queries]![qryMyQuery]![SomeValue]

DoCmd.Close acQuery, "qryMyQuery", asSaveNo

Me.Text18.Value = sngValue

My problem lies in the 3rd line of the code and I can't seem to find the
correct syntax for obtaining "SomeValue" and storing it as "sngValue".
I know that I can do this a different way, but there is a method to my
madness where I am going to do a calculation from multiple queries.

Could someone help me with this?

Thank you.

 

[glnbnz]

Thanks Doug answered my question perfectly

Glenn

Replace what you have with


Me.Text18.Value = DLookup("[SomeValue]", "qryMyQuery")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,
I am trying to retrieve a value from a query. I have a form and when I
press a command button I would like the code to open a query, take the
value
and place it into a variable then close the query.

Here is my code so far:

Dim sngValue As Single

DoCmd.OpenQuery "qryMyQuery", acViewNormal

sngValue = [Queries]![qryMyQuery]![SomeValue]

DoCmd.Close acQuery, "qryMyQuery", asSaveNo

Me.Text18.Value = sngValue

My problem lies in the 3rd line of the code and I can't seem to find the
correct syntax for obtaining "SomeValue" and storing it as "sngValue".
I know that I can do this a different way, but there is a method to my
madness where I am going to do a calculation from multiple queries.

Could someone help me with this?

Thank you.

 

[DStegon via AccessMonster.com]

First off you want to open a recordset with the qry as the source. You can
then retrieve any value within the recordset you wish and set that value to
your variable.

I use ADO so just your code accordingly

Dim rst as ADODB.Recordset

With rst
.open "SELECT ..... ",currentproject.connection (put your query text where
the select is
sngValue =!SomeValue
.close
end with

Me.Text18.Value = sngValue

to get the query string forom a stored query:

Dim rst As New ADODB.Recordset
Dim sqlStr As String

sqlStr = CurrentDb.QueryDefs("qryMyQuery").SQL

rst.Open sqlStr, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

'get your values for what fields you are querying here

rst.Close

Hello,
I am trying to retrieve a value from a query. I have a form and when I
press a command button I would like the code to open a query, take the value
and place it into a variable then close the query.

Here is my code so far:

Dim sngValue As Single

DoCmd.OpenQuery "qryMyQuery", acViewNormal

sngValue = [Queries]![qryMyQuery]![SomeValue]

DoCmd.Close acQuery, "qryMyQuery", asSaveNo

Me.Text18.Value = sngValue

My problem lies in the 3rd line of the code and I can't seem to find the
correct syntax for obtaining "SomeValue" and storing it as "sngValue".
I know that I can do this a different way, but there is a method to my
madness where I am going to do a calculation from multiple queries.

Could someone help me with this?

Thank you.