Deleting records in a subform

[Rats]

I have a "display only" subform that is updated by a popup form. If I add or amend an entry the details of the changes are displayed immediately in the display window by requerying the form "afterupdate". However, I have a delete button on my popup form which will delete a record but replaces it with the word #deleted in each of its fields. The #delete will only disappear once I have moved to another record which I don't want to do.

Can any one tell me why the amend/insert update works well but the delete behaves in this way.

How can I set up the delete function to function and remove the #deleted message without moving to another record. I have tried adding a save button but this doesn't fix it.

Thankyou

 

[RuralGuy]

Do you requery the SubForm or the Form? Do you execute the requery from your PopUp form?

 

[Rats]

Hi RG

The form that I want to update is a subform in a tabbed control. The popup form holds the requery command in its after update event. As I mentioned this works well for new records and changes but not for deletes. I have now gotten around it by placing a save button on the subform so that when I see #deleted I can press that and the deleted record is removed.
I don't like this very much and would prefer that the #deleted didnt appear at all. I have tried to include the save record command after the delete command with no success.

 

[RuralGuy]

The #deleted should vanish after a requery or a refresh. Could you post the code you are using to requery the SubForm?

 

[Rats]

Hey RG here it is

Private Sub Form_AfterUpdate()

DoCmd.Echo False
Forms![Customers]![CapitalMovementQry].Form.Requery

DoCmd.Echo True
End Sub

Would the fact that the delete button controls the delete function affect the after update event of the form that it is placed on. IE the add and amend functions occur on the form but the delete is a seperate command. For what its worth!!!

 

[RuralGuy]

LOL - remove both Echo commands.

 

[Rats]

Done but no change. Still displays the #delete until I press the save button.

 

[RuralGuy]

Could you post the code that invokes the pop-up form? Are you using the acDialog parameter?

As a test, change your save button to just Refresh the subform.

 

[Rats]

I will do the refresh thing now.

Private Sub capmvmntinputbtn_Click()
On Error GoTo Err_capmvmntinputbtn_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "CapitalMovementFormInpt"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Requery
Exit_CapMvmntInputBtn_Click:
    Exit Sub

Err_capmvmntinputbtn_Click:
    MsgBox Err.Description
    Resume Exit_CapMvmntInputBtn_Click
    
End Sub

Tried refresh. Can see the form flicker but the #delete remains. Have been looking at the requery info in MS help and it says that deleted records will be removed on requery. So dont know what I'm doing wrong.

 

[RuralGuy]

Change this:

DoCmd.OpenForm stDocName, , , stLinkCriteria
Requery

to:

DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
Me.Requery

and let's see what happens.

 

[Rats]

OK I have added a me.requery to the open form button (no change except that if I open it again the #deleted vanish) I added a me.requery to the onclose event of the input pop up form with no change.

It is puzzling that the desired outcome is achieved by moving to the next record or by pressing a save button but I can't get it to behave the same way when I close the popup form which is all it needs to do.

 

[RuralGuy]

Did you add the acDialog argument? What time is it there by the way? It is ~ 0200 here.

 

[Rats]

Now you have me stumped. What does the "ACDialog" argument do. I haven't knowingly come upon that one yet. Can't find it in help. Heeellllp!

By the way your last message was received here at 5.00pm Thursday, 3 November. So I figure your about 9 hours ahead or 15 hours behind. Whatever, you've either got a sleeping problem or your very dedicated to be providing help at that time of the night. But I appreciate your help, thanks.

 

[RuralGuy]

It looks like you are 15 hours ahead of me. The acDialog argument forces the next form to open as modal and popup and pauses the code in the first form until the next form is closed or made invisible.

 

[Rats]

Hmmm, not sure what your after but I can tell you that the popup form is not modal and border style is sizeable not dialog (if that is significant).

What I don't understand is that after doing the delete by using the popup form the word #deleted appears immediately in the display subform. If I close down the popup nothing happens. If I use the navigation buttons to move to the next clients record the #delete disappears and the records on that form shuffle up to take out the vacant row left by the deleted record.
Obviously the change record event triggered by the navigation buttons has the desired affect. Why then can't I emulate that event in either the close event of the popup form or part of the code for the delete command button so that it deletes the row then updates the subform.

Would it help if I uploaded the database for you to have a look at?

 

[RuralGuy]

Uploading the db would make it a lot easier, thanks.

 

[Rats]

OK I'll upload to our website. Get back to you in a minute. May need to give you a sign on and password. How can I do that securely?

 

[RuralGuy]

Do you want to just email it to me? Rural Guy at Wild Blue dot Net without the spaces. You could just email the zipped mdb if you like. I have high speed connect.