problem Making a Subform Controlled by a Report Update After Data Entry

[BryanStrickland]

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!

[twinnyfo]

Hi, Bryan,

Welcome to Bytes! I'm an Air Force civilian, so I'll try to talk slower for you Army folks! ;-)

I think I understand much of what you are saying and trying to do, but some of the language you use is not quite Access-speak (which grows over time) and you may be referring to things incorrectly (or your DB may be structured improperly). Either way, we will try to help with both. And, keep in mind, that I'll be "flying blind," as your DB is not in front of me.

First, because it is a little unclear as to what all is supposed to happen, rather than jump right into a solution, I will try to clarify what I think I understand you to be saying and request additional information and clarification from you on a few things.

1. Concerning point 3 above:

Each is populated by a report which displays the information in its corresponding Subform.

It would be highly irregular to have an access "form" populated via a "report". Do you actually have a "report" that is providing data for a form? in Access-speak, a "Form" is an object that displays data and allows the user to add, change or manipulate data. A "Report" is an object that only displays information and is not typically designed for user manipulation. Reports are typically printed after they are displayed. Forms help the user navigate through the mountain of data in the project.

2. You seem to be having problems with the forms saving/updating data properly. To begin, just in case you were not aware, in MS Access, whenever you are within a table or a bound form, any time you move between fields, the data is always "saved" automatically, and unless you cancel the save, moving to another record will save the entire record. So, as a general rule, there is never a need to "save" a record manually. Sometimes this is preferred, but seldom is it easy, and this type of activity is really beyond the average database construction.

So, that being said, if your tables are properly related and your forms properly constructed, the data entered for each record "should" update automatically. You mention

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.

My thought is if the subforms are properly related to the main form through the Case ID, any time a record is updated or added, the record should be automatically related to the Case ID. In that case an

Me.Refresh

or

Me.Requery

should be all that is needed (for example if you were "counting" associated records. However, your description is a little unclear as to what you want to do here.

Based on what you mention in A) through C) above, it doesn't sound like you are doing anything so strange that your date does not refresh in the display of the Case Form. However, you may have to force a refresh of both subforms when you refresh the main form.

Additionally, just on the surface, it does not seem that your table structures are way out of whack. Again, if the forms are built properly, all should be working (which it appears it "works", but it just doesn't refresh and update the display?).

Hope this can help us work toward a solution for you.

[BryanStrickland]

Sir,

I greatly appreciate your reply. And I definitely am grateful for you talking slower in this context. The answers to your questions are:

1) Each of the two subforms are controlled by a corresponding report that I created, which in turn is linked to a query. For example, the Case_Subjects query scans the Case_Subjects table for all subjects associated with a particular case number. The report then displays the Last Name, First Name, and Middle Name. This information then appears on the main form (Case Details Form).

The only real reason that I adopted this approach is because I read a posting on one of the web sites that I used to conduct research on this project that someone else had done it this way. So it seemed like a good way to me to reduce the ability of users to manipulate the data if they aren't supposed to. Unfortunately, that posting, which I am no longer able to find, did not go into detail about how he made it work. If there are other ways that I simply don't know about, I am definitely open to them.

2. As far as the database's ability to save entered data properly, it is working as it should. For example, if I add a subject to a particular case number, I start at the main Case Detail Form. I then click the Add_Case_Subject button, which pops up a Subject Record form. The form is autofilled with the Case Number from the main Case Detail form. The user cannot change that number and cannot change the date/time that the record was made. After they complete the form, the user clicks a Save Record button, which Saves the Record, closes, and refocuses on the Main Case Detail form. At this point, the two subforms (or reports--whatever they should be called) should update and display, but they do not. However:

a) if you minimize the form and open the table associated with the case subject form, the data is there, exactly as it should be;

but

b) if you run the query at this point, the data that was just entered does not appear. This in turn means it doesn't display on the report, which in turn means that it doesn't appear on the main Case Detail form. (Note that if you run the query or the report at this point, they do return results for previously entered data that was associated with a particular case number--so I'm sure the query and reports work properly--when they work).

3) I have tried Me.Refresh and Me.Requery, which didn't work. Although a Refresh button does work, so long as the Case Subject form that you have entered was not the first record for a case subject associated with that particular Case Number. So I thought that maybe I needed to alter the language to be more specific to the subform, and I added the following to the main form's GotFocus event:

Me.Case_Targets.Form.Requery

Does this help explain my situation any? I'm sorry that the postings are kind of long and that I may not be explaining things very well.

I very much appreciate your help though--Air Force people are very much more technically inclined than we Army folk!

Best regards!

[twinnyfo]

Bryan,

Please bear with me, as point 1) above is particularly confusing:

Each of the two subforms are controlled by a corresponding report that I created, which in turn is linked to a query. For example, the Case_Subjects query scans the Case_Subjects table for all subjects associated with a particular case number. The report then displays the Last Name, First Name, and Middle Name. This information then appears on the main form (Case Details Form).

I still think you are mis-speaking, but I also think I understand what you are trying to say.

Your other points (I think) are addressed below, but using a different approach.

Please take a look at the attached Word Doc (I hope you like the Air Force Blue!), which has a visual of how your forms (and I use this term reservedly) "ought" to be structured. Note how the main form has the RecordSource of a Table (no filters or queries). I'm not saying that it can't have a query as a RecordSource, but by this setup, I want to demonstrate an important principle in relational databases.

When you create your Subjects Subform, note that the RecordSource is also a Table (no filters at this point). However, when you insert the subform into the main form, you establish the Master/Child relationship between the two forms (in fact, if your RecordSources and Forms are properly named and structured, MS Access may make some assumptions and establish this relationship for you). The same holds true for your Case History subform.

Why is this important? Well, when you move to a record in the CaseDetails form, the other two subforms will automatically filter out only the records associated with that Case ID! Additionially, when you are on a particular CaseDetail, whenever you add a record to either one of the subforms (at this point, no need for any pop-up forms), those records will be automatically associated with that CaseID.

Now, what should these forms look like? That is entirely up to you. Some people like to have a datasheet view for the subforms, or you could have a single form view that allows you to page through your subjects or history records. You could also experiment with a Split form, which would show both a datasheet view and a single form view on the same form. Those can be tricky, and, honestly, I don't have much experience with them, but I do know they are available for use.

The reason I explain this principle and this structure is because it precludes the need for any pop-ups and also precludes the need for a "save" button, as any time you complete a record, the data is already properly associated--simply clicking away from the record in question "saves" everything for you. This also precludes "requerying" the subforms, as they will always display all the records associated with that CaseID.

You can get fancy with these forms, too, if you want, and using a query for a RecordSource also works, as you can include various fields from other tables, as well. The key is the relationship between the three forms in question by use of the CaseID.

It sounds like you know enough about databases that this should all make sense.

Hope this hepps!

[BryanStrickland]

Sir,

Let me consider what you have told me. I think you raise some good points, and it sounds like I may have overcomplicated the database by adding layers that aren't necessary. I am going to attempt to double check a few things and alter a few thngs along the lines you suggest.

I will work it this weekend and see what happens. I will post more on Monday or so.

Have a great weekend, and thanks again!

[twinnyfo]

Take care, Bryan! Have a safe weekend yourself!