Query returns Null value

[oohmygod831]

i have a form for the user to book in tools, the list of tools are filtered through three cascading combo boxes.

My problem is this, i am using a query to calculate individual tool stock levels. now if i book tools in and run the query separately it works ok but i need the query to run automatically once the tool selection in the final combo box has been made.

I am using AfterUpdate and DLookup to access the data from the query but this way i get a Null value every time and ive no idea why.

this is a copy of the sql for the query:

SELECT tblToolBooking.Tool_ID, Sum(tblToolBooking.Book_Value) AS Out, tblTool.Tool_Name, tblTool.Factory_Stock, tblTool.Factory_Stock+[Out] AS StockTotal
FROM tblTool INNER JOIN (tblOperator INNER JOIN (tblMachine INNER JOIN tblToolBooking ON tblMachine.Machine_ID = tblToolBooking.Machine_ID) ON tblOperator.Operator_ID = tblToolBooking.Operator_ID) ON tblTool.Tool_ID = tblToolBooking.Tool_ID
GROUP BY tblToolBooking.Tool_ID, tblTool.Tool_Name, tblTool.Factory_Stock
HAVING (((tblToolBooking.Tool_ID)=[Forms]![frmBookInOut]![cboToolName]));


This is the code for the DLookup:

Private Sub cboToolName_AfterUpdate()
Dim bkValue As Integer

MsgBox "qryCheck returns --> " & DCount("*", "qryCheck")
bkValue = Nz(DLookup("StockTotal", "qryCheck", "Tool_ID = " & Forms![frmBookInOut]!Tool_ID), 0)


If bkValue < 1 Then
MsgBox "The tool is already booked out or hasn't been returned"
Cancel = True
Me.Undo
End If
End Sub

i used this code to check what the query was returning and it is always a Null value

MsgBox "qryCheck returns --> " & DCount("*", "qryCheck")

I have no idea where to start, any help would be gratefully recieved.

 

[MSAccessRookie]

Have you taken the query away from the form and determined that it brings back any data for the DLookup to Report?

Create a new SQL query that contains the following:

SELECT tblToolBooking.Tool_ID, Sum(tblToolBooking.Book_Value) AS Out, tblTool.Tool_Name, tblTool.Factory_Stock, tblTool.Factory_Stock+[Out] AS StockTotal
FROM tblTool INNER JOIN (tblOperator INNER JOIN (tblMachine INNER JOIN tblToolBooking ON tblMachine.Machine_ID = tblToolBooking.Machine_ID) ON tblOperator.Operator_ID = tblToolBooking.Operator_ID) ON tblTool.Tool_ID = tblToolBooking.Tool_ID
GROUP BY tblToolBooking.Tool_ID, tblTool.Tool_Name, tblTool.Factory_Stock;

Notice that the HAVING clause has been removed for the purpose of determining whether the query returns nothing, or whether what is returned does not compare favorably to the value imbedded in the form

If the query returns data that meets the criteria for the form comparison, you will need to check the form control value definitions to find out what is getting in the way, if not, you will know you need to fix it for it to work..

 

[oohmygod831]

tried the query without the HAVING clause and it still isnt working, '2' is returned every time now which happens to be the number of records in the Booking table and it still acts as if the stock level is less than 1.

 

[MSAccessRookie]

tried the query without the HAVING clause and it still isnt working, '2' is returned every time now which happens to be the number of records in the Booking table and it still acts as if the stock level is less than 1.

Try removing the SUM and the GROUP BY statements to check the raw data and see if the query is returning any data. If it is not, you will need to rethink the query

SELECT tblToolBooking.Tool_ID, tblToolBooking.Book_Value, tblTool.Tool_Name, tblTool.Factory_Stock, tblTool.Factory_Stock
FROM tblTool INNER JOIN (tblOperator INNER JOIN (tblMachine INNER JOIN tblToolBooking ON tblMachine.Machine_ID = tblToolBooking.Machine_ID) ON tblOperator.Operator_ID = tblToolBooking.Operator_ID) ON tblTool.Tool_ID = tblToolBooking.Tool_ID;

 

[oohmygod831]

still get the same result, was wondering if i should abandon the query and do the calculation in sql using DLookup's