Subform Linking problem

[Chinde]

Hi

I'm having problems with a subform, I'm using access 2007 but the DB's format is 2003, running os Xp.

This is the problem, I have a main form which is set to be a single form and will auto open to Add New Record in this form I have placed a subform which is to show continuous records for the same department (I have cleared the master and child links and get the record set via code). On the sub form I have set a few buttons which will call different sets of SQL for the subforms recordset. 2 days ago this worked perfectly, today it has just stopped and will only show fields which have the same ID as the current record in the main form. the subform, When opened on it's own still works.
I have checked for the linked fields and they are still cleared, I have commented out all of my VBA on the main form, I have compacted and repaired the db and delected the subform and replace it on the page physically clearing the linked fields and it still does not work as it did 2 days ago.

I would be happy to believe that this is a simple error but at present I believe it to be the work of malicious spirits unknown.

Any help would be great, even a direction.

Any more info or detail please ask i am more than willing.

[ChipR]

Sometimes I get inexplicable behavior from my database, and a decompile will fix it. This set of steps may help: Recovering from Corruption

[Chinde]

Thanks for that, i've run all of the items in the article but still it is broken.

Any more anyone.

[ChipR]

You said you are using code to get the recordset for the subform, but then you said you commented out the code. What is setting the recordset now?

[Chinde]

commented out all of the code for the main form, which on load set the general record set on the subform, but the subform has a few buttons which reference some SQL to set the recordset for it ad hoc and like I said when the subform is open independent of the main form all works just fine.

Does this make sense??

[ChipR]

So the form is supposed to filter on Department but it's filtering on some ID?

[Chinde]

Yes That's it but the linked fields properties of the subform I have emptied, so I can set the link dynamically at a later point, at present I have limited the record set in SQL to emulate the link I want. It might be worth me mentioning that the fields on the subform are more or less the same as the fields on the mainform and i've just noticed that I do have some naming conflicts (which I will sort out) but this shouldn't cause problems for the link, should it??

[ChipR]

Just the confusion of the naming conflicts could cause you problems anywhere, so hopefully straightening those out will reveal something.

The subform's LinkMasterFields and LinkChildFields can only be set in Design View or during the form's Open event, according to the Developer Reference. If you want to filter the records on different fields according to events, you can just change the filter for the subform, or change the recordset itself. If necessary, we can take a look at the code that isn't working to do that.

[Chinde]

Thanks for your help but I think I'm going to have to strip the whole thing down and see if I sort it out that way, thank fully the DB is still in early development, just hit an early stumbling block.

Thanks again

[Chinde]

Thanks for the help ChipR, I've done some work on the MainForm and SubForm and in the process placed a button on the main form which just cleared the LinkData and the FilterData to make sure that they were clear, and i found that running the SQL followed by the ClearData code that all of the correct result turned up.

Going to migrate the buttons on the SubForm to the MainForm and see if that is the long term solution.

Thanks Again

[ChipR]

Good to hear that you got it working again. Always glad to help.