Delect record from table if criteria is met?

[Guest]

good day all,

What I am trying to do is delete a record from a message box, perform a
requery then allow me to add a new record.

below is my code that I am using, the after update is on a lookup on
tblinventory to add the inventory item to my invoice, but I am trying to set
up a custom Message Box with record delete only if the orderid from the
tblinventory matches the orderid from a record that is currently in my
tblinvoicedetails, is this possible?

the main form is frminvoices with frminvoicedetailssubform if this helps...

thanks,

Brook

Begin Code:
Private Sub cboordnum_AfterUpdate()

If Me.orderid = Me.orderid Then

If MsgBox("Duplicate Record?", vbYesNo) = vbYes Then
CurrentDb.Execute "DELETE FROM qryinvoicedetails WHERE OrderID = " &
Me.orderid


Else

Me.orderid = Me.cboordnum.Column(1)
Me.DesignNumber = Me.cboordnum.Column(2)
Me.DesignName = Me.cboordnum.Column(3)
Me.Quality = Me.cboordnum.Column(4)
Me.Size = Me.cboordnum.Column(5)
Me.SqFt = Me.cboordnum.Column(6)
Me.PricePerSqFoot = Me.cboordnum.Column(7)
Me.TotalPrice = Me.cboordnum.Column(8)
Me.shippingcost = Me.cboordnum.Column(9)
Me.invoicetype = Me.Parent.Form!invoicetype
Me.clientname = Me.Parent.Form!cbocompanyinfo.Column(1)

End If
End If


End Sub
End Code:

 

[Wayne Morgan]

If Me.orderid = Me.orderid Then

This will almost always be True, sort of like saying "If 1=1 Then". The
exception would be if the item was Null, then the result would be Null, not
True. I believe you are trying to see if the value in the textbox OrderID
already exists in the underlying recordset. There are a few ways to do this,
one way would be to use DLookup().

If Not IsNull(DLookup("OrderID", "qryinvoicedetails", "OrderID = " &
Me.OrderID)) Then

DLookup() will return Null if the value isn't found, so if it's Not Null,
then the value was found. This syntax assumes that OrderID is a number data
type.

Another way to do this would be to do a search on this field in the form
using the form's RecordsetClone, FindFirst, and the NoMatch property of the
recordset.