Total from subform on main form (2000)

Topic

New Reply

OK. This has got to be something simple, but I’ve tried everything I can think of or find in my books or sample DBs for the last three evenings and can’t get it. I’m used to databases that keep track of people – this one keeps track of money.

I have a subform. It is linked to the main form by TransactionID. All I want is a field (TransTotal – unbound) on the main form (F_Transactions) that will display the total of the amounts (DetAmount) from the subform (F_TransactionDetailsSub) for that transaction. Seems like it should be simple – but I’m completely stumped. At one point I had it so it almost worked – but it was always one entry behind the subform. That was several experiments ago, and I’m completely back to square one now.

Can someone point me in the right direction or refer me to something that will explain how to do this?

-cynthia

Reply | Quote

Replies

Place a text box in the footer of the subform with Control Source =Sum([DetAmount]). This will display the sum of the amounts for the records in the subform.

If you prefer to display the sum in the main form, you still need the text box in the footer of the subform; let’s say you named it txtSumOfDetAmount. You can hide it by setting its Visible property to No.
In the main form, place another text box with Control Source =[F_TransactionDetailsSub].[Form]![txtSumOfDetAmount]
Here, you must use the name of the subform as a control on the main form; this is not necessarily the same as the name of the subform in the database window.

Reply | Quote

Thanks Hans,
I had tried nearly what you suggest, and just to be sure I moved the field on the subform from the header (where I had it) to the footer, and I renamed it to match the name you used in your post so I could cut and paste from the post and eliminate the possibility of a typo. At one point I was getting a number in the txt field on the subform (before I renamed it), but now all I’m getting is #name in both of them (subform and main form). I am beginning to suspect that the database may be corrupted because when I tried to zip it to post for you to look at, I had problems with that too! Fortunately there is not much to this database yet, so I’m going to start it from scratch this weekend. If it still doesn’t work, I will post again.

One question. Last night in some miscellaneous reading on the boards, I found and downloaded some tips for databases that someone had made for their students. One tip was “Never run your database from your A: drive”. I have been running this database off a zip disk. Is this bad practice and could it cause corruption?

Thanks,
-cynthia

Reply | Quote

Cynthia,

If you suspect corruption, you can try

• Compact and Repair (in Tools/Database Utilities).
• Decompile – do a search for decompile in this forum if you don’t know what that is.
• Create a new blank database and import all database objects from the suspect database.
  [/list]Good luck!

  The rationale behind the warning about the A: drive (probably from SteveH‘s Golden Access Rules) is that Access (like many programs) needs a certain amount of free disk space to function correctly. On a 1.44 MB floppy disk, you run into problems quite soon; a Zip disk has a much larger capacity, so provided it is not almost full, you should be OK. For best performance, however, I recommend copying the database to hard disk, and work from there.

Reply | Quote

Thank you for the explanation about running from disk.

I had already both compacted and created a new empty database and imported objects, but I need to learn about the decompiling, so will do that this weekend as well. And I’ll keep my fingers crossed! At this point I’ve got significantly more time into this particular project than its worth, but I can’t stand to be stumped by something this simple.

-cynthia

Reply | Quote

It works now! The part I was originally missing was the part you supplied to put as the datasource in the field on the mainform. The reason it didn’t work when I followed your instructions was that in my blearyness of having tried so many things so many times, somewhere in there I had deleted the = from in front of the Sum on the subform . Thanks for your help. It’s now working as I wanted it to – and now I know about not running it from disk!

-cynthia

Reply | Quote