How Do I Make a Subform Controlled by Report (Which is Controlled by a Query) Update After Records Are Added?

Question

I recently took a duty position that requires me to track our administrative actions. Previously, the office had tracked them on an Excel spreadsheet, and the historical data was corrupted/missing. So I have built a database in Access 2010. I designed the database to display a main form that provides an overview of each action, but decided to require most of the data to be added or updated on specific data entry forms. My question relates to two subforms on the main form that I cannot get to update after data is entered into the tables. Here are specifics:

1) The main form is the case detail form, and each case has a unique case ID number that links most of the forms and tables.

2) The Case Detail Form has two subforms. One displays the names of the people involved in the case (this is the Subject Subform). The other displays the case history entries (this is the Case History Subform).

3) Both of the subforms are supposed to work the same way. Each is populated by a report which displays the information in its corresponding Subform. In turn, the reports underlying each Subform are based on a query that selects the records to display based in the case number.

4) New data for the subforms cannot be entered on the Case Details Form. Instead, the user can press a command button which launches a separate form that allows the user to enter either subject details or to enter a case history update. Once the user enters the data, he or she clicks A Save Record button, which saves the record and closes the form window. Everything seems to work fine up to this point.

5) However, I've now been working on this project for two weeks (as time permits), and I am still not able to make the two subforms update automatically. If it's a new case record, the user can make the Subforms update by using the navigation arrows to leave the main Case Details Form and then returning to it. If it's an established case record, a refresh button that I have added to the main Case Details form will cause the two subforms to update.

As I've tried to make this work, I've tried a number of approaches that I've found on various boards. Right now, I have:

A) The Subject data entry form has a Me.Requery statement in After Insert Event Procedure

B) The Subject data entry form has a SaveRecord and a CloseWindow command that are executed when the Save Record Button On Click event is triggered.

C) The main Case Detail Form has a executes a Me.Case_Subjects.Form.Requery when the GotFocus Event Procedure is executed.

I apologize for the lengthy question and explanation. I'm an Army officer and a little out of my depth with this. I would greatly appreciate any help anyone might be able to offer.

Best regards!

Answer 1

I'm not sure having understood everything but I try to give you some suggestions about what to try.

If I'm not wrong both form and subforms have the case ID as field so when you created the report you should have put them in "link" by the wizard (you can set it manually after but it's a bit more tricky).

Did you do this? This makes the form / subforms update when you navigate them.

Please note that there must be a relation between the underlaying tables to guarantee that the reports / forms moves together!

This applies to the query too, just remember to include the ID field int the queries.

If you don't want to see the ID (in case you use an autonumbering id) you can set it Not Visible.

In case you don't want to link the two tables with a relation there is another solution but it's a bit more tricky and, if it's not your case, I don't want to make confusion.

Let me know if you solved.

Bye

Answer 2

Firstly, the line Me.Requery should be on the After Update event, not the After Insert event. Secondly, you will then need to change Me.Requery to Me.[insertsubformname].Requery.

As the user above said, you will need to ensure that you have created your database relationships properly, however if you used the Insert Subform, it should've asked you what you wish to use as your link between the forms.