1. Registered User
Join Date
Jan 2004
Posts
44

I've run across a seemingly minor problem. I have a form that contains 11 combo boxes that corepsond to a table. once i pick an item from the combo box three box to the right are populated: Cost to Complete, Cost to Date, and Total Cost.
What I am trying to accomplish is having a set of additional boxes down at the bottom act as "Total" boxes for all of the selected programs. I have it set to total all of the boxes up and produce a total, however for some reason I am beinng forced to choose a program form each of the combo boxes before a total will appear. Problem is I do not necessarily want to pick from all 11 programs each time.

I tried setting the default value to 0 for all of the boxes that could contain numbers, but this didn't work.

Is it going to be necessary to write code that tells all 3 boxes in a row that if the combo box is empty, then they should equal zero? That would solve the problem for the "Totals", but it seems like the long way around. I would think there is something that I can do with the "Total" boxes themselves, particularly the control source, that will solve the issue more quickly.

2. Registered User
Join Date
Sep 2003
Location
T.O.
Posts
326
How have you written your total calculation? Have you considered using Nz() with 0 as the default there instead?

3. Registered User
Join Date
Jan 2004
Posts
44
All I had done was tell it to add the value from all of the text boxes in the column.

What does Nz() represent?

4. Registered User
Join Date
Sep 2003
Location
T.O.
Posts
326
Nz() converts a null into something else you might care about.

Nz(value [,value if null])

Total = Nz([Combo0], 0) + Nz([Combo1], 0) + Nz([Combo2],0) + ...

This way if one of your combo boxes aren't filled in, you have a null, if you have a null, Nz makes it a zero. Access can't add 1 + Null.

HTH.

5. Registered User
Join Date
Jan 2004
Posts
44
Kinda, I may have to mull over it some. However, I only have one combo per row.

A B C D
A B C D
A B C D
_____

X Y Z

A are my combo boxes, and bcd are the fields that will be filled, if and only if, a program is picked from the combo box. so perhaps i should instead have

Total = Nz(ComboX, 0) in the B C and D fields as the default value? Or is that not possible?

6. Registered User
Join Date
Sep 2003
Location
T.O.
Posts
326
If you're not worried about rounding errors, maybe you can do a "row total" in a hidden box, per the set of combo boxes you care about in a given set, then do a total of that for your report which you show.

7. Registered User
Join Date
Jan 2004
Posts
44
Well we're not dealing with pennies, so I wouldn't see rounding being a problem. However, I'm not sure how a row total would help me. I want column totals.

I'm not looking for F, which is what I think your solution would lead to.

A B C D - E
A B C D - E
A B C D - E
___________F

8. Registered User
Join Date
Jan 2004
Posts
44
Well the form I was working on is now on hold since I'm not sure we are going to use that. However, I am not working on a form that use the scroll function at the bottom of the form via the arrows. I'll use a sports team metaphore that will hoepfully help.

When you scroll through the form you pick from Team A, B, C...etc. Below that there is one row of fields for budget years (let's call it row x). Meanwhile there are tables for both "free agency money" and "contract money" - each table has rows that are associated with a team. What I need to happen is for the row on the main sheet to add the values from the "free agency money" and contract" money tables, but only for that team, and only for the year that matches the field in row x.

If I'm not being clear please let me know as I'd like to clarify.

Btw, I already tried as the field =FormA!Field01 + FormB!Field01, but I believe the problem is the form does not know what row to access on FormA & B to get the information from?

 - almost forgot, the fields in the row I am trying to populate gives me the error #Name?

9. Registered User
Join Date
Jan 2004
Posts
44
Alrighty, still having a problem here.

Right now I have a column that has 3 rows, so I have 3 boxes that I am trying to add. However, if each box contains 2, I get 222 as my answer, but I am in search of 6!

I have tried as many permutations of Sum, (), [], =, and + as I can think of and still no progress besides #Error and #Name?, that is if you want to call that progress...

10. Registered User
Join Date
Jan 2004
Posts
44
I seem to have made some progress, however I'm not really sure why, and was wondering if somebody could clear this up.

Originally I was trying to populate my popup for with data froma query, which contains the exact same information as "Table A". As I mentioned ealier in the post this method did not work.

However, when I used the expression builder instead, and used the fields from "Table A" directly the addition/sum worked.

Can anybody tell me why this is? Does it have something to do with how Access stores data and/or temporarily?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•