loop through each detail record in report

[nickjar2]

Maybe someone can help with this.

I have asked a similar question earlier on, but no repsonse.

What is the best way to loop through each record in the detail, to insert values on the fly?
Example, if Joe Bloggs has ordered 5 items, how can I easily insert a seperate value for each of the 5 items. So I would see Joe Bloggs in the group header, and in the detail, each of the 5 'ordered items' would have a different value against it.

Cheers!

Nick (Everton Rool OK!)

 

[THWatson]

Nick
What value are you wanting to put by the items? An incrementing number?

Joe Bloggs
1. widgets
2. balloons
3. grapes
4. bananas
5. apples

Or is the value placed by each item being populated from some other field?

Tom

 

[nickjar2]

Hi Tom,

Cheers for responding.

It checks each line, and checks whether it is standby or not. If it is standby, depending on who Joe bloggs is, will determine what value is displayed.

Example, if joe bloggs is on standy by for saturday or sunday, then if he works for a certain business unit , then his standby might be £15. If is a weekday, his standyby will only be £8.
The next page, may display data for fred bloggs, who works for a different business unit, therefore his standby for saturday or sunday may be £25, and his weekday rate maybe £13.

Then a total will be displayed in the footer, summing up all these £8 and £15 etc

Does that make sense???

Cheers,

Nick (Everton Rool OK!)

 

[THWatson]

Nick
So, if I understand correctly, at least 2 issues here are interdependent. The first is the business unit. The second is the standby, with one of two values, either a weekend or a weekday.

I'm not clear where the report is picking up the business unit and standby values from, but assuming that the business unit value can be only 1, and the standby can be only 1 of 2 values, then it should be fairly easy to do.

example...
Joe Bloggs works for Primary Business Unit. His weekday standby is 8, his weekend standby is 15.
Your report shows his name and Primary Business Unit with no difficulty. The only variable then is whether it's a weekday or weekend standby, and from somewhere there has to be an indication as to whether the item being listed is weekday or weekend.
You could then have an unbound text box which has as its control source the following structure...
=IIf([Standby] = weekday, 8,15)

This structure would check for the standby to determine if it was weekend or weekday. If weekday, it would put 8 in the unbound text box, and if not 8 would put 15.

So Joe Bloggs' report would show...
Business Unit Primary
Item 1 Standby Weekday 8
Item 2 Standby Weekend 15

Are we getting anywhere closer?

Tom

 

[nickjar2]

>This structure would check for the standby to determine if >it was weekend or weekday. If weekday, it would put 8 in >the unbound text box, and if not 8 would put 15

it would also need to check whether this record was for standby or not. Below is a little bit of the code that does these calculations (in the detail_format part of the report)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo err_DF

' IS employee on standby???
If Me.FirstOfStandby Like "Y*" Then
' What resource/business type
Select Case Me.ResourceType

Case "A", "Z"
If Weekday(CDate(Me.WorksDate)) <> vbSaturday And Weekday(CDate(Me.WorksDate)) <> vbSunday Then
Me.SBP1 = 8
stbp = stbp + 8
Else
Me.SBP1 = 15
stbp = stbp + 15
End If

Case &quot;R&quot;, &quot;L&quot;, &quot;J&quot;, &quot;I&quot;
If Weekday(CDate(Me.WorksDate)) = vbSaturday Or Weekday(CDate(Me.WorksDate)) = vbSunday Then
Me.SBP1 = 20
stbp = stbp + 20
Else
Me.SBP1 = 10
stbp = stbp + 10
End If

End Select

Else
Me.SBP1 = &quot;N/A&quot;
End If


Exit Sub

err_DF:
Resume Next

It will do this as many times as there are child records. For Joe Bloggs, if he had worked for the 7 days (eg Saturday 17 Jan to Friday 23 Jan) his record on the report may look like:

WorksDate Day Terms SB SBP Status Ext

17/01/2004 Sat A N N/A ED

18/01/2004 Sun A N N/A ND

19/01/2004 Mon A N N/A ND

20/01/2004 Tue A N N/A ND

21/01/2004 Wed A N N/A ND

22/01/2004 Thu A Y 8 ND

23/01/2004 Fri A Y 8 ND

Standby £24.00

The sum of the SBP column should be £16, but it says £24. SOrry there is a load of text to view.

Cheers

Nick (Everton Rool OK!)

 

[THWatson]

Nick
So is it the case that your formulas all give the correct results, and it is just the calculation of the STANDBY TOTAL that isn't working properly?

If that's the case where do you have the Standby Total located - in the Detail section, or in the Group Footer for Joe Bloggs? And what is your sum formula?

From looking at the above, it could be that you are trying to get a Total (sum structure) of text boxes that could either be a number (8 or 15) or N/A. This might confuse Access. Can you use the Nz function?
In other words, where you want the Standby Total to show, put the formula...
=Sum(Nz([SBP],0))

That would convert the N/A entries to 0, and the sum should work then.

Does this do what you want, or am I still misunderstanding?

Tom

 

[nickjar2]

There isn't an actual sum. What happens is this:

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo err_GF
Dim rsHT As Recordset


