Forms do not refresh after deleting record

[Farnham]

Hi,


I have a really hard nut to crack.

Ha have a main form containing 4 subforms in different tabs. From subform A I have a button that deletes a record in a list.

If MsgBox("Delete selected record?", _
vbYesNo + vbCritical, "Delete") = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True


The record is deleted and A is updated (I can see Me.ID change). The problem is the subform B that is presenting details from subform A. This doesn't update properly, it's still pointing at the deleted record (I print Me.ID). I have tried refresh, requery and DoCmd.RunCommand acCmdRefresh, but nothing works.

Anyone got any tips?

 

[rainman89]

you said you have tried requery, you did the subformname.requery?

 

[ghudson]

I believe that Me.Requery should refresh everything in the form including the subforms.

 

[stopher]

Have you tried requerying the subform. I think this is the syntax:

Forms!MainForm!SubFormControl.Requery

hth
Chris


Edit: Sorry, didn't see other posts until I'd posted - very slow me.

 

[Farnham]

you said you have tried requery, you did the subformname.requery?

I do this:
Forms!Base!Dependencies.Requery

Base is the main form and Dependencies is the subform B. Subform A is updated properly.

But this doesn't solve it.

 

[stopher]

Or maybe:

Forms!MainForm!SubFormControl.Form.Requery

 

[rainman89]

I do this:
Forms!Base!Dependencies.Requery

Base is the main form and Dependencies is the subform B. Subform A is updated properly.

But this doesn't solve it.

So you have a main form and 2 subforms?

 

[ghudson]

Try...

Me.[subform control name].Form.Requery

 

[Farnham]

So you have a main form and 2 subforms?

The main form doesn't do anything else than to hold my 4 subforms. So, 4 subforms. But I figured that if I solve this for one form, the rest will tag along.

 

[Farnham]

Try...

Me.[subform control name].Form.Requery

What is subform control name?

 

[ghudson]

What is subform control name?

The name of your subform within the form. In design view of your form, right click on the subform and select the properties option, look for the Name of the subform at the top of the All tab.

 

[Farnham]

Try...

Me.[subform control name].Form.Requery

The problem is that this has to be done from the main form (Base). And in base, nothing is done codewise. It's just a container for the subforms.

Or where in my mainform did you have in mind that I could put it?

BTW, thank you all for helping! Really appreciate it!

 

[ghudson]

Your main form has the delete code, right? Then that is where you have to requery your subforms. Add the requery code to your delete code.

If MsgBox("Delete selected record?", _
vbYesNo + vbCritical, "Delete") = vbYes Then
DoCmd.SetWarnings False
     DoCmd.RunCommand acCmdSelectRecord
     DoCmd.RunCommand acCmdDeleteRecord
     Me.[subform control name1].Form.Requery
     Me.[subform control name2].Form.Requery
     Me.[subform control name3].Form.Requery
     Me.[subform control name4].Form.Requery
DoCmd.SetWarnings True

 

[Farnham]

Your main form has the delete code, right? Then that is where you have to requery your subforms. Add the requery code to your delete code.

If MsgBox("Delete selected record?", _
vbYesNo + vbCritical, "Delete") = vbYes Then
DoCmd.SetWarnings False
     DoCmd.RunCommand acCmdSelectRecord
     DoCmd.RunCommand acCmdDeleteRecord
     Me.[subform control name1].Form.Requery
     Me.[subform control name2].Form.Requery
     Me.[subform control name3].Form.Requery
     Me.[subform control name4].Form.Requery
DoCmd.SetWarnings True

Actually, it's one of the subforms having the delete code.

 

[stopher]

Actually, it's one of the subforms having the delete code.

In which case you must provide the full reference:

Forms.[mainform control name].[subform control name].Form.Requery

 

[Farnham]

In which case you must provide the full reference:

Forms.[mainform control name].[subform control name].Form.Requery

I missed some parts of my delete routine. I actually have that line already.

This is the complete routine:


Private Sub btnDelete_Click()
On Error GoTo Err_btnDelete_Click
If MsgBox("Delete selected record?", _
vbYesNo + vbCritical, "Delete") = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

Forms!Base!Dependencies.Form.Requery

DoCmd.SetWarnings True
End If
Exit_btnDelete_Click:
Exit Sub
Err_btnDelete_Click:
MsgBox Err.Description
Resume Exit_btnDelete_Click

End Sub

 

[stopher]

Did you try:
Forms!Base!Dependencies.Requery

Also,I'd put the statement after the Setwarnings True line

 

[Farnham]

Did you try:
Forms!Base!Dependencies.Requery

Also,I'd put the statement after the Setwarnings True line

I have tried them both.

The strange thing is, I got it to work a while ago when I moved it below SetWarnings. It moved forward in the record in the Dependencies subform. But, when I tried it in safemode it didn't work. And when I tried it again in developmode (?) it didn't work. I got a new error saying:

Run-time error 2424:

The expression you entered has a field, control, or property that Microsoft Access can't find.

 

[stopher]

And what happens when you try the other statement after setwarnings true?:
Forms!Base!Dependencies.Form.Requery

 

[Farnham]

And what happens when you try the other statement after setwarnings true?:
Forms!Base!Dependencies.Form.Requery

Right now it's really strange. I have a version - 1.3 - that is the base for the not working version of delete. When I change my delete-button with
Forms!Base!Dependencies.Form.Requery and a couple of other changes it works fine in developer mode. The record moves and changes as it should.

When I move to safe mode something happens. When I press "enable macro" it says something like "The command or action "SaveRecord" isn't available right now" and it doesn't work as it should when I delete a record.

If I move back to developers mode it doesn't work anymore.

I find this really strange. What can happen in the safe mode that spoils all the work?