Delete Record

[tempk]

Hi all, facing a little problem with my forms. Access gives me headaches everyday. Haha.

In a continuous form, I have a delete button with the following code

Private Sub cmdDelete_Click()
   On Error GoTo Err_cmdDelete_Click

   DoCmd.SetWarnings False
     If MsgBox("Confirm deletion of the record?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then
     DoCmd.RunCommand acCmdSelectRecord
     DoCmd.RunCommand acCmdDeleteRecord
   End If
Exit_cmdDelete_Click:
   DoCmd.SetWarnings True
   Exit Sub
Err_cmdDelete_Click:
   MsgBox Err.Description
   Resume Exit_cmdDelete_Click
End Sub

However, after the record is deleted, the focus is immediately set to the next field which creates a new record of null data. And since the fields are all required, I am forced to enter data into the new record, if not a pop up will inform to user to enter data.

I do not wish for this new record to be created everytime I delete a row. How do I go about it?

I'm learning more each day! Thanks in advance..

 

[llkhoutx]

After the deletion, set focus to a field outside the form containging the deleted record.

I use txtHoldFocus (visible, zero width, length & left, and no tabstop) to hold sub focus.

Why aren't you requering after the Delete, optionally then positioning to the record with a bookmark to the record immediately preceeding the deleted record?

A lot of events fire following a delete.

 

[tempk]

Pardon me because I do not understand..

The form is a popup which I opened as a dialog window. Would setting focus outside still work?

The form's recordsource is a table so how would a query come into place?

I'll try the bookmark on preceeding record and see if it works.

Thanks for the quick reply!

 

[tempk]

I inserted DoCmd.GoToRecord acDataForm,"frmName", acFirst
right behind accmdDeleteRecord. It's not a perfect solution but I'm wondering if any problem might occur?

Today is a day when I need more than the usual cups of coffee...

 

[godofhell]

Continues Form

I have added a continues form database, I added your code to the button and do not see any problems. If possible modify this database so that it opens the form as a popup and see what happens. I am not clear with the process you are using to trigger the continues form to open. If you explain that I might be able to help further.

 

[tempk]

The form is opened via a button containing:

Private Sub cmdAdd_Click()
Dim strLinkCriteria As String

strLinkCriteria = Me.txtDept

DoCmd.OpenForm "frmAddNew", , , , , acDialog, OpenArgs:=strLinkCriteria
End Sub

This opens my pop up form which has Rich's validate data function in the form_beforeupdate.

Since I limit the user to a dialog window and hid all the close buttons, they can only exit thru an exit button I created.

I placed a simple docmd.runcommand accmdclose for my exit button, it will override the function. But if there were any unfilled fields, the window would close and not save the record. If however, I add the function to the exit button (only close when all fields are filled in), I cannot close even though I decide not to enter data or when I delete the only record in the form(the beforeupdate fires off and the function is called).

So I'm in a conundrum on which is the better way to proceed. Any further help would be appreciated.

 

[llkhoutx]

Back to your original post, you code has the following lines:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

That code does not delete a record!

Am I missing something?

 

[tempk]

It does delete a record...why wouldn't it delete?

The plot thickens...

 

[godofhell]

You could set 1 of the fields so that is not set to required on the table, then you write code against that field to make sure users fill it in before the record is saved. Doing this will allow you to move the focus to this control without it complaining that you need to fill in the information. The other way would be to set the values of each field to "" before closing the form.
ie: Department set on table to be not required, to make sure users fill it in, you can write code as follows.

If Department.Value= "" then
msgbox "You must enter a Department Name"
Department.SetFocus
End If

Now you can keep the current delete code and move the focus to this field. Note the line added after the delete line.

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

DoCmd.SetWarnings False
If MsgBox("Confirm deletion of the record?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
Department.SetFocus
End If
Exit_cmdDelete_Click:
DoCmd.SetWarnings True
Exit Sub
Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click
End Sub

 

[tempk]

Hey, thanks for the reply. Managed to sort it out.

Mondays are always blue.