calculate sum in subform footer

[jurekorla]

Hello

I have a problem with calculating sum of fields in the footer of subform

The subform consists of following 5 fields
Opis kolicina txtCena rabat CenaSpop

Opis is combobox (cbododatki), which takes values from another table (tbldodatki)
SELECT DISTINCT [IDdodatki], [Opis], [Cena] FROM tbldodatki ORDER BY [Opis];
Kolicina is enetered manually
Rabat is entered manually
Cena is calculated with =cbododatki.column(2)
Cenaspop is a calculation of [kolicina]*[txtCena]*(1-[rabat])

In such case i cant get Access to calculate the Sum in the footer of the subform

On the other hand, if i add an additional column to the source table (tblPodrobNaroc) of this subform (frmPodrobNaroc) where i enter Cena manually (instead of only being part of cbododatki), then all calculations are fine.

Which option do you reccomend, should i:
a) add to the source table of subform (tblPodrobNaroc) another column with values of Cena from tbldodatki (how is that done?), which would be filled in through cbododatki, and then perform needed calculations
b) call Cena in subform in such way that the Access recognizes it (not as cbododatki.column type) and performs the calculation

thank you in advance

Jure

 

[boerbende]

The comboboxes, are you sure you work with the values or can it be that you work with the ID of the searchtable?
Under Properties of the combo, check rowsource, and bound to.

 

[jurekorla]

yes i work with the ID, is that the reason?

 

[boerbende]

I think in your calculation the ID is used in stead of the value you see in the combo. This might be the reason that the calculations are wrong.
Do you use VBA too calculate the result? Maybe you can debug and see what value you use for the calculations?
Do you use the controlsource of a textfield to calculate the result? Add another textbox in your form and make the controlsource equal to your combobox. for example =[Combo306]. You will see that you get the ID in stead of the value which is visible in your combo

You can retrieve your "value" again by using a DLOOKUP function, based on the ID in the afterupdate event of your combobox. Do this in the Code builder.

 

[jurekorla]

Hello

i have checked the combobox the way you recommended and i m getting the ID saved into the base table of subform (tblPodrobNaroc)

I have a sample database where the Dlookup function is used in VBA as
(cbododatki Afterupdate), should i do something like that:

Dim strfilter as string
strfilter = "IDdodatki =" & Me!cbododatki
Me!txtCena = DLookup("Cena","tbldodatki",strfilter)

will that save the value of txtCena so that it will be available for calculation of the sum in footer?

 

[jurekorla]

Hello

I added another row in the source table of subform (tblPodrobNaroc), where the value txtCena is saved for each order. Now it works just fine, although now database has the same values in tbldodatki (CenaEnote) and tblpodrobnaroc (CenaEnote), i know that is not ideal - only ID fields should connect different tables, what is your opinion on that?

best regards

Jure Korla

 

[boerbende]

Just busy with reply when you replied again
1) I think the Dlookup wil work.
2) Why saving in the table? Just make an extra field in your form with the outcome of the DLookup

 

[jurekorla]

I tried to do that - i added an unbound field to form (named txtCena)
now when i enter (or change) any of the product (dodatki) through subform combo
all the txtCena in the subform change - i cant get different values (txtCena) for different products (IDdodatki)
If i change a single product (IDdodatki) , all the products get the price (Cena) from chosen product....hm...

 

[boerbende]

Yes, I tried it and saw that was not a good solution. Because it is not linked to a record, "all" records get the same information. Sorry, was a too quick answer

Is it possible to upload your database so I can have a look at it?

 

[jurekorla]

No problem, its in the attachment

 

[boerbende]

One new trial (just tried
Just a simple example with 1 search table and one data table

The record source of your subform is a query looking something like this

SELECT data.ID, searchtable.SValue
FROM search INNER JOIN data ON searchtable.ID = data.ID;

Place in the footer of your subform a textbox. Call this one testSum
Change under properties the control source to =Sum([Svalue]).
Now you have in the footer the sum and you can make it invisible if you want

Now to have it on the main form:
create again a textbox
Change the control source to
=[NameOfSubform].[Form]![TestSum]

So you don't use the fields of the subform, but the data from the query

 

[boerbende]

Hello out there in Slowenia? Your origin could be the problem

I could not get it work either, allthough you had done it correctly.
So there was something other wrong.
I just deleted all the boxes, keeping only the subforms, then it worked. So I more precisely deleted fields and it seemed to be the date field... I changed the name Datumnaročila to Datumnarocila and then your code worked...
So maybe the issue was you were using non English characters ?

So I found several #Name? issues in the form. Or is it just because I opened it on an English computer?

 

[jurekorla]

Hello

First let me thank you for taking so much time with this project

Regarding the language, having the Slovenian version of Windows, names of the fields with "č","š","ž" are not yet a problem. Nevertheless, i changed all the "naročila" fields to "narocila" (i sense some future problems here...you never change all, but better change now than later)

I was also thinking about calculating sum with query, but i dont know if i will still be able to enter data in the subform, if the subform is based on query? I understand that if you want to enter data in the (sub)form, it has to be based on some kind of table?

The database i sent you already has (doubled) Cena datafield in the table on which the subform is based, that solution is used already in some sample databases i have...as you can see it works just fine. I will also decrease its field size to minimum, to optimize the size of database.

The last trial is interesting (search table + data table), but i am entering data in both form and subform...

Have a nice day

Jure

 

[boerbende]

Hello

Correct me if I am wrong, but in your field Besedilo51 you link to the sum in the subform.
This was again not working until I upgrade to Access 2010 (AND changed the name of the datefield off course). I just import your whole database and it works, so it still looks to me as if somewhere in your database an error has occured which makes that your sum is not working correctly.
Maybe it works as well when you just import it in a new blank database. Could you make a new blank database and import all by menu, "external data", access import and import all of forms, tables, queries etc?

If that does not work I don't know any more, because here it seems to work.
Or I understand your problem wrongly

Ben

 

[jurekorla]

Hello

sorry for late reply but now all works fine. Sum is working correctly and transfer to form is ok too, seems the field names with "č" were the problem.

Thanks for all the help

Best regards

Jure