how to: check to see if query returns value

[Bobnz]

have a query that runs and need to check to see if it returns a value

strSQL = "select ..........."

if not isnull (strSQL) then
...........
else
..........
end if

will this work

 

[GingerR]

where are you doing this? In a module? or on a form or report?

 

[bboffin]

A SELECT query will ALWAYS return a recordset but that recordset may have no rows in it.

If it has no rows then the BOF and EOF flags of the recordset will both be true.

Your code does not attempt to open a recordset at the moment. Try adding:

Dim lRS as DAO.RecordSet
Set lRS = CurrentDb.OpenRecordset(strSQL)
If lRS.BOF and lRS.EOF then
   ' there is no data returned by the query
.
.
.
Else
    ' data has been returned
.
.
.
End If

Bob Boffin

 

[GingerR]

or

if lrs.recordcount = 0 then

 

[Bobnz]

Thanks for the replies,

I was using 'docmd.runsql strSQL' so the query didn't work as runsql is only for action queries. So I tried

strSQl as string

strSQL = DLookup("[UnitsInStock]", "[tblproducts]", "[ProductID] = " & product_id & "")
onhand = strSQL

but for some reason no value is being returned and the value "1" should be

This is happening inside a function

function product_reorder(product_id as integer)

B

 

[GingerR]

is productID a number or text?
your Dlookup will return, I assume, a number (UnitsInStock). why are you dim-ing "strSQL" as a STRING?

I would do what Bob Boffin says, but if you want to pursue the dlookup thing, how about this:

dim intUnitsInStock as Integer
intUnitsInStock = DLookup("[UnitsInStock]", "[tblproducts]", "[ProductID] = " & product_id)

If IsNull(intUnitsInStock) Then
     'There is no match
     .
     .
     .
Else
     'There is a match
     .
     .
     .

End If

if Product_ID is a number, you do not need the final "".

 

[Bobnz]

Thanks for the reply,

I'm trying to create a function that handles reordering products once their stock levels fall below a reorder level. I tried using a select query but docmd.runsql only handles action queries so I have tried dlookup but no values are being assigned to the variables and I know that there should be.

this function is called by

product_reorder prodid

prodid being a global var that is set just before calling function

[b]Function product_reorder(product_id As Integer)[/b]    

    Dim onhand As Variant
    Dim reorderlevel As Variant    
    Dim onorder As Variant 
    strSQL as string

        
    onhand = DLookup("[UnitsInStock]", "[tblproducts]", "[ProductID] = " & product_id)  

    reorderlevel = DLookup("[ReorderLevel]", "[tblproducts]", "[ProductID] = " & product_id)    

    rquantity = DLookup("[OrderAmount]", "[tblproducts]", "[ProductID] = " & product_id)    

    onorder = DLookup("[UnitsOnOrder]", "[tblproducts]", "[ProductID] = " & product_id) 
    
    If (onhand > reorderlevel) Then
        MsgBox "1"
        End
    Else
        'UPDATE tblproducts
        MsgBox "2"
        strSQL = "update tblproducts set UnitsOnOrder = " & rquantity & " where ProductID = " & prodid & " "
        DoCmd.RunSQL strSQL
    End If   
End Function

B

 

[GingerR]

Function product_reorder(product_id As Integer)

    Dim onhand As Variant
    Dim ReOrderLevel As Variant
    Dim onorder As Variant
    [COLOR=red]Dim[/color] strSQL As String

        
    onhand = DLookup("[UnitsInStock]", "[tblproducts]", "[ProductID] = " & product_id)

    ReOrderLevel = DLookup("[ReorderLevel]", "[tblproducts]", "[ProductID] = " & product_id)

    rquantity = DLookup("[OrderAmount]", "[tblproducts]", "[ProductID] = " & product_id)

    onorder = DLookup("[UnitsOnOrder]", "[tblproducts]", "[ProductID] = " & product_id)
    
    If (onhand > ReOrderLevel) Then
        MsgBox "1"
        End
    Else
        'UPDATE tblproducts
        MsgBox "2"
        strSQL = "update tblproducts set UnitsOnOrder = " & rquantity & " where ProductID = " & [COLOR=red]product_id [/color]& " "
        DoCmd.RunSQL strSQL
    End If
End Function

 

[Bobnz]

sorry ginger to cryptic for me

This is where I'm upto now. I decided to forget about dlookup and use recordset instead.

my first if statement...

If (onhand > reorderlevel) Then....

isnt functioning, onhand is 3 and reorderlevel is 2, so when it gets to here it should end but is going straight to update query?

Function product_reorder(product_id As Integer)

Dim rst As New ADODB.Recordset
Dim strSQL As String

Dim onhand As Variant
Dim reorderlevel As Variant
Dim orderquantity As Variant
Dim onorder As Variant

strSQL = "SELECT UnitsInStock, ReorderLevel, OrderAmount, UnitsOnOrder "
strSQL = strSQL + "FROM tblproducts "
strSQL = strSQL + "WHERE ProductID = " & product_id

rst.Open strSQL, CurrentProject.Connection

onhand = rst.Fields("UnitsInStock").Value
'MsgBox onhand
reorderlevel = rst.Fields("ReorderLevel").Value
'MsgBox reorderlevel
orderquantity = rst.Fields("OrderAmount").Value
'MsgBox orderquantity
onorder = rst.Fields("UnitsOnOrder").Value
'MsgBox onorder

rst.Close

If (onhand > reorderlevel) Then
MsgBox onhand
MsgBox reorderlevel
End
Else
'UPDATE tblproducts
strSQL = "update tblproducts set UnitsOnOrder = " & orderquantity & " where ProductID = " & prodid & " "
DoCmd.RunSQL strSQL
MsgBox strSQL
End If

Set rst = Nothing


End Function

thanks for the replies guys.

WD

 

[PHV]

Why not simply this ?
DoCmd.RunSQL "UPDATE tblproducts SET UnitsOnOrder=OrderAmount WHERE UnitsInStock<=ReorderLevel AND ProductID=" & product_id

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GingerR]

too cryptic? It was an exact duplicate of the code you supplied, with two tiny changes in it (which I highlighed in RED), which you could have just copied and then pasted over your existing code and it would work.

In your last try at the code, you continue to put the wrong item in your UPDATE code. You have to change "prodid" to "product_ID", which is that exact same problem I highlighted for you above.

 

[Bobnz]

Again sorry ginger it was late last night when I read your reply. I had picked up the missing dim though.

PHV: yes much tidier.


this is were I'm at at the moment:

Function product_reorder(product_id As Integer)
    
    Dim rst As New ADODB.Recordset
    Dim strSQL As String
    
    Dim onhand As Variant
    Dim reorderlevel As Variant
    Dim orderquantity As Variant
    Dim onorder As Variant
    
    'query for recordset
    strSQL = "SELECT UnitsInStock, ReorderLevel, OrderAmount, UnitsOnOrder "
    strSQL = strSQL + "FROM tblproducts "
    strSQL = strSQL + "WHERE ProductID = " & product_id
        
    'create recordset
    rst.Open strSQL, CurrentProject.Connection
    
    'assign field values to vars
    onhand = rst.Fields("UnitsInStock").Value
    reorderlevel = rst.Fields("ReorderLevel").Value
    orderquantity = rst.Fields("OrderAmount").Value
    onorder = rst.Fields("UnitsOnOrder").Value
                        
     If (rst!UnitsOnOrder > 0) Then  'has stock already been ordered
        rst.Close
        MsgBox "Stock already on order"
        End
     End If
     If ((rst!UnitsInStock) > (rst!reorderlevel)) Then  'has reorder level been reached
        MsgBox onhand
        rst.Close
        End
    Else                        'reorder level reached UPDATE tblproducts and return
        rst.Close
        strSQL = "update tblproducts set UnitsOnOrder = " & orderquantity & " where ProductID = " & product_id & " "
        DoCmd.RunSQL strSQL
        MsgBox strSQL
    End If
    
    Set rst = Nothing
        
        
End Function

WD

 

[GingerR]

so is it working? what's the outcome?