Subforms stopped working

[Paul H]

I am posting this tonight to get a jumpstart on work tomorrow which is my drop dead deadline for finishing my project.



My master form is full of invoices and related information. I have three subforms. All were working and dismissed as finished a long time ago till today when it was pointed out to me that two of them were no longer pulling up related records. For one I have validated that there are related records for a particular invoice. I wrote a snap query joining the two table (tblInvoices and tblChecks) by invoice number and the related check data pulls up just like it should. These are the same fields I am using in the Master and Child section of the subform container. I've deleted the subform and recreated it. No luck. I'm exasperated because I've worked for months on this project and now at the very end I have to pull this rabbit out of the hat. I just won't feel right if I can't get this done. It's a matter of professional pride.

I'll be in bright and early Friday morning with answer to any questions you might have.

Update Friday 8:00 AM: I tried a different vendor and that account IS working with the same Parent / Child relationship. Still not sure why my query would work, but not my subform. I'll keep digging.

Update Friday 8:30 AM: I've isolated the problem down to a single vendor, but still confused, I am already running several queries that based on these common fields. These work for all vendors including the suspect vendor. Only the suspect Vendor's data does not show up in the subform. If I am allowed the time. I will create a sample database for you to look at. As a stopgap we are deleting all the data from that Vendor and reimporting and updating it. Maybe that will do the trick.

[ranman256]

As long as you have all key fields on both forms,
and links from master-child fields are there it should work. (that what its for!!)
I've never seen it not.

[aytee111]

I saw something similar once where the "Name" property of the subform on the main form was different to the "Source Object" property. Not sure why, but when I made them the same it solved the problem. (As in - the Source Object is called "subform name" and the Name is "Child1").

[Xipooo]

You may have to force the subforms to requery when the data is changed on the main form. I honestly prefer this way over trying to use the master-child relationship.

[Paul H]

Do you mean just send a requery command to it or something else like setting a filter on the subform.

[Xipooo]

Yeah but I assume you already did that. I usually filter the record source of the subform by a value from the main form... then just requery when I make changes. I just like having a bit more control over the whole thing.

If you're feeling really clever I like to make private recordset variables in the code behind the main form then set the recordset property of the subforms to those private recordsets. I have found this helpful in avoiding record locking issues or data changes that happen behind the form.

[Paul H]

I like the idea of setting the recordset dynamically on the subform, of course I wouldn't be considering this if the master/child thing was working. If I get a chance, I will try it. As of today I'm off the clock We'll see if they take me back.

[hapm]

What is the type and value of the join fields for the given vendor?