AfterUpdate event won't refresh subform!

[Remster]

This is a follow-up to this solved thread (sorry, Rural Guy).



I have a form ('Mainform') that contains a subform ('Subform') that contains combo boxes populated with values from a lookup table ('Lookuptable'). I have a further form ('Lookupform') that's used for inputting data to the lookuptable.

I have a refresh button on Mainform that successfully refreshes the data in the combo boxes in Subform whenever an update is made to Lookupform. I've tried to do away with the refresh button by adding AfterUpdate events to Mainform and Subform. But it doesn't work!

Here's my code:

Private Sub Lookupform_AfterUpdate()
On Error GoTo Err_Lookupform_AfterUpdate
Me.Refresh
Me![Subform].Form.Refresh
Exit_Lookupform_AfterUpdate:
Exit Sub
Err_Lookupform_AfterUpdate:
MsgBox Err.Description
Resume Exit_Lookupform_AfterUpdate
End Sub

What am I doing wrong?

(For info, I've tried 'requery' as well as 'refresh'.)

[RuralGuy]

Nothing for which to be sorry! Any chance you can post a sample of your db so we can both be looking at the same thing?

[ConneXionLost]

Just a shot in the dark here, but it looks like you want to refresh the Mainform & Subform while the Lookupform still has the focus. If so, then you'll need to name the main & subform in your code instead of using "Me" since Access will assume you're referring to the Lookupform.

Cheers,

[slave138]

I think it's probably in the wrong event. Instead of AfterUpdate, I would suggest putting the code in the OnChange event. AfterUpdate would only trigger when the parent form's record source is updated (assuming it's bound to a record source) so it doesn't always fire when expected.

[Remster]

Neither of those suggestions works, assuming I've tried them properly, so I've cobbled something together to demonstrate my problem (see attached).

The first subform (headed 'Friend of') contains dropdowns populated with data from the table corresponding to the main form. The second subform (headed 'Hobbies') contains dropdowns populated with data from a lookup table. The refresh button works, but the OnChange event doesn't.

(Note that the very first record you enter after opening the form appears in the appropriate dropdown, but I've tested this and it has nothing to do with the OnChange event.)

[slave138]

I guess I don't understand what exactly is supposed to happen but Refresh doesn't seem to do anything either. What do you expect it do, ideally?

[Remster]

I guess I don't understand what exactly is supposed to happen but Refresh doesn't seem to do anything either. What do you expect it do, ideally?

As soon as a new record is added in the main form, the relevant name should appear as an option in the dropdown list in the first subform. As I said, this happens for the first new record I add after opening the main form but not for any subsequent records. But clicking on Refresh remedies this.

So, for example, try adding 'Mike Green' as record 5. This will appear immediately in the dropdown list in the first subform. But then try adding 'Wendy Taylor' as record 6. This won't appear in the dropdown list until you click on Refresh. I want it to appear automatically.

Mutatis mutandis, the same goes for the second subform.

[slave138]

Now that I understand better, you should put the following in the LostFocus event for each text field:

Me.sfmFriendship.Form.FriendOf.Requery

[Remster]

Now that I understand better, you should put the following in the LostFocus event for each text field:

Me.sfmFriendship.Form.FriendOf.Requery

Thank you. I couldn't get that to work (could you?) but I have found success with something I've based on it:

Private Sub FirstName_LostFocus()
On Error GoTo Err_FirstName_LostFocus

Me!sfmFriendship.Form.Refresh

Exit_FirstName_LostFocus:
Exit Sub
Err_FirstName_LostFocus:
MsgBox Err.Description
Resume Exit_FirstName_LostFocus
End Sub

Setting aside the fact that this seems to work, can you see any problem with it?

One final question. I've put this in the LostFocus event of the Hobby field in frmHobby, which links to the sfmPersonHobby (the second subform) within frmPerson:

Private Sub Hobby_LostFocus()
On Error GoTo Err_Hobby_LostFocus

Forms!frmPerson.sfmPersonHobby.Form.Refresh

Exit_Hobby_LostFocus:
Exit Sub
Err_Hobby_LostFocus:
MsgBox Err.Description
Resume Exit_Hobby_LostFocus
End Sub

It works fine when frmPerson is open but generates a 'can't find form' error message when frmPerson is closed. How would I add a condition to say 'Refresh sfmPersonHobby if frmPerson is open; otherwise forget about it'?

[slave138]

It worked fine for me. Here's your DB with only that command added to the LostFocus events of the two text boxes.

[slave138]

To answer your later question, use something like:

If CurrentProject.AllForms(formname).IsLoaded = True Then...

[Remster]

It worked fine for me. Here's your DB with only that command added to the LostFocus events of the two text boxes.

And for me now. I'm blowed if I can work out what I was doing wrong this morning. Thank you.

[Remster]

To answer your later question, use something like:

If CurrentProject.AllForms(formname).IsLoaded = True Then...

I couldn't get this to work at first either, but now it works perfectly. It seems to make a difference if instead of editing your old code you cut it out and start again. I wouldn't have a clue why.

Thank you again. That's it from me at least until next week!

[Remster]

Man, I'm having this problem again! What can prevent lists from requerying?

[RuralGuy]

Have you already imported the db into a fresh, empty db and see if the problem follows? You can cause corruption if the debugger is runnig and you edit code. The import leaves the corruption behind. You can also try a /decompile.