retrieveing a value from a select query using vba

SELECT tblserviceowner.ServiceQTY
FROM tblserviceowner
WHERE (((tblserviceowner.ServiceID)=[forms]![frmorderdata]![subfrmserviceorder].[form]![serviceid]) AND ((tblserviceowner.CustomerID)=[forms]![frmorderdata].[customerid]) AND ((tblserviceowner.ServiceAgreedPrice)=[forms]![frmorderdata]![subfrmserviceorder].[form]![ServiceOrderPQAgreedPrice]));

I have created the above query to find the current quantity based on a certain set of criteria. What i would like to do is then use this data within a form to carry out further opperation. I have a command button that calls the query docmd.openquery strdocname and obviously it shows the result in a datasheet, is there anyway to not show the data sheet and just use the value that is returned?

i want the code do something like the following:

dim strdocname as string
dim lngQTY as long

strdocname = "queryname"
docmd.openform strdocname 'or whatever the code would be to retrieve the value
lngQTY = 'whatever the code is to set the variable to the value retrieved by the query

if lngqty = 1 then
call delete query to remove the record based on criteria (i do know how to do this)
else
call update query to deduct 1 from the total quantity retrieved by the query (this i do know how to do
end if

any help would be very much appreciated.

thanks
mrBrightsideAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NatchiketCommented:
Hi MrBrightSide,
Consider doing something like this ...

(I'm assuming you have the above query stored as a named query)

lngQty = Nz(Dlookup("ServiceQTY","Name of the above query"),0)


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Arthur_WoodCommented:
here is a coded version that may help you:

Dim strSQL as String
Dim cn as ADODB.Connection
Dim lngQTY as Long
Dim rs as ADODB.Recordset
 
strSQL = "SELECT ServiceQTY FROM tblserviceowner WHERE ServiceID =" & [forms]![frmorderdata]![subfrmserviceorder].[form]![serviceid] & _
 " AND CustomerID)= " & [forms]![frmorderdata].[customerid]
 & _
" AND ServiceAgreedPrice = " & [forms]![frmorderdata]![subfrmserviceorder].[form]![ServiceOrderPQAgreedPrice]
 
Set cn = CurrentProject.Connection
Set rs = cn.Execute strSQL
lngQTY = rs.Fields("ServiceQTY")

Open in new window

0
JezWaltersCommented:
Have you thought of using DLookup() instead?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

JezWaltersCommented:
Sorry Natchiket - anyone for a slice of deja vu?!  :-)
0
JezWaltersCommented:
... although I was thinking along these lines (assuming ServiceQTY is a mandatory field):
Dim lbgQty As Long
 
With Forms!frmorderdata
    lbgQty = DLookup("ServiceQTY", _
                     "tblserviceowner", _
                     "ServiceID = " & .subfrmserviceorder.Form.serviceid & " AND " & _
                     "CustomerID " & .customerid & " AND " & _
                     "ServiceAgreedPrice = " & .subfrmserviceorder.Form.ServiceOrderPQAgreedPrice)
End With

Open in new window

0
JezWaltersCommented:
'lbgQty' should of course have been 'lngQty' ... if I could type properly!  :-)
0
mrBrightsideAuthor Commented:
simple and brilliant, works like a charm, thanks my friend
0
JezWaltersCommented:
Just a small point, but you don't need the Nz() call if ServiceQTY is mandatory.
0
mrBrightsideAuthor Commented:
how do you mean mandatory? as in a "required" value?
0
JezWaltersCommented:
The ServiceQTY field is mandatory if its Required flag is set to 'Yes' in the Design of your tblserviceowner table.
0
mrBrightsideAuthor Commented:
i see thanks, what does nz actually refer to? i have seen it in calculations before
0
JezWaltersCommented:
I think Nz stands for Null-Zero - or something like that!

If the first parameter of Nz() is Null, it returns the second parameter.  If the second parameter isn't specified (it's actually optional) either 0 or "" is returned, depending on the context.

The official definition can be found here:

http://msdn2.microsoft.com/en-us/library/aa172237.aspx
0
Arthur_WoodCommented:
the NZ function, as in NZ(Field,<Value>), tests the Field supplied, and returns the value of the Field if it is NOT NULL, or returns the specified <Value> if the Field IS NULL.

AW
0
JezWaltersCommented:
On this basis, there's no point calling Nz if the first parameter can never be Null - which is the case for a mandatory field.

See what I mean?
0
NatchiketCommented:
If you look carefully you'll see that the Nz applies to the Dlookup() not to the ServiceQTY.  This is to avoid a null value being assigned to lngQty, since we cannot know a priori what value the query will return given the embedded criteria
0
JezWaltersCommented:
Natchiket,

Fair point - Null will indeed be returned if there's no record - I assumed (but forgot to say) the record HAS to be present too.

Perhaps it's better to err on the side of caution ...
0
mrBrightsideAuthor Commented:
thanks guys, it has to return a record as it is part of a decrease amount of service event, which cannot be called unless there is a record to begin with, but thanks for all your help. i will do some reading up about this nz, seems pretty crtiical
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.