Strange delete record issue (involving msgbox)

[Craggy]

Hi,

I am trying to pop up a yes/no message box so that a user can delete a record in a continuous form. The default delete message is a bit sloppy because it seems to move the continuous form to the position of the record u want to delete. If you then choose to not to delete the record it looks to a user as if it has deleted every record before the one you chose not to delete.

To get around this I made a yes/no msgbox pop up and only after saying yes did the delete action take place. This bypasses the default effect caused when choosing not to delete the record.

This was the code:

If MsgBox("Are you sure you want to PERMANENTLY delete this record?", vbYesNo, "Delete?") = vbYes Then
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings True
Else
'do nothing
End If

Works fine on my main form, but in further forms it deletes records from my main form rather than the form the delete button is on.

I tried to see what the issue was and I found something really strange out the following code works correctly:

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

This deletes the record in the relevant form, but as soon as you put a message box before it, even if it is has no yes/no request then the code suddenly stops deleting the correct record and deletes a record from my main form.

Msgbox “test”
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

It also removes the default "are you sure" message that access usually produces.

This is quite annoying and I can’t think of why this would happen, unless the message box is somehow removing the focus from the current form.

It’s really quite important that I get a working delete button since the default version is causing many users to become confused.

Thanks

[Craggy]

Is there a way of telling Access to delete a specific from a specific table using vba?

Maybe you can set up a delete query to do this but it seems like a hell of a lot of work for something that seems like a simple request.

[nico5038]

I don't use the " DoCmd.DoMenuItem" as it's sometimes not avaiable. (It does indeed execute the menu items and isn't really 100% reliable)

Best to use in the button's code:

currentdb.execute ("delete * from tblYours where ID=" & Me!ID)
 

This will delete the row the user presses the button on as it refers to the Me!ID of the current record. Just make sure that the ID is unique (and has the name of your unique ID) and that the tablename is your tablename.

Getting the idea ?

Nic;o)

[Craggy]

Excellent, thats just what i was looking for.

Now Im working on trying to refresh the form so that the deleted line is removed rather than displayed as a blank row.


thanks

[nico5038]

Just add a line with:

Me.Requery
 

Success with your application !

Nic;o)