MS ACCESS --- Update everything through VBA

Question

How to update everything through vba? I want to update all open forms ...

Answer 1

The reason @CodeSlave's answer probably didn't do what you need is that you need a VBA method, not an update. Refresh will display the changes made to existing records, but only requery will display the newly added records.

Here is a shorter version of the code (put this in a module so that it can be called from any form):

Public Sub RequeryOpenForms() Dim f as Form For Each f In Access.Forms f.Requery Next End Sub 

NOTE. Unfortunately, requery has a side effect of losing the currently selected record. This can be especially unpleasant to use if there is a long list of records, as they may have to scroll a long way to find the record they previously watched.

Answer 2

How about this?

 Sub AllForms() Dim obj As AccessObject dim dbs As Object Dim i As Integer dim intFormCount as Integer Set dbs = Application.CurrentProject intFormCount = dbs.AllForms.Count - 1 For i = 0 To intFormCount If dbs.AllForms(i).isloaded = True Then dbs.AllForms(i).refresh End If Next End Sub 

Answer 3

In the comment above you say:

I would like the new record to just be added to the table, which will be available in this form when I return to it

You might want to explore the Activate event. But it bothers me to fill out the form if you don’t know that the entries have been added. I expect that if I need it (I never did this, in fact - my users know about Shift-F9, where it is relevant, but most of them do not need them), I would use the OnActivate event and check the current number of records and only request a form if the counter does not match the current set of records.

But then again, I find this a rather exotic affair. In general, this is probably an architectural mistake when too many forms sit around with an open set of records that you walk away from and then come back to. They should probably be completely closed and not open when you are away from them. This reduces the number of table descriptors, as well as the number of locks stored in the database on the rear panel, which can be a problem when properly managed.