Clone Access 2000 SubForm to perform calculations

[pete61uk]

Further Access 2000 problem:

I need to complete a calculation based on recordsets in a bound subform. This, based on a user defined multiplication factor for a currency conversion.

The output needs be displayed in another, either cloned/copied subform (prefered) or unbound text box using another unbound text box for inserting the factor and a control to initiate the conversion. I have already tried both options in the subform footer.

Using the cloned subform, it also being bound, any changes are as you'd expect transfered to the parent form, but removing the clone's binding renders it unworkable in design view?

Using just unbound text boxes I found that I could complete the conversion but not with a control to initiate it. Further, when moving between recordsets the output remained fixed?

My apologies for the lengthy explaination, though an experienced reader may not need the fine detail it helps me to focus.

Any suggestions welcome.

[pete61uk]

Further to my last.

Have managed to get the currency conversion calcs working, though not (as yet) using a copy of the subform as a template for the unbound controls.

For any reader with a similar (novice) stage of "Access 2000" database knowledge, I put a new field, "CF", within the appropriate table and then in the query from which the subform is generated. I then put it and text boxes (hidden) within the subform footer. A straight forward link between these text boxes and their opposite recordset controls was then applied.

In this example Text22, is linked to the opposite subform field using the code, =[Field Name].

Performing the calculations, isolating the results from that displayed in the recordset, and therefore "Not to be stored", is completed on the main forms corresponding unbound text boxes using, for example, the following code placed within the "control source" of the text boxes Properties_Data section. This can be applied either manually or using the expression builder:

=[Subform Name].Form!Text22*[Subform Name].Form!CF

The inputting of "CF", the conversion factor, is at present straight into the CF text box on the subform (footer) but would be much better were the user isolated from directly inputting at this main control keeping the entire footer section invisible.

Assistance appreciated here:

Instead, it would be preferable were the function completed using a main form control/switch to both call up a user parameter input box and "onEnter" (if possible?) then make the currency conversion section and results visible on the main form?

Any suggestions really would be welcome!

[pete61uk]

Perhaps, not "quite" as I had envisioned but, for other "novices" out there:

Though the ultimate presentation changed (no subform) I put an additional field, "CF" (Conversion Factor) in the table holding numeric values and, within the original query used to generate my original form, added both it and as many calculated fields necessary to convert all numeric values (later to become visible within their own "Expression" text field) based on that inputted in "CF".

Then, instead of generating the form from within the forms design section, I saved the saved, "still open" query, as a "form". The resultant form is then a continuous list with each individual record available. NOTE: The calculated "Expression" fields are only visible from within the form and are not stored, only the "Conversion Factor" is!

That may not seem too important, however, if using the data to produce charts, it means that the charts are produced using only individual records as their source and not the "sum" of all records within the group! As you then advance through your records the chart displays values for each.

I hope this is of use?

Should an "Expert" take pity, assistance would be appreciated here:

I will be using varying versions of the same form, some with both fixed and user-defined parameters. This means that, where charts are used, the user-defined parameters have to be re-inserted once a form loads before the chart will.

Question: Can I (and if yes, how do I) use a macro to insert the relevant text from the form into the custom-dialog box/s and therefore remove the needless duplication of effort?

Thanking you in advance