If Me.ResourceType = &quot;I&quot; Then
Me.txtShiftAllowance = Format(67.76, &quot;£0.00&quot
Else
Me.txtShiftAllowance = Format(0, &quot;£0.00&quot
End If

' The sum for SBP
Me.Text97 = Format(stbp, &quot;£0.00&quot
stbp = 0

What happens is, for some employees, we get the correct result, but for others we don't. Do u think the N/A maybe to blame then????
I will try to explain best I can what happens when I step through the code.
We come to Joe Bloggs, he has 7 days worth of data, so we would expect in the detail_format event to process these 7 days. (starting from Saturday and finishing at Friday, before moving onto the next employee) The strange thing is though, say we get to Wednesday, and a N/A is found, the loop starts again at Saturday. Say he is on standby for saturday, sunday,monday and tuesday, but there is an N/A for wednesday, this is exactly what happens: for sat and sun, stanby is 15, so 15+15=30. tues and wed are 8's (so 8+8=16), total so far =30+16=46. BUT, once it gets to wednesday, finds a N/A, it goes back to Saturday, and starts again, BUT still keeps the 46 in the variable, therfore, the variable now holds the 46 PLUS another 46,cause it does the calculation again. THE VERY STRANGE THING THO, this time when it reaches the N/A, it doesn't reloop, but coninues, as it should have done in the first place going onto Thirsday and Friday, THEN goes to the next employee?????????? Am I explaing properly, cause I feel like I am not.

Cheers Tom, I feel like I am going mad!!

Nick (Everton Rool OK!)

 

[THWatson]

Nick
I can well understand why you feel as if you are going mad (I've been there), but don't call for the white coats yet. There's gotta be a way.

The interesting thing is this extra loop, but only one, if an N/A shows up in Wednesday. I am wondering...can you just leave that blank, or put a 0, rather than an N/A? Could be worth a try.

You say there isn't an actual sum, but isn't that what your Me.Text97 is?

I'm going to have to be out for the next couple of hours, but will check back in when I return.

Worst case scenario, you could send me a small bit of your db, extracting any sensitive data, and I'd be willing to have a look.

Keep smiling.

Tom

 

[nickjar2]

lol, cheers buddy. I am off home in a little while myself, but will keep checking here.

The text97 just displays the value of the stbp variable. This holds data taken from the loops, but nothing happens if a N/A is found.

It isn;t just wedensdays tho, and also, I tried putting a 0 instead of a N/A, but to no avail.

Another weird thing is this: u can either log into the database as 'Office Manager', whereby u see all records, or log in as a user (depot). If I log in as a depot, then open the report, the code in the On_open checks to see whose logged in. Becasue I am logged in as a depot (say for example Wrexham), the code says FilterOn = True
Me.Filter = &quot;SupervisorID=&quot; & Forms!frmofficemanagermenu.txtUser

The report will only display records for employees who work at Wrexham. ALL CALCULATIONS are perfect!!!!
Log in as Office Manager, view the report, and all employees for Wrexham are messed up. The same report is used. Obvioulsy when logged in as Office Manager, the On_Open sees this, so says FilterOn=False, then we have the strange goings on (

The only other criteria is the report asks for a weekending date. If u enter 23 Jan 2004 (has to be a friday), the report will display data from the saturday before (17 Jan) up to and including the friday 23 Jan), providing the employee had worked all 7 days.

Don't u just hate taking over someone elses code

Cheers again Tom

Nick (Everton Rool OK!)

 

[THWatson]

Nick
From your last post, it appears to me that the problem has something to do with database security, optimistic and pessimistic locks and such. And I am not sufficiently up to speed with that to offer any direction in which to go.

I say this because if it works just perfectly depending on the log-in, and at other times go bump in the night, something's weird. It sounds as if the problem lies in the log-in, or record locking, or something like that.

I am wondering if you make a new post, which indicates the anomaly you outlined in the most recent post above, if you might get some help from people who are better versed at this end of it than I.

There has to be a solution! I'm sorry to have to bow out and leave you hanging, but I know my limitations.

If you don't get what you are looking for, you could always go to Woody's Office Portal,

http://www.wopr.com,

sign up there (it's free) and make a post in their Access area there.

Good luck, Nick.

Tom

 

[nickjar2]

Cheers Tom,

All that happens when u log on as a differnet user is a variable gets initialised. If the user logs in as 'Office Manager', the value 19 gets written to the variable, whereas the depot id gets written to the variable if u login as the depot (anything from 1 to 18). The database doesn't use any security whatsoever, just some code that updates a variable, and whenever any other code is executed, this variable is checked, to see what records should be viewed. )

Cheers,

Nick

Nick (Everton Rool OK!)

 

[THWatson]

Nick
Okay, I get your point that it hasn't to do with records locking. However, I'm at a loss to suggest anything, because I really can't get a grasp on what is causing the problem.

If you were located in Canada, I'd be tempted to think it was a cold weather thing, because we've had that up to our ears lately. However...

Again, Nick, good luck. Try a new post, starting with the most recent description of the problem. There are some experts out there. Surely somebody can figure it out.

Tom

 

[nickjar2]

lol, cheers Tom, I'll do that )

All the best buddy and cheers for taking the time to help me )

Nick

Nick (Everton Rool OK!)