Forms and Subforms Problem

[desibabu90]

Here is the situation that is giving me trouble.

I have two forms with multiple subforms that are being referenced off one table. I need the user to enter data into subform1 of Form2. Then I want to sum those data and then show it in a field in subform1 of Form1. Does anyone have the proper syntax for this?

I had assumed that since all the data is in one table, I can easily just call out and sum the fields in both the subforms but unfortunately, I am not able to do it on the Form1 but it works flawlessly in Form2.

Please help!

[GinaWhipp]

Sounds like you need to Requery at the point that data is added. Have you tried that?

[desibabu90]

If I do a query then I will lose my ability to input data in the table because all my fields on the subforms are input fields besides the total field.

[rpeare]

I can not imagine a situation where you would want to have a form and subform based on the same table, can you post an example of your database.

[desibabu90]

I can not imagine a situation where you would want to have a form and subform based on the same table, can you post an example of your database.

my bad..I actually have other stuff on the main form that is pulling information from a different table. what i had meant to say was that all my subforms are referenced off one table.

[GinaWhipp]

I did not say use a query I said *Requery* the form after data input to allow the form to *catch-up* thereby enabling you to get the data you require. Are you not familar with *Me.Requery*?

[rpeare]

I don't see the difference in what you're saying but here's an example using dsum. There are better ways but this is the easiest.

[desibabu90]

I did not say use a query I said *Requery* the form after data input to allow the form to *catch-up* thereby enabling you to get the data you require. Are you not familar with *Me.Requery*?

hey no i am not familiar with a requery...where exactly would i use it? Do i need to create a separate button for the requery...

@rpeare

let me explain my setup of the database:

I have a query based on a table that is importing data from an oracle database so I cannot create any new fields or do anything with that table/query.

Form1 is based on this Query (Query1).
Then I have a blank table (Table1) with fields that I need the user to input.
I created subform1 on Form1 based on Table1.
I created another Form (Form2) based on Query1 that auto populates some fields that show up both the forms.
I then created another subform (subform2) based on Table1 where the user would put in more information. I then added up this data and shown it in a textbox (Total) in that subform.

What I want to do is now show the value of Total in a textbox that is located under subform1 in Form1. I am able to display the Total value in Form2 but cannot get it to show it in subform1.

Table1 has all the data and since subform1 is based on this table, I assumed that I can add all the fields like I did on subform2 and the values would show up, but they aren't.

I have also tried making a textbox on Form2 and displaying the Total value there and then tried to show that same value on subform1 but was not succesful in that either.

I hope this helps in explaining what I am trying to do and hopefully you can help.

[GinaWhipp]

The Me.Requery goes behind the form either in the After_Update event of a field or to do the entire form put it in the On_Current event of the form. It does not go in a field and is not a QUERY. This might help...

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

However, if your way of using DSum works then okay. I just refrain from using controls with calculations because it has the tendency to slow the loading of forms (and reports).

[rpeare]

Did you look at my example?

The dsum function will work regardless of where you place it, you would just have to force a requery or refresh whenever you add new data

which is simply a

forms!fromname!fieldname.refresh

or

forms!fromname!fieldname.requery

I don't know whether you'll have to use refresh or requery(I haven't tested it)

[desibabu90]

The Me.Requery goes behind the form either in the After_Update event of a field or to do the entire form put it in the On_Current event of the form. It does not go in a field and is not a QUERY. This might help...

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

However, if your way of using DSum works then okay. I just refrain from using controls with calculations because it has the tendency to slow the loading of forms (and reports).

I will try to give this a try and will let u know if it works..

thankx

[desibabu90]

Did you look at my example?

The dsum function will work regardless of where you place it, you would just have to force a requery or refresh whenever you add new data

which is simply a

forms!fromname!fieldname.refresh

or

forms!fromname!fieldname.requery

I don't know whether you'll have to use refresh or requery(I haven't tested it)

I did look at your example but in my subform the values are not all under one field. I have 10 different fields that I am adding together.
For example - all your data is in ChargeAmt and so you can use DSum on it but would I still be able to use a DSum in my case?

Unfortunately, I cannot post my database because the data is linked to a server here otherwise it would definitely be useful.

[GinaWhipp]

Okay, I am so confused...

What part are you up to? And what are your trying now? (Yes, I did see you example and stated why I don't use calculations on forms. Was there something else I was suppose to see?)

[rpeare]

Yes you can still use dsum

(assume for the sake of this example you could had 10 fields called chargeamt1 through chargeamt2)

=DSum("[chargeamt1]+[chargeamt2]+...+[chargeamt10]","Tbl_Charges","[personID] = 1")

And if you don't want to have a formula in a text box then you can do the summation behind the scenes and just populate the total in an unbound text box. Though how you would get a sum without a formula is beyond me.

One other thing you could try is, as you said you can get the total on your subform would be to just have any place where you want the total appear you just have a textbox where the CONTROL SOURCE property is set to:

=[Forms]![frm_people].[Frm_charges]![Chargetot]

where frm_people would be your main form
frm_charges would be the subform where the total is correctly displayed

[desibabu90]

Yes you can still use dsum

(assume for the sake of this example you could had 10 fields called chargeamt1 through chargeamt2)

=DSum("[chargeamt1]+[chargeamt2]+...+[chargeamt10]","Tbl_Charges","[personID] = 1")

And if you don't want to have a formula in a text box then you can do the summation behind the scenes and just populate the total in an unbound text box. Though how you would get a sum without a formula is beyond me.

One other thing you could try is, as you said you can get the total on your subform would be to just have any place where you want the total appear you just have a textbox where the CONTROL SOURCE property is set to:

=[Forms]![frm_people].[Frm_charges]![Chargetot]

where frm_people would be your main form
frm_charges would be the subform where the total is correctly displayed

okay..i tried both the methods didn't work for me.

I am attaching a sample database I made that is similar to the one I am working on.

I keep getting a #Name error when I try to fill in the value from subform2.