Full Version: Problems deleting record
UtterAccess Forums > Microsoft® Access > Access Forms
zerotech
All. I am having a bit of trouble when trying to delete a record. I have a list box that I using to find a record based on what the user clicks. Then I have a label (used as button) that when the user clicks it asks them to re-type the part number and then click Ok to delete the part. However, I always get an error and it ends up deleting the part number out of the text box. The error that I receive is no current record seleted. I have tried using the SelectCurrentRecord before deleting but it does not seem to be working. Thanks in advanced for the help.


Private Sub Label28_Click()
Dim dResponse As Integer
Dim cResponse As String
Dim sResponse As Integer
cResponse = InputBox("Please re-type the part number to delete.", "Confirm Delete")
If cResponse = partNumber.Value Then
dResponse = MsgBox("Are you sure you want to delete the part " & partNumber & " from the database?", vbOKOnly)
Select Case dResponse
Case vbOK
DoCmd.RunCommand acCmdDeleteRecord
Me.List1.Requery
End Select
Else
dResponse = MsgBox("Sorry but " & partNumber & " and " & cResponse & " do not match. Please try again.", vbOKOnly, "Error")
DoCmd.CancelEvent
End If
End Sub
quest4
I once had a similar problem. A wise person told me to create a cmdButton with the wzard and select delete record. Then copy that code and replace my current code with the wizards code. It worked like a champ after that. It is worth a try. hth.
zerotech
Yeah. See I tried to use a transparent command button and then set focus there when the user clicked on the label but that did not work. I just tried to copy the code so it would now look like this.
Private Sub Label28_Click()
Dim dResponse As Integer
Dim cResponse As String
Dim sResponse As Integer
cResponse = InputBox("Please re-type the part number to delete.", "Confirm Delete")
If cResponse = partNumber.Value Then
dResponse = MsgBox("Are you sure you want to delete the part " & partNumber & " from the database?", vbOKOnly)
Select Case dResponse
Case vbOK
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.List1.Requery
End Select
Else
dResponse = MsgBox("Sorry but " & partNumber & " and " & cResponse & " do not match. Please try again.", vbOKOnly, "Error")
DoCmd.CancelEvent
End If
End Sub
Now it does delete the record but I get an error saying there is not current record
This is highlighted.
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Any suggestions would be greatly appreciated.
Thanks,
Jason
quest4
Wheneve I use an undo or delete, I do something like this:
DoCmd.Echo False
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 'Deete record
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings True
DoCmd.Echo True
This gets rid of the messages. Sorry I forgot to mention it. hth.
zerotech
The error is still there and the same line is being highlighted. I am not sure if it has to do with the message boxes and things like that but I am going to remove that to see what happens. I will post in a little bit the results.
Thanks,
Jaosn
zerotech
I have narrowed it down to be something with deleting the record. I just used a simple command
oCmd.SetWarnings No
DoCmd.RunCommand acCmdDeleteRecord
And I still get the same error. If anyone has an idea let me know. Thanks.
Jason
quest4
Try commenting out the requery line. See what happens. hth.
jokeme71
I am having the same problem.

THere is my code.

Private Sub Reset_Click()
On Err GoTo Err_Reset_Click

Dim stMsg1 As String, stTitle1 As String

stMsg1 = "This process will reset and backup the Paging Service Billing Database for use within the next billing period. THIS SHOULD BE DONE ONLY IF ALL OF THE PREVIOUS STEPS HAVE BEEN COMPLETED SUCCESSFULLY. Are you sure you wish to proceed?"
stTitle1 = " Reset and Backup this Database?"

DoCmd.Beep

If Me.Step1 = False Then
MsgBox ("The previous step must be completed before executing this step!")

Else:
If MsgBox(stMsg1, vbQuestion + vbYesNo, stTitle1) = vbYes Then
DoCmd.SetWarnings No
DoCmd.RunCommand acCmdDeleteRecord
'DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
'DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings Yes
MsgBox ("This step has completed successfully. Congratulations! You have successfully completed processing Paging Service 'Transaction Records' for this billing period.")
Call Shell("C:\Backup Paging Billing DB.bat", vbNormalFocus)
DoCmd.Quit

Else:
End If

End If

Exit_Reset_Click:
Exit Sub

Err_Reset_Click:
MsgBox Err.Description
Resume Exit_Reset_Click

End Sub

When I look at the debug it shows a value or "223" for the DoCmd.RunCommand acCmdDeleteRecord and a value of "70" for the DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70 when I used it.
quest4
Another thought, which could be dangerous, I am not sure about Case here. Maybe try it this way:
Dim Response As VbMsgBoxResult
Response = MsgBox("Are You sure You Want to Delete this Part?", vbYesNo + vbDefaultButton2, "New?")
If Response = vbYes Then
Your code
Else
Your code again
End If
Maybe I am off the wall here but maybe it is worth a shot. hth.
quest4
SA one thing I do know DoCmd.SetWarnings True or False, not Yes or No, someone yelled at me for that a long time ago. The code I posted works for me in several dbases, but I am runing Access2000. There must be a difference, that is probably why it is working in my stuff. Other than what I mentionsyour code looks good, except for the Else with the End If after it. why is that there? I don't think any of those things should cause you to error, but Access is a very funky thing at times. hth.
jokeme71
I added the Else... End If when trying to play around with the code to make this delete record work. This one really has me stumped. Because of the error it will not let me execute the CALL line in my code.
Thanks you :-)
quest4
Me to. The code should work. Well keep it posted maybe someone else can spot what we have missed. Sorry I could not do much for you.
jokeme71
I appreciate it. I will keep plugging away at it for sure... ;-)
zerotech
I would use probably a cancel event instead of an End If. That way it is sure to cancel the delete function. I have figured out what is causing the problem but not sure how to correct the problem. The record is being deleted, but it is not deleting the record. Like this. After you receive the error the record is still showing in the list (hence Requery), but it then tries to delete it again for some reason and it is not there.
How about deleting the current record from the list box. How exactly would I do that. Then I will just send a command to the Detail area to goto the first record. If someone could please show me how to delete a row out of the list box I would appreciate it. Thanks,
Jason
jokeme71
I have two If statements within my code. That is why I have the Two End Ifs.
My record deletes fine, but I still get the error message "No Current Record" and I cannot execute code after that.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.