DLookup based on the results of a combo box

[oohmygod831]

i have a database that allows the user to book tools in or out of the stores. To keep a check on the bookings and to ensure that a tool isnt booked out when it isnt available i need to check the stock level of the tool being booked.

To do this i have a query that calculates the current stock level but i need to access this figure. i was directed towards using a DLookup.

The form i am using has cascading combo boxes which filter the tool selection down to the last combo box and the query results are based on the selection made in the last combo box.

However i am getting a runtime error 2001 and when i debug it points towards the DLookup. Any clues as to what is wrong with it?

The following code is in the AfterUpdate of the last combobox (cboToolName)

Dim bkValue As Integer

bkValue = DLookup("StockTotal", "qryCheck", "ToolID = " & Forms![frmBookInOut]!cboToolName)

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

 

[pbaldy]

What is the data type of ToolID in the table? Is the bound column of the combo the ToolID? The DLookup will return a Null if the value isn't found, and the Integer data type can't accept a Null. Is that a possibility?

 

[oohmygod831]

The data type of ToolID is number and yes it is the bound column of the combo. Didnt think the combo could return a null if there wasnt one in the table.

 

[pbaldy]

I'm not saying the combo would be Null, I'm saying the DLookup will return Null if it can't find the specified ToolID in the query. Is that a possibility? What exactly is error 2001? Can you post a sample db?

 

[oohmygod831]

sorry about that , not sure if the Dlookup would return a null as its the first time ive tried one and not sure if syntax is correct
tried to shrink the dbase but the smallest i can get it is 1.2mb and thats too big to post, if you PM me i can email it to you if you wish