Refreshing a Subform after a record is added on another form

Question

I'm having issues refreshing a subform after a record is added in another form in access 2007-2010. It seems that could be very simple but I'm not sure where I'm failing.

I have three forms:

One that is the main user form called main_user,
another one where the user creates the purchasing requirements called tb_requirements
and another one that is linked to the tb_requirements that is called tb_records.

The main idea of this system is that the user creates general purchasing requirements with attachments in tb_requirements and then adds some detailed requirements to the general requirement form (tb_records).

This function is already working but when I'm trying to update/go back to the main user screen (main_user) with all the open and new requirements, it does not work. The main user form is using a subform of the table tb_requirements. So in the VBA of main_user, I wrote the following code:

Private Sub Form_Load()

   Dim sSQL as String

   sSQL = 'with the query I want to show in the screen

   Me.tb_requirements.Forms.RecordSource = sSQL

   Me.tb_requirements.Forms.Requery

End sub

I also tried with .Refresh and .Recalc and it is not working. Could you please help?

Form_Load only runs once when the form is first loaded. Your description implies that the main_user form is already loaded, then while it is loaded a record is added on another form and you want the already-loaded main_user to refresh, correct? In that case, Form_Load will not run again. — C Perkins
Thanks @CPerkins for your prompt feedback. Yes, that is what I'm trying to tdo but since the Form_Load event will not run again, now I am trying with the Form_Open and Form_Current, but they are not refreshing the result with the new added record. Is there any other operation I need to perform before requerying the form? — JHin84

wazz wazz · Accepted Answer · 2017-05-17T10:23:31

activate might be what you need. it's like getting the focus back (IIRC) on the main form.

another approach is to open 'secondary' forms in dialog mode. pseudocode:

- start working in main_user
- open purchasing requirements -- but open it in *dialog mode*.
    -> code in main_user stops
        - work in purchasing requirements
        - close purchasing requirements
    -> code in main_user starts *from where it stopped*.
    -> refresh the subform now - me.sfrmName.form.requery

you can do it in 2 lines.

1. on button click, open purchasing requirements in dialog mode;
2. refresh subform.

between 1 and 2 you're working in purchasing requirements.

Refreshing a Subform after a record is added on another form

2 Answers