Problem Deleting Record?

[agehoops]

For some reason the form will not delete the record. I've tried two versions of the record delete function each one failing. By using

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

It simply does nothing, but by using

DoCmd.RunCommand acCmdDeleteRecord

I get an error saying that the function is not currently available?

The property of the form to allow deletions is set to yes, so i really can't see why it won't delete.

Thanks

 

[MarkK]

It may be that the recordsource of the form is a query that doesn't allow deletion. A query that sums data from many records, for instance, does not allow deletion.

 

[agehoops]

hmm the record source of the form is based on just one table not a query.

 

[RuralGuy]

Where do you have the code you posted? In what event?

 

[agehoops]

It's behind a button so when the User clicks it and the button is on the same form as the record to be deleted. I've got almost the exact same thing on another form which also wasn't working earlier, however I sorted that one out, but when i changed the same things for this one it made no difference

 

[RuralGuy]

If you are on a new record that has not been saved yet then add If Me.Dirty Then Me.Dirty = False before you try to delete.

 

[agehoops]

Same problem. It is set to save the record before doing anything else, but i'm testing it on a record that's been there for ages, but that is still doing the same thing

 

[RuralGuy]

Can you post a sample db that demonstrates the problem? Go ahead and remove any sensitive data and zip it up and then include instructions on how to duplicate the problem.

 

[agehoops]

Ok, i've removed loads of things and HOPEFULLY it'll work how it is supposed to, but you may get some errors thrown at you due to files it needs and such but it'll hopefully be alright.

Ok, when you open it, firstly open the Login form, otherwise everything will fail. Login to the Admin account with the password test. Then click the top button on the menu which is "Staff Details" then pick a record, and click delete. That is where it fails.

Also, when you open the zip file, you'll need to copy the .dll and the .bas file into the same folder as the database for it to work properly.

View attachment Help.zip

Thanks for your time.

 

[RuralGuy]

I won't pretend to understand what all of those queries are doing but you do seem to be doing a few other things besides trying to delete the Staff Member behind the button. Have you tried deleting the record first and then running the queries?

Private Sub cmdDelRecord_Click()
   DoCmd.OpenForm "frmProgress"
   Forms!frmProgress.Progress = 1
   'Save Record
   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
   Forms!frmProgress.Progress = 10
   'Ask user if they're sure they want to remove member of staff
   If MsgBox("Are You Sure You Want To Remove This Member Of Staff?", vbYesNo, "Are You Sure?") = vbYes Then
      Forms!frmProgress.Progress = 15
      'Turn of warnings
      DoCmd.SetWarnings (False)
      Forms!frmProgress.Progress = 20
      On Error GoTo Err_cmdDelRecord_Click

      'Run all append and delete queries

      DoCmd.OpenQuery "qryStaffMove"
      Forms!frmProgress.Progress = 25
      DoCmd.OpenQuery "qryMoveNVQ"
      Forms!frmProgress.Progress = 30
      DoCmd.OpenQuery "qryMoveTraining"
      Forms!frmProgress.Progress = 35
      DoCmd.OpenQuery "qryMoveSupervisions"
      Forms!frmProgress.Progress = 40
      DoCmd.OpenQuery "qryMoveAppraisals"
      Forms!frmProgress.Progress = 45
      DoCmd.OpenQuery "qryNVQDelete"
      Forms!frmProgress.Progress = 50
      DoCmd.OpenQuery "qryTrainingDelete"
      Forms!frmProgress.Progress = 55
      DoCmd.OpenQuery "qrySupervisionsDelete"
      Forms!frmProgress.Progress = 60
      DoCmd.OpenQuery "qryAppraisalsDelete"
      Forms!frmProgress.Progress = 65
      [ID].SetFocus
      DoCmd.RunCommand acCmdSelectRecord
      DoCmd.RunCommand acCmdDeleteRecord

      'DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
      'DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
      Forms!frmProgress.Progress = 70

      On Error Resume Next
      DoCmd.GoToRecord , , acPrevious
      DoCmd.GoToRecord , , acPrevious

      Forms!frmProgress.Progress = 100
      DoCmd.Close acForm, "frmProgress"
      'Turn warnings back on

      DoCmd.SetWarnings (True)
Exit_cmdDelRecord_Click:
      Exit Sub

Err_cmdDelRecord_Click:
      MsgBox Err.Description
      DoCmd.Close acForm, "frmProgress"
      Resume Exit_cmdDelRecord_Click

   Else
   End If

End Sub

 

[agehoops]

haha yea sorry should've explained a bit more thoroughly. All the queries move the data in the linked tables, then delete the data in the linked tables ready to have the main record deleted.

Unfortunately, if I try to delete the member of staff first, it just stops it due to there being related records in the other tables if there are any, so they need to be run first.

 

[RuralGuy]

Try deleting the Staff Member without using the Progress form.

 

[agehoops]

Damnit! That works Now how do I go about using the progress form without it stopping it from working? I mean obvious the Delete function is much more important but would be nice to have the progress meter?

 

[RuralGuy]

Two ways that I know. Build your Progress meter into your existing form (and you do not need the ActiveX control) or use the built in progress meter on the Status Bar.

 

[agehoops]

How Do i build it into the form? I know there is the status bar one but i want to try and avoid that as i want it to be as visible and obvious as possible to the users

 

[RuralGuy]

They are real easy to implement; all you need it some geography on your form that is now being used and you leave the meter invisible until you need it. Jeff has several examples on this link to his site. Post back if you need further assistance.

 

[agehoops]

ah brilliant, thanks so much! I'll take a look through it all later. Mouse battery is dying, and it's about 2:20am here so might head to bed soon. Thanks for all your help, i'll reply if i've got more db problems, but thanks again so much. Appreciate it

 

[RuralGuy]

I thought it had to be getting late there. Talk to you later maybe. Sleep tight.

 

[agehoops]

Hey, thanks again. Got one up and running now. Had the same problem as before but just told it to close the form before deleting the record and it works a treat. I'll leave you the credit thing on here later as it wont let me right now. Says I need to spread it around first