Why did table data suddenly stop displaying in form?

[Jennifer F]

Access 2007: I have a DB where I have customers that have multiple sites (one-to-many relationship), and the sites have multiple systems (another one-to-many). Up until today, the relationships have worked fine and data has appeared as expected on each of the corresponding forms. There is a button on the customers form that opens the site form, and displays the sites that are linked to that customer. There is also a button on the [Sites] form that opens the [Systems] form and displays the systems that are linked to that site. It has all worked fine until now... Suddenly today when I click my button on the sites form to open the Systems form, none of the systems data appears, even though I know it's in the table. I've checked the data in the table and everything seems in order. The linking field (SiteID) is correct for the site and the corresponding systems, but for some reason they do not appear on the Systems FORM. Again, all the data is correct in the tables and even the queries work. Any reason why they wouldn't appear on the form, and when I try to use the "Find" function to search for a record, the Find function doesn't work because it says:
*the fields are "controls;
*the fields have no data; or
*There are no fields to search.

Thanks for any help with my mystery!

[RuralGuy]

Try *importing* your FrontEnd into a fresh, new db and see if the problem follows. http://www.btabdevelopment.com/ts/impnew

[Jennifer F]

I tried importing the old DB into a new one 2 different ways: (1) I tried it by importing the structure AND the data - didn't work; and (2) I tried it by importing the structure only and tried test data. It still is not working. When I get to the Systems form, it is not showing data. I find it really weird that the form accepts the data, but if I refresh the page it disappears and says there are no records. But the Systems form has a subform based on a query that is designed to show all the types of systems that are associated with that site. The query works and the subform shows the correct systems. Still no clue why it won't show up in the main Systems form, and why it worked seamlessly up until recently.

[RuralGuy]

What you describe can also be caused by setting the DataEntry property of the MainForm to TRUE.

[Jennifer F]

Thanks again for the quick response... I've made sure that the DataEntry property was set to NO for both my original DB main Systems form, and for the new imported DB Systems form. It didn't seem to make a difference. I've even tried building the form again from scratch. Again, the queries and subforms work; but not the main form. For example, even though in my tables I can see that I have roughly 8 systems for one site, and it lists the 8 systems in the subform, the mainform displays 0 records for that site.

[RuralGuy]

How are you trying to display the count of records in the SubForm on the MainForm?

[Jennifer F]

Here is a picture of how the subform displays the systems. In the Systems Table, the systems are shown accurately, as they do in the subform. The data and records DO exist; however they aren't showing here on my main form. I've quadruple-checked that the form is based on the correct table and I don't recall changing anything before the form stopped showing the data.

[RuralGuy]

Show a picture of the form is design view so we can tell what are SubForms, please.

[Jennifer F]

Here is an image of the form in Design view. It is a terrible picture but you can see 2 subforms: the subform on the left lists dates when the inspections are due, and the subform on the right shows all of the systems that are assigned to the site. The list is populating correctly.

[RuralGuy]

So in your picture in post #7 there should be data in the SubForm on the left AND the MainForm?

[TheSmileyCoder]

I once accidentally changed the "Display When" property of the Detail Area of a form to "Print Only".

The whole detail section simply stopped showing in my form. Like you I checked my Queries, I trieded copying/importing the form, and (of course) none of it helped.

Maybe thats what's gotten you?

[Rabbit]

How big is the file? Is it 2 gigs? That's the limit of an Access database.

[Jennifer F]

RuralGuy --> There is the main [Systems] form, with 2 subforms on it. The left subform is based on a table called Cycles and is linked to the Systems table by [SystemID]. For each system there can be multiple cycles (dates). The subform on the right is based on a query that shows a list of the systems (system name only) that are assigned to the site. You can see from the first picture that the query and that subform are still working. The left subform [Cycles] seems to be in the same boat as the main form [Systems]. It is not displaying the data either, which makes sense to me, since the cycle/date data is tied into the data on the main form.

TheSmileyOne --> Unfortunately, no... I checked the Visible property and it's set to ALWAYS.

Rabbit --> The DB is 5,024 KB so I'm safe for now.

Thanks everyone for your input. All ideas are welcome as this problem is really stumping me.

[RuralGuy]

I'm out of guesses unless you can post your db so we can play with it. Remove any sensitive data of course.

[Jennifer F]

I'm posting the database that I imported into the new DB, so it only has test data in it. All the structure and the behavior is the same. Thank you!

[RuralGuy]

Put a TextBox on the MainForm bound to the SystemID field of the RecordSource and you will see what is happening.

[Jennifer F]

I added the text box. I guess I'm not recognizing what I need to see. Can you explain what's happening with that text box that could be causing the data not to show? When I tested the data again, it looks like when I open the [Systems] page, the SystemID textbox says (New) as if I'm going to add a new record, even when there are already 3 systems assigned to the site. When I try to use the [Jump to System] combo box to go to one of the existing systems, it tells me that I can't use the find/replace function. If I start to add a new system, then the system ID shows up, which is what I'd expect. What am I missing?

[RuralGuy]

Maybe I just don't know how to use your forms yet. The left hand SubForm will have no records until there is a valid SystemID in the new textbox on the MainForm. I expected there to be records in that SubForm when we got there.

[Jennifer F]

You start off on the Customers form. Click the "View Customer Sites" button to get to the first Site form. Then click the "View/Add Systems Details for this site" button on that form. When you're at the Systems form, choose a system from the [System] combo box, choose a [Frequency] and maybe put some test data in another field or two. Then tab or click down in the left-hand subform and choose a [Cycle Type] and put any date in the inspection date field. You can put another line of cycle type and inspection date in, just to get a couple of [Cycle] records in there. If you then press the "refresh" button that I've added to the page, the system that you just entered will show up on the right-hand subform, since you've not assigned that system to the site. It still looks like everything is OK. Now close the form, which will take you back to the [Site] form. Now click on the "View/Add Systems Details for this Site" button again. You're back at the Systems form, and all the systems that you added are listed on the right-hand subform, but none of the data appears on the main form. In addition, the "count" that I have listed above the navigation buttons will say "System 1 of 0 for this site" when I know there is at least one in existence because we just added it, and we can see it in the right-hand subform.

[RuralGuy]

I have to go to several meeting now but I'll check in later. I really cannot follow the logic of your forms yet, sorry.

[RuralGuy]

In the WhereCondition argument of the Sites Form
"View/Add Systems Details for this Site" button,
change: [Forms]![Sites].[SiteID]=[Forms]![Systems].[SiteID]
to: [SiteID]=[SiteID]
...and see if that now works as you want.

[RuralGuy]

Did that make any sense?