Another SetWarnings Question (1 Viewer)

[wizcow]

I can't get rid of the "No Current Record" message.

My form runs this code in the 'On Current' event to get the last stock take date.
If there has not been a stocktake done for this record I get the above error message box.

 Dim db As DAO.Database   'Type of reference Tools>>References.. Microsoft DAO 3.6 (or highest)
    Dim rs As DAO.Recordset  'A place for the query result to live (recordsets)
    Dim strSQL As String     'A place for the current query to live
    Dim lngPartNo As Long    'A place to store the part number
    lngPartNo = [Forms]![Part]![ItemNo] 'get part number from a form
    
    Dim lngStockTakeDate As Long
    Dim lngLastStockTakeQty As Long

    'Get the last StockTakeDate and Quantity for the part
    strSQL = "SELECT TOP 1 Inventory.StockTakeDate, InventorySub.Quantity" & _
            " FROM Inventory INNER JOIN InventorySub ON Inventory.InventoryID = InventorySub.InventoryID" & _
            " WHERE (InventorySub.ItemNo) = " & lngPartNo & _
            " ORDER BY Inventory.StockTakeDate DESC;"
      
    'Set the database to this database and open the query results in a recordset
    Set db = CurrentDb
    [B]DoCmd.SetWarnings False[/B]
    Set rs = db.OpenRecordset(strSQL)
    [B]DoCmd.SetWarnings True[/B]
        With rs
            'Check to see if there has been an inventory taken
            If .RecordCount > 0 Then
                'Assign a place for the last StockTakeDate and Quantity to live
                datStockTakeDate = rs!StockTakeDate
                lngLastStockTakeQuantity = rs!Quantity
                'Pass value to form
                Me!LastInventoryDate = datStockTakeDate
                Else
                Me!LastInventoryDate = "No Data"
                lngLastStockTakeQuantity = rs!Quantity
            End If
        End With

    'Empty the strSQL recordset so you can open a new one
    rs.close

I thought the setwarnings would stop the error message but it does not.
Anyone see what I doing wrong?

Thanks
Tom

 

[spacepro]

Haven't looked at your code in detail but you could do something like:

if err.number = 3021 then
msgbox "No Current records!" & "Exiting Function",vbcritical,"Error"
end if

Andy

 

[wizcow]

Thanks for the reply Andy.

I would rather not have any message.

If there is no record the code is supposed to put "No Data" in the textbox that the code normally fills with a date. (LastInventoryDate)

Tom

 

[spacepro]

You could just then
change it to:

if err.number = 3021 then
me.lastinventorydate = "No Data"
msgbox "No Stock Take has taken place",vbinformation,"Message"
end if

or add into the code something like :

if rs.recordcount = 0 then
me.lastinventorydate = "No Data"
msgbox "No Stock Take has taken place",vbinformation,"Message"
exit sub
end if

You could leave the msgbox line out.

Hope this helps

Andy




Andy

 

[wizcow]

Perfect!

Thanks Andy

Tom