Calculated control on subform does not use current record

[StefanScheidegger]

I have quite an urgent issue which I am trying to solve for some time now.

I have a main form and a subform. The subform includes the subform fields "January", "February", ... "December". These fields are the record source of the subform.

I would like to include a control (text box) to the subform (preferrably in the detail section, may in the footer) which gets its value from a query. Please keep in mind that the query is not part of the record source and cannot also not be since the recordset would be unupdatable if the field was part of the record source.

How can I add this text box to the subform and link the showed value to the value in the query?

Please note also, that the query should refer to the actual record in the detail section of the subform (if the actual record in the subform is record 23, the control on the subform should show the respective query amount of record 23).

Thanks a lot for your help. I appreciate your comments.

Stefan

 

[seaport]

I suggest putting that "textbox" in another subform -formC.
Let's call the main form formA, the subform you had formB.

Then you have two options. First, you can put formC in the formB as a subform within the subform. Second, you can put formC in formA, and create an invisible control linking formB and formC. (the control source of the invisible control is from formB, and the control source of formC is linked to that invisible control).

Hope this helps.

seaport

 

[StefanScheidegger]

Hi Seaport

That's a wonderful idea and I go for it. However, I did not somehow get it how to do this invisible control in the newly created subform C. I put a text box in form C and but as control source:

=[Formulare]![ProductDetails]![ProductID] (MS Access is configurated in German on my computer)

in english somewhat: =[Forms]![ProductDetails]![ProductID]

anyhow the text box on form C shows #Name. Could it be the problem that the subform B and C are both continuous forms?

Thanks very much for your help.

Stefan

 

[seaport]

Sorry that I should have make it clearer.

The invisible control should be in formA (the main form). If the [ProductDetails] form is the formB, it would be reasonable for me that you got the error. Since formB is a subform, you can not reference it directly. The way to reference a control in subform is:

[Forms]![FormA].form![ProductDetails].form![ProductID]

You may try linking formB and formC directly without the invisible control in formA. Make the control source of formC as:

"select * from [QureyName] where productid=" & _
[Forms]![FormA].form![ProductDetails].form![ProductID]

See whether it works.

Seaport