By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,983 Members | 2,035 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,983 IT Pros & Developers. It's quick & easy.

Unable to DELETE a RECORD from a FORM

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

Expand|Select|Wrap|Line Numbers
  1.     Dim strSQL As String
  2.     Dim strSQL2 As String
  3.     Dim Reply As String
  4.     Dim db As Database
  5.     Dim rst As Recordset
  6.     Dim SeqItemNo As Integer
  7.  
  8.     DoCmd.SetWarnings False
  9.  
  10.     Set db = CurrentDb()
  11.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  12.  
  13.     DoCmd.Save
  14.     DoCmd.GoToRecord , , acFirst
  15.  
  16.     SeqItemNo = 1
  17.  
  18.     Do
  19.         rst.Edit
  20.         Me.Requery
  21.         rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
  22.         If IsNull(rst!StkID) Then
  23.             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!")
  24.             If Reply = vbYes Then
  25.                 db.Execute "DELETE * FROM preordlin WHERE IsNull(StkID) And OrderNo = [Forms]![frmCustomerOrderForm]![sfrmSOHeader]![OrderNo]"
  26.             Else
  27.                 Exit Sub
  28.             End If
  29.         Else
  30.         rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
  31.         rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
  32.         rst!ItemNo = rst!ItemNo + SeqItemNo
  33.         rst.update
  34.         SeqItemNo = SeqItemNo + 1
  35.         End If
  36.         rst.MoveNext
  37.     Loop Until rst.EOF
  38.     rst.Close
  39.     Me.Requery
  40.     Set rst = Nothing
  41.  
  42.     cmdCheckStock.Enabled = False
  43.     cmdUpdateInformation.Enabled = True
  44.     cmdAssignDetails.Enabled = False
  45.     cmdcalcgrainmeasures.Enabled = False
  46.     cmdImportintoOrderline.Enabled = False
  47.  
  48.     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.
Sep 16 '08 #1
Share this Question
Share on Google+
3 Replies


P: 90
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.

Expand|Select|Wrap|Line Numbers
  1. "DELETE * FROM preordlin WHERE IsNull(StkID) And OrderNo = [Forms]![frmCustomerOrderForm]![sfrmSOHeader]![OrderNo]"
might work if you change it to:

Expand|Select|Wrap|Line Numbers
  1. "DELETE * FROM preordlin WHERE IsNull(StkID) And OrderNo =" & [Forms]![frmCustomerOrderForm]![sfrmSOHeader]![OrderNo]
Sep 16 '08 #2

100+
P: 129
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.

Expand|Select|Wrap|Line Numbers
  1. "DELETE * FROM preordlin WHERE IsNull(StkID) And OrderNo = [Forms]![frmCustomerOrderForm]![sfrmSOHeader]![OrderNo]"
might work if you change it to:

Expand|Select|Wrap|Line Numbers
  1. "DELETE * FROM preordlin WHERE IsNull(StkID) And OrderNo =" & [Forms]![frmCustomerOrderForm]![sfrmSOHeader]![OrderNo]
Thanks for the info, unfortunately it still didn't work.
Sep 16 '08 #3

NeoPa
Expert Mod 15k+
P: 31,347
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.
Sep 16 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.