[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 797
  • Last Modified:

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
0
mrBrightside
Asked:
mrBrightside
  • 9
  • 4
  • 2
  • +1
1 Solution
 
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
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 9
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now