0

I have a quite complex MS-Access database (2013) with a main form. These one contains 6 main subform lists (A). Each subform has it own single form (B) to allow user to edit each record.

Because I sometimes have error 3048: cannot open any more databases, I set all the subform (A) to snapshot as recommanded in the following post:

Getting Error 3048: Cannot open any more databases

It resolves my current issue (error 3048) but added a new one :

When I edit a record (on a single form (B) ) I wish to update the data on the main subform (A).

  • If I call refresh or recalc on A, nothing happens...
  • If I call requery on A, data update but I lose the current view/selection (what is not friendly user): If I was editing one record (on second page of the list for instance), the focus return automatically to the first record of the list.

Do you have any idea to have a user friendly behaviour and data resfreshing at the same time?

Thanks for your support.

Elen Glimois
  • 47
  • 5
  • I also tried : DoCmd.GoToRecord acDataForm, FormA.SubFormB, acGoTo, 7 but it return an error. – Elen Glimois Apr 10 '19 at 04:31
  • 1
    So subform and subsubform have the same data source? You are trying to emulate a split form? Not a design I've ever used. Returning to record after refresh/requery involves saving record ID to a global variable or TempVar or to an unbound textbox then referring to that object in VBA to use that ID. Review https://stackoverflow.com/questions/2426371/in-an-access-form-how-to-return-to-previous-record-after-requery – June7 Apr 10 '19 at 05:31
  • 1
    Consider a change to your form's overall design. Is it necessary to have all data on the subforms immediately available on the main form? Perhaps consider creating other pop-up / companion forms for some of the data. This won't address the particular problem you mention, but it could reduce the need for the special 'Snapshot' query configurations which lead to this undesired behavior. – C Perkins Apr 10 '19 at 05:51

1 Answers1

0

I finally remove half of subform in the main form. It resolve the issue of memory. Then I was able to put all the Recordsettype subform to dynamic instead of snaphot. Thanks C Perkins for your advise.

Elen Glimois
  • 47
  • 5