Unable to DELETE a RECORD from a FORM

[Constantine AI]

Hi i am trying to DELETE a Record if the details do not exist within a table. Here is my code below;

    Dim strSQL As String
    Dim strSQL2 As String
    Dim Reply As String
    Dim db As Database
    Dim rst As Recordset
    Dim SeqItemNo As Integer
 
    DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    DoCmd.Save
    DoCmd.GoToRecord , , acFirst
 
    SeqItemNo = 1
 
    Do
        rst.Edit
        Me.Requery
        rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
         If IsNull(rst!StkID) Then
            Reply = MsgBox("There are Stock Items present that do not belong to our existing Stock files. Would you like to DELETE them?", vbYesNo, "None Existent Stock Details!")
            If Reply = vbYes Then
                db.Execute "DELETE * FROM preordlin WHERE IsNull(StkID) And OrderNo = [Forms]![frmCustomerOrderForm]![sfrmSOHeader]![OrderNo]"
            Else
                Exit Sub
            End If
        Else
        rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
        rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
        rst!ItemNo = rst!ItemNo + SeqItemNo
        rst.update
        SeqItemNo = SeqItemNo + 1
        End If
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    Set rst = Nothing
 
    cmdCheckStock.Enabled = False
    cmdUpdateInformation.Enabled = True
    cmdAssignDetails.Enabled = False
    cmdcalcgrainmeasures.Enabled = False
    cmdImportintoOrderline.Enabled = False
 
    DoCmd.SetWarnings True

I have tried many ways in which to DELETE particular records, all either failed or displayed a variety of messages such as Too Few Parameters, I am locked out by another user, Error due to StkID doesn't exist (Which i why i am wanting to delete the item). The only way i managed to delete these records is physically pressing the delete button on every record itself.

Could anyone tell me why this record will not delete? At present the code functions without errors and just moves on to the next record, but doesn't delete any in the process.

[mandanarchi]

I'm not an expert here, but assuming you've got the [forms]!.....![orderno] right, I had a similar problem with something like this a while ago, but a SELECT not a DELETE.

"DELETE * FROM preordlin WHERE IsNull(StkID) And OrderNo = [Forms]![frmCustomerOrderForm]![sfrmSOHeader]![OrderNo]"

might work if you change it to:

"DELETE * FROM preordlin WHERE IsNull(StkID) And OrderNo =" & [Forms]![frmCustomerOrderForm]![sfrmSOHeader]![OrderNo]

[Constantine AI]

I'm not an expert here, but assuming you've got the [forms]!.....![orderno] right, I had a similar problem with something like this a while ago, but a SELECT not a DELETE.

"DELETE * FROM preordlin WHERE IsNull(StkID) And OrderNo = [Forms]![frmCustomerOrderForm]![sfrmSOHeader]![OrderNo]"

might work if you change it to:

"DELETE * FROM preordlin WHERE IsNull(StkID) And OrderNo =" & [Forms]![frmCustomerOrderForm]![sfrmSOHeader]![OrderNo]

Thanks for the info, unfortunately it still didn't work.

[NeoPa]

I think line #13 is extraneous (not required). What is it there for? I can't see that it's doing anything.

Likewise line #20 shouldn't be there (after .Edit).

Other than that, I suspect the DELETE fails due to the record being locked by your VBA code. You won't see the warning message because they're disabled.