CODEWITHSUNDEEP
ms-accessvba
flyerwolf
flyerwolf

Reputation: 33

Requery a Sub Table in Access Using VBA

https://i.sstatic.net/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

Upvotes: 1

Views: 1349

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

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"

Upvotes: 0

Related Questions