Form-Subform Strange interaction problem

[gg80]

I have a form with two subforms. Parent is sourced to a query and both subs are sourced to a table. Each sub has same Parent-Child data link. OnLoad code on the parent filters one sub alphabetically A_H and other J_Z, (“H” is actually a field on the Parent, controlled by the user). The majorButtons on the parent actuate code transfer and import data from excel.



All works well in A2007, but bombs out in 2003 with “not Working” message whenever code is triggered. One trigger for error was any refresh statement-I removed those. In A2003 just clicking record Refresh from the menu causes the “--Not Working" message.

If I remove the filters from the two subforms, or remove the parent-child link from one of the forms, all works well. The only way I can get the code to work is to close the form with the first line of code, then reopen it when code is finished.

Can someone tell me what is going on? I skipped from A2000 to A2007, so don’t have much experience with A2003.
I am using W7 for both computers. The A2003 computer is a 32 bit machine, the other is 64 bit.

[Bob Fitz]

Can you attach a copy in A2000 or A2003 format.

[gg80]

Thanks for looking at this. I have attached a simplified version to cut down on size. There is no code to crash, but clicking Update Records on the menu bar makes it crash, just like the full version.. In A2007 it works fine-no crash. I agree that most users don't use accdb, so, while I do my work in 2007 (a must on 64 bit machine I have been told), I save in A2003 for this and other users. This is only the second time in two years that I have run into a conflict with 2007 vs 2003.

Thanks again. I went through the attachment procedure, hope I did it right.

gg80

[Bob Fitz]

I can't be sure about the cause of the problem but I think the form is corrupt.
If you create a new form and paste all the controls into it and then set its Record Source property the new form can be refreshed.
Then delete the problem form and rename the new form.

[gg80]

That did it! I was really fooled by the fact that A2007 didn't "think" that it was corrupt. Next time I will be ready. I hate to think of the time I wasted getting around this. Thanks so much-I really appeciate you taking time to do this. I will now see if I can figure out how to mark as solved in this newer format.
gg80

[Bob Fitz]

I'm glad that you have a solution to your problem and pleased if I have been of some help.

I will now see if I can figure out how to mark as solved in this newer format.

I think this can be done using "Thread Tools" at the top of the page. I think that clicking on the star at the bottom left of this post and adding to my reputation also marks it as solved, but I'm not sure.

[gg80]

Hi-As it turns out, it was only a partial fix. On the final form the subforms have a control that calculates the cost for each item chosen. As soon as I create that control, form crashes with on refresh command in A20003, not 2007. I tried it on another computer with A2003 and does same.

I am attaching example with offending control with yellow background.

If you can take another look, much appreciated. This is driving me crazy.

George

[Bob Fitz]

I don’t understand the need to refresh. However, I think I have found the problem and a solution to it.

The problem seems to be that A2003 doesn’t like the refresh when there is a filter applied to the subforms. I have no idea why, but have a way to overcome it.

Do not make a call to your sub procedure “AdderSplit” in the On Load event of the main form.
Instead of binding the two subforms to a table, bind them to a SELECT statement with criteria that references the textbox (break2) that your sub procedure “AdderSplit” references.
Instead of setting the Control Source of “break2” to =”D”, set the Default Value to “D”. This would give you more flexibility, as the user could then change the value. To make this work the two subforms need to be re-queried in the After Update event of the text box.

In the attached db I have implemented these changes.

Just a couple of other thoughts:
I can’t see the need for a two field Primary key in table “T_MAIN_BUILD”. I think that just “idQuote” would be sufficient.

Table “T_MAIN_adder_DB” should have a Primary Key added.

There should be a one-many relationship set between “T_MAIN_BUILD” and “T_MAIN_adder_DB” using the “idQuote” field of both tables.

Create an “Items” table. Set a one-many relationship set between the Item table and table “T_MAIN_adder_DB”. Users could then use a combo box in the two subforms to select items. Not only would this be quicker, it would also stop spelling mistakes when entering data. You have one already (bag susage). This arrangement would also help at some point in the future when you need to query the db.

[gg80]

Thanks again. Realize that this is a simplified version of the whole project-the form source is really a query with five source tables. Also, there are other tables with their own possibly identical idquote number, so need another field to separate the type of sail. Some of the things are there because the user wants the layout (pulldowns vs. check box e.g. ).

I don't need to refresh from the menu, though I admit to overusing Refresh it in my code, bad habit. My thought was that whatever instability existed in other code statements would go away if I could get past the Refresh problem.

I don't understand the "Select" recommendation but will go over the attachment thoroughly, ASAP. The default for break2 is clever way to go, I was going to make a table and I very much appreciate the follow up and the amount of time you put into this. Thanks much.

[Bob Fitz]

I don't understand the "Select" recommendation but will go over the attachment thoroughly, ASAP. The default for break2 is clever way to go, I was going to make a table and I very much appreciate the follow up and the amount of time you put into this. Thanks much.

If I have been of some help then I am pleased. Please post back if you need any clarification on the SELECT statement.