Best place for code (1 Viewer)

[lightray]

Hi again, This might be a simple answer, but I have tried several event properties, the last in the OnCurrent of the main Form. But most of the calcs work most of the time, and occassionally I get an 'Error 2001'. I have a couple of years VBAing, so still a teenager?? I am curious as to wether I am placing the calcs in the right event. I really only want them to do the calcs when the leave tab is selected (see enclosed image) and/or if a new employee record is navigated to. I will also attach code of calcs. If it needs to go into the Tab control what is the best event?
NB pink fields on form are visible=No and for troubleshooting

Coding:
' Run Calcs here for Leave other than Sick Leave.
If IsNull(EmpStartDate) Then
DaysWorked = 0
Else
DaysWorked = DateDiff("d", EmpStartDate, Now)
End If
' Calc number of days between Employee Start Date and Now

TotalLeaveAlloc = DaysWorked * (Me!AnnualLeaveDue / 365)
' Divide Allocated days by 365 and multiply by number of days worked

If IsNull(DSum("[DaysTaken]", "[qryLeaveRecords]", "[LeaveType]<>'Sick' And [tblEmployees]![EmployeeID] = [tblLeaveRecord]![EmployeeID]")) Then
TotLeaveRecorded = 0
Else
TotLeaveRecorded = DSum("[DaysTaken]", "[qryLeaveRecords]", "[LeaveType]<>'Sick' And [tblEmployees]![EmployeeID] = [tblLeaveRecord]![EmployeeID]")
End If
' Calculates the number of leave days taken from leave records in database based on qryLeaveRecords
' Used IsNull to check if there are no records found - set calc result to zero

Me.Taken01 = TotLeaveRecorded 'visible option = no
' Display this on screen

If IsNull(Me.LeaveAccrued) Then
LeaveBalance = TotalLeaveAlloc - TotLeaveRecorded
Else
LeaveBalance = TotalLeaveAlloc - (TotLeaveRecorded + Me.LeaveAccrued)
End If
' Check if there is any Accrued Leave [reduces the need to load historical Leave Records] and subtract them

Me.Bal01 = LeaveBalance
' Display Balance

' Run Calcs for Sick Leave here.
If IsNull(DSum("[DaysTaken]", "[qrySickLeaveRecs]", "[tblEmployees]![EmployeeID] = [tblLeaveRecord]![EmployeeID]")) Then
TotSLeaveRecorded = 0
Else
TotSLeaveRecorded = DSum("[DaysTaken]", "[qrySickLeaveRecs]", "[tblEmployees]![EmployeeID] = [tblLeaveRecord]![EmployeeID]")
End If
' Calculates the number of leave days taken from leave records in database based on qrySickLeaveRecs
' Used IsNull to check if there are no records found - set calc result to zero

Me.Taken02 = TotSLeaveRecorded 'visible option = no
' Display this on screen

Me.Bal02 = Me.SickLeaveDue - TotSLeaveRecorded
' Display Sick Leave Balance

thanks in advance ...

 

[lightray]

Well, I think it's all better off in the OnCurrent event after all, and solve the 2001. There were problems with the subform, so I have deleted it and re-created. So far everythings running smoothly, so it's on to putting the screen version into a report.

Thanks to a previous post about refrencing the form values in the report (as long as the form is open)

 

[Pat Hartman]

If you do the calculations in a query, you can use the query as the RecordSource for both the form and the report. This will minimize future maintenance. BTW, domain functions should not be used in queries. Create aggregate queries and join to them with left joins. If any columns might be null, use the Nz() function to convert the null to zero to avoid null results.

 

[lightray]

Thanks Pat, a query would be an ideal solution, and solve another form I'm trying to design. I'm a bit stunned at the prospect of trying to put a query like this together.
Hope you'll keep an eye on the thread. lightray

 

[lightray]

Well that was easy ...

I can't believe how easy that was! Thanks Pat for showing me outside the square I hadn't considered that I could use a query in this way.

Enclosed is a screen dump of the final query solution.

qryLeaveAg_Dif solved the Diff calc
qryLeaveAg_OL solved the Dsum for Leave<>'Sick' and Error 2001 Phew!
qryLeaveAg_SL solved the Dsum for Sick Leave

All other + - calcs handled in the text boxes on report and form.

If there was an even simpler way to put this together, Pat, I would appreciate your further input.

Thanks again

 

[lightray]

Oh! something else I was wondering about. Pat, If I use this query layout on an updateable form, does a requery on the main query, requery all the (sub)queries within?

 

[Pat Hartman]

Yes it does.

 

[lightray]

Tu Meke! [Awesome!]

 

[lightray]

Just another Question on this thread, with all the linked queries, does this make the form unable to update any of the static data?

Or you just can't update Aggregate queries - nature of the beast?