How to Dynamically Expand Detail Section of a Report?

[Celestz]

Ok, I have made a report that contains unbound controls(take note unbounded, since I generate the info from queries).

I have a sample function like the following that moves the controls down as I add content.

Private Function MoveTotals()
    Me!txtTotalDeductionTitle.Top = Me!txtTotalDeductionTitle.Top + moveValue
    Me!txtTotalDeductions.Top = Me!txtTotalDeductions.Top + moveValue
    Me!txtSubTotal.Top = Me!txtSubTotal.Top + moveValue
    Me!txtSubTotalTitle.Top = Me!txtSubTotalTitle.Top + moveValue
    Me!txtVatTitle.Top = Me!txtVatTitle.Top + moveValue
    Me!txtVatTotal.Top = Me!txtVatTotal.Top + moveValue
    Me!txtGrandTotalTitle.Top = Me!txtGrandTotalTitle.Top + moveValue
    Me!txtGrandTotal.Top = Me!txtGrandTotal.Top + moveValue
End Function

Now I'm receiving a lot of errors.

1st error was "The Control or Subform Control is too large for this Location"

so I tried to add a code like this.

Private Function MoveTotals()
    [COLOR="Red"]Me!Detail.Height = Me!Detail.Height + moveValue[/COLOR]
    Me!txtTotalDeductionTitle.Top = Me!txtTotalDeductionTitle.Top + moveValue
    Me!txtTotalDeductions.Top = Me!txtTotalDeductions.Top + moveValue
    Me!txtSubTotal.Top = Me!txtSubTotal.Top + moveValue
    Me!txtSubTotalTitle.Top = Me!txtSubTotalTitle.Top + moveValue
    Me!txtVatTitle.Top = Me!txtVatTitle.Top + moveValue
    Me!txtVatTotal.Top = Me!txtVatTotal.Top + moveValue
    Me!txtGrandTotalTitle.Top = Me!txtGrandTotalTitle.Top + moveValue
    Me!txtGrandTotal.Top = Me!txtGrandTotal.Top + moveValue
End Function

Since basically the movement of the controls are equal.

Then I get the following error:

"Microsoft Office Access can't find the field 'Detail' referred to in your expression."

Can anyone help me with this? I've seen some that explains I need to normalize the DB but that's for bounded controls? Correct me if i'm wrong.

Btw the function above is called inside Detail_Format event.

Also, my canShrink and canGrow property is set to yes.

 

[pbaldy]

I'm not clear on what this means: "unbounded, since I generate the info from queries". Most of us use queries as the source of reports, but simply have the report bound to the query. Fields from the query would be in the detail section, textboxes for totals would be in the report footer. No code or moving of controls required.

 

[Celestz]

Thanks for replying and thanks for the tip, will post back here for results since I use DB Calls on Format Events to Handle the data.

 

[Celestz]

And I'm back, so I got my report working, now I need to ask if there is a way to get a Time Range between 2 Time Fields.

i.e. I have 2 fields in my table named Time-In and Time-Out.

Say I have a record with the time-in at 10am and Time-Out at 2am(Midnight Time Out)

Is there a way I can get the total hours the 2 fields have intersected during the timeframe 10pm-6am(i.e. in my sample, the 2 fields would overlap a total of 4 hours, from 10pm to 2am).

Thanks

 

[pbaldy]

I'm having trouble visualizing the situation, but perhaps a function that you pass the times to and it uses the later of "time in" vs 10pm, and the earlier of "time out" and 6am.

 

[gemma-the-husky]

because you get times modulo 24hrs you would have to do something like

if starttime> endtime then 
  timediff = 24hrs - (starttime-endtime)
else
  timediff=starttime - endtime
end if

 

[Celestz]

Thanks for the feedback guys! I'll try this 1st thing monday when work resumes.

 

[Celestz]

because you get times modulo 24hrs you would have to do something like

if starttime> endtime then 
  timediff = 24hrs - (starttime-endtime)
else
  timediff=starttime - endtime
end if

Thanks for this tip Gemma, I think I have the working code for now, and I'm gonna post it here so other people can refer to it if they need it.

This is custom fit for my project so edit this to your preference, also correct me if you think anything is wrong.

Cheers


Night Differential Code: Assuming Night Differential Coverage is 10pm-6am, case to case.

Private Function GetNDHrs(ByVal LogINTime As Date, ByVal LogOutTime As Date) As Date

Dim TempIN As Date
Dim TempOut As Date
Dim LoginGreater10pm As Boolean
Dim LoginLess10pmLogoutBet12am10am As Boolean
Dim LogOutGreater10pm As Boolean
Dim LogOutBetween12am6am As Boolean
Dim LogOutBeyond6am As Boolean

'Time In Considerations!
LoginGreater10pm = LogINTime > #10:00:00 PM#
LoginLess10pmLogoutBet12am10am = LogINTime < #10:00:00 PM# And (LogOutTime > #10:00:00 PM# Or (LogOutTime >= #12:00:00 AM# And LogOutTime <= #10:00:00 AM#))
LogOutBeyond6am = LogOutTime < #10:00:00 AM#
'Time Out Considerations!
LogOutGreater10pm = LogOutTime > #10:00:00 PM#
LogOutBetween12am6am = LogOutTime >= #12:00:00 AM# And LogOutTime <= #6:00:00 AM#

'Time In Filtering
If LoginGreater10pm Then
    TempIN = LogINTime
ElseIf LoginLess10pmLogoutBet12am10am Then
    TempIN = #10:00:00 PM#
Else: TempIN = #12:00:00 AM#
End If

MsgBox TempIN

MsgBox LoginGreater10pm


'Time Out Filtering
If LogOutGreater10pm Or LogOutBetween12am6am Then
    TempOut = LogOutTime
ElseIf LogOutBeyond6am Then
    TempOut = #6:00:00 AM#
Else: TempOut = #12:00:00 AM#
End If

MsgBox TempOut

If TempIN > TempOut Then
    GetNDHrs = (TempOut - TempIN) + 1
Else
    GetNDHrs = (TempOut - TempIN)
End If

End Function

 

[vbaInet]

You use rather long variable names, e.g.

LoginLess10pmLogoutBet12am10am

 

[vbaInet]

archibald753 - spam reported

 

[Celestz]

You use rather long variable names, e.g.

LoginLess10pmLogoutBet12am10am

I am aware of that and I should avoid it imo ><, anyway I encountered something.

This is a new problem, although I'm not sure if there's already a solution here for this.

So, I have an unbounded textbox Field in my header and I designated it for the worded total of a computation.

The computation takes place on the on format, and I wanna ask if I can reflect it to the textbox field on the header. I already have the worded function, and I tested it with a message box command

MsgBox Me!txtWordedTotal

And it output's the passed worded value I sent, problem is when I view the report page it is not reflected visually. Is there a way around this?