Delete record button on form

[garywood84]

I have created a form and have put a delete button on it to delete the active record. This works fine in that it deletes the record as required.

However, at the top of the form is a combo box which can be used to find records. Once a record has been deleted, it is still listed in this combo box but as #Deleted!. Closing down the form and reopening it corrects this and the #Deleted! entry disappears.

How can I get around this? Possibly by making Access refresh the form after the delete command? If that's the correct solution, how do I do it?! Otherwise, is there a better way.

Thanks in advance,

Gary

 

[*Pete*]

In the code for the delete button, I add the line:

Me.Recalc

Don't know if this is the correct way of doing it but it works for me.

Regards

Pete

 

[garywood84]

Pete,

Thanks for this. Adding that code to the button now makes the form refresh after the record is deleted. However, for some reason the combo box is still not refreshing so the deleted record still exists there, with #Deleted! in every field.

How can I make this combo box refresh as well?

Gary

 

[Fear Naught]

You need to requery the combo box. Use code something like this after deleting the record:

me.comboboxname.requery

 

[neileg]

Requery is the command you need.

 

[garywood84]

This is the code that is currently run the the delete button. Where do I need to insert the additional code to refresh the form and requery the combo box?

Private Sub DeleteRecord_Click()
On Error GoTo Err_DeleteRecord_Click


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

Exit_DeleteRecord_Click:
    Exit Sub

Err_DeleteRecord_Click:
    MsgBox Err.Description
    Resume Exit_DeleteRecord_Click
    
End Sub

Gary

 

[Fear Naught]

Gary,

I have copied you code and added the relevant line of code for you. You will of course have to change the "comboboxname" part with the name of the combo box on your form.

Depending on what version of access you are using I would replace your "DoCmd" lines with the following:

DoCmd.RunCommand acCmdDeleteRecord

Private Sub DeleteRecord_Click()
On Error GoTo Err_DeleteRecord_Click


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

    [COLOR="Red"]me.comboboxname.requery[/COLOR]

Exit_DeleteRecord_Click:
    Exit Sub

Err_DeleteRecord_Click:
    MsgBox Err.Description
    Resume Exit_DeleteRecord_Click
    
End Sub

I hope this helps