[RESOLVED] calculated fields on each row in a report

[maxhadden]

I have VB 6.5, so hopefully this is the correct forum. If not please tell me which one is better to join.

I have a report with some VB code that executes on load. Basically it assigns values to two text boxes that act as "sums" (week1 and week2) on hours worked during the week dependent upon numbers/codes in each day box. So I have two weeks worth of hours and codes (14 boxes) and calculations for week1 and week2 populate the wk1 and wk2 textboxes. These 16 box rows repeat from the detail section on load.

The problem is the formula is only working on the last row. So the two sums (not straight sums but calculated formulae off the 14 numbers and codes) from the last row, repeat themselves for all the other rows as well. How can I do this a better way?

[Hack]

Welcome to the forums

You have the right forum site, just the wrong forum section.

VB 6.5 is actual VBA (Visual Basic for Applications) and is associated with a Microsoft Office product. Based on your question I'm guessing you are using Excel which is why I've moved your question from the VB.NET section to the Office Development section.

[maxhadden]

Its actually Access, sorry I neglected to put that. Is Office Dev still the right section?

[maxhadden]

Well its been a few days on my first thread here, with no replies. Was hoping for more activity! I ended up putting the VB code into functions, and having the textboxes call them. Thanks anyway!

[vbfbryce]

Is it doing what you need? I was going to ask you to clarify your requirement a little, but never got around to it!

[maxhadden]

Yes! Thank you! Basically I had a report that looked (for sake of argument) basically like this:

WEEK 1 M T W TH F SA SU WEEK 2 M T W TH F SA SU
39 8 8 8 8 7 0 0 40 8 8 8 8 8 0 0
39.5 8 8 8 7.5 8 0 0 45.5 8 8 8 8 8 0 5.5
32 8 *y* 8 8 8 *po* 0 16 8 0 *s* 0 8 0 0
40 8 8 8 8 8 0 0 34 8 8 8 8 2 0 0

32 24 32 31.5 31 0 0 32 24 24 24 26 0 5.5

(Hope this formats right when I send it), where Weeks 1, Week 2 and the bottom row sums are all calculated when the form loads. The bottom row was very easy, but I couldnt figure out how to calculate sideways for days for the week columns, mainly because the daily hrs values also had codes in them which needed additional VB code to reference their intended hourly value. So I ended up putting all this code into public functions, and then called the names of these functions from the textbox properties. Works perfect!

[westconn1]

Works perfect!

can't get better than that

thnx for posting solution, great if anyone finds when searching

pls mark thread resolved

[maxhadden]

Im sorry I dont see anywhere to mark this as resolved

[vbfbryce]

Should be under "Thread Tools" in the upper right corner