Requery a Sub Table in Access Using VBA

Question

http://i.stack.imgur.com/NhBss.jpg

I have on the f3AgreeService form the Agreement itmes(parent) and Service details(child). I used a sub table (tService) rather than a sub form to show service info as I want to take advantage of the subdatasheet function that is available only to tables (the + to expand function. The subdatasheets can be linked to either facility/DHB info of the service)(1 to 1-many).

I want to be able to use a button (see facilities/see DHBs) to switch between two different sub data sheets for tService sub form. The code is as follows.

The problem is that sub data sheet will not automatically update, until you close and reopen the whole form. I could for sure close and open the form every time the user clicks. But it seems ugly. Is there a way just to requery or update the tService for the sub datasheet information to take effect?

Private Sub cmdDHBs_Click()
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
MyDB.TableDefs("tService").Properties("SubDataSheetName") = "Table.tServ_DHB"
MyDB.Close
Call RefreshTable 'How?
End Sub

Those I have tried and not worked: Forms!f3AgreeService.Refresh Forms!f3AgreeService.Recalc Forms!f3AgreeService.Query Forms!f3AgreeService.Repaint

If you intend to use this application in a multi-user environment or across a network, you are heaping up trouble for yourself : allenbrowne.com/bug-09.html — Fionnuala
Can you expalin why? I read the attached link but it is about setting subdatasheet property to auto. And what is the best way to update the linked tables? One by one? — flyerwolf
You are already experiencing problems. These are likely to get worse. You can use subforms or forms to update the related tables. — Fionnuala
I tried to just close and open the window and it seems to do the thing. I mean if I can update using subform, the table is no much difference? — flyerwolf

Fionnuala Fionnuala · Accepted Answer · 2012-03-20T10:19:10

If you really insist on doing this, and I do not recommend it, you can reload the source object of the subform after you have change the subdatasheet:

Me.MySubformName.SourceObject = "Table.tService"

Requery a Sub Table in Access Using VBA

1 Answers