Delete not working

[Ally]

Hi All

I'm back with Access again for a short time (hurray) where I'm picking up a problem for one of Col's dbs. And I'm really stuck!

It's a car parking db and you go into the record from a form with a list box on.

There is a delete button, which has the usual wizard code behind it:

On Error GoTo Err_Delete_Click
Dim rsp As Variant

rsp = MsgBox("You will delete the entire record and associated permit and car details. Are you sure you want to do this?", vbQuestion + vbYesNo, "Warning!!!")

If rsp = vbYes Then

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
   
    Else
    
    Exit Sub
End If

Exit_Delete_Click:
    Exit Sub

Err_Delete_Click:
    MsgBox Err.Description
    Resume Exit_Delete_Click

Basically all this did was bring up the msgbox, bleep and not do anything else. When I debugged, it appeared to go through the code no problem.

I looked at an older db of mine and changed a few bits:

• added docmd.setwarnings false and true before and after code
• changed the Variant to Integer
• Added docmd.close and requery of the form that the listbox is on, after the DoCmd. .... ... acVer70.

This closes the form, but doesn't appear to delete or requery, and when you click on the list box, there's now an error message, "No Current Record".

Anyone know what I'm doing wrong please.

Many thanks - Ally
I'm going now, but will be back on Monday.

 

[Rich]

Very few now know which menu item does what, try changing them to the RunCommand constants
I think it's RunCommand acCmdSelectRecord and RunCommand acCmdDeleteRecord resp.

 

[Ally]

Thank you Rich. I tried that but am getting the same problem - "No current record".

My code now looks:

Dim rsp As Integer

DoCmd.SetWarnings False
rsp = MsgBox("You will delete the entire record and associated permit and car details. Are you sure you want to do this?", vbQuestion + vbYesNo, "Warning!!!")

If rsp = vbYes Then

    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.Close
    
    Else
    
    Exit Sub
End If

DoCmd.SetWarnings True

I forgot to say that I'm using Acc 2000 - which I really don't like but this is what I'm presuming this one had to be written in.

The drop-downs in VB are only flashing up for a second, then going red, disappearing and the cursor moves back a space to the end of the last word which is really annoying. I tried converting it back to '97 to see if that made a difference but it did the same thing!

 

[Ally]

Any more ideas anyone please!?

 

[Rich]

The drop-downs in VB are only flashing up for a second, then going red, disappearing and the cursor moves back a space to the end of the last word which is really annoying. !

I suspect you have a form open with the timer running, close it first

 

[Ally]

Right! Back on this thing again!

I got rid of the timer for the time being.

I now have XP loaded and am using this.

My code now looks like:

On Error GoTo Err_Delete_Click
Dim rsp As Integer

rsp = MsgBox("You will delete the entire record and associated permit and car details. Are you sure you want to do this?", vbQuestion + vbYesNo, "Warning!!!")

If rsp = vbYes Then

    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.Close
    DoCmd.SelectObject acForm, "frmPermitSearch"
    DoCmd.Restore
    Else
    
    Exit Sub
End If
Exit_Delete_Click:
    Exit Sub

Err_Delete_Click:
    MsgBox Err.Description
    Resume Exit_Delete_Click

Now I get the error message

"The command or action 'DeleteRecord' isn't available now."

Please - anyone know what is wrong here!

 

[Rich]

Is the form PopUp/Modal ?

 

[Ally]

No, neither.

 

[Ally]

Here's a copy of it.

Go to Switchboard > Search for Permit No > Click on anyone.

The delete button is on this screen.

 

[Rich]

Need a97ver

 

[Ally]

Voila! 97 version.

 

[le888]

I did several changes on the forms. Frist, I put me.recalc on the frmPermitSearch. Then, on the delete button, I took off me.Restore and on the query "qryUserTblData" I took off the table "tblItemNumHold".
Take a look on the database.

Hope this help,


Le

 

[crizzottzz]

I'm really interested in finding a resolution to this problem. I encountered it today. I have a database where I dragged a button onto a form, used the wizard to automate the standard "Delete Record" and it works no problem.

I advised a coworker to do the same, and the row is not deleted from the table. No warnings, no failure, simply not deleted. Both using Access 2003 on the same server using Access 2000 file format.

 

[Rich]

On Error GoTo Err_Delete_Click

Dim rsp As Variant

rsp = MsgBox("You will delete the entire record and associated pool permit. Are you sure you want to do this?", vbQuestion + vbYesNo, "Warning!!!")

If rsp = vbYes Then

Screen.ActiveForm.SetFocus
RunCommand acCmdSelectRecord
RunCommand acCmdDeleteRecord
Else

Exit Sub
End If


Exit_Delete_Click:
Exit Sub

Err_Delete_Click:
MsgBox Err.Description
Resume Exit_Delete_Click

End Sub

 

[Ally]

Thank you all for your help.

Rich: once I've pressed delete, I close that record, then when I click on the search screen again, it comes up with "No Current Record", which is one of the problems that was happening before.

Le888: The database has been saved as read-only and whenever I try anything, it won't allow me, then throws me out of access!

 

[le888]

Try this one...

Le

 

[Ally]

Unfortunately, it says compressed folder is invalid or corrupt!

 

[le888]

Second try...

Le

 

[Ally]

No, sorry! Same thing again.

 

[le888]

Ok. Follow these steps.

1) Goto the "frmSearchPermit" , click on current and put the code:

Private Sub Form_Activate()
    Me.Recalc
End Sub

2) Goto the "tblUser" form, and put code on the delete button:

On Error GoTo Err_Delete_Click
Dim rsp As Integer

rsp = MsgBox("You will delete the entire record and associated permit and car details. Are you sure you want to do this?", vbQuestion + vbYesNo, "Warning!!!")

If rsp = vbYes Then

    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.Close
    DoCmd.SelectObject acForm, "frmPermitSearch"
    
    Else
    
    Exit Sub
    
End If

Exit_Delete_Click:
    Exit Sub

Err_Delete_Click:
    MsgBox Err.Description
    Resume Exit_Delete_Click

3) Goto the query "qryUserTblData" , take off the table "tblItemNumHold"
The query should have only one table which is ""tblUser"

Try these steps.

Le