Hi everyone wondering if anyone can please help! I have attached a copy of my database so that you can see the code and what it should do. User 'ewardell' very very kindly modified my original database so that it has subforms and he also wrote a brilliant piece of code for the absence subform. You can see that on the absence form it says Start Date, End Date and 12 month total. What should happen is that Start Date should be the date of that employees last absence. End date should be 12 months previous to the Start Date. 12 month total should therefore be the total number of absence days taken from Start date to End Date. The calculation does work - however the start date for every employee is the same - this is because it is finding the date for the last absence in the entire database not just for that employee. So for example X's start date should be different to Z's. I hope this makes sense but I have no idea how to alter the code so that it finds the last absence for each employee - since I did't write it to start with!!! Can anybody help me with this? I'd be really grateful as its quite important that I get this done fairly soon :-(

In the OnCurrent event behind the form, change txtSQL to

txtSql = "SELECT Max(tblAbsence.AbsDate) AS AbsDate FROM tblAbsence where " & _
"tblAbsence.EmployeeId = " & Me.EmployeeID

This will select the last absence date by employee.

I have changed my code as you suggested so that it looks like this:


Private Sub Form_Current()
On Error GoTo Error_Form_Current
Dim adoFindRec As New ADODB.Recordset
Dim txtSql As String

'For getting Absences from the latest dated entry


txtSql = "SELECT Max(tblAbsence.AbsDate FROM tblAbsence where " & "tblAbsence.EmployeeId=" & Me.EmployeeID
adoFindRec.Open txtSql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
adoFindRec.MoveFirst
txtStartDate = adoFindRec![AbsDate]
txtEndDate = DateAdd("M", -12, txtStartDate)

If IsNull(Me.EmployeeID) Then
txtStartDate = Null
txtEndDate = Null
adoFindRec.Close
Exit Sub
End If


Me.txt12ATotal = Nz(DSum("AbsDays", "tblAbsence", "AbsDate Between #" & txtEndDate & "# and #" & txtStartDate & "# and EmployeeID = " & Me.EmployeeID), 0)
If Me.txt12ATotal = 0 Then
txtStartDate = Null
txtEndDate = Null

End If

adoFindRec.Close

Exit_Form_Current:
Exit Sub

Error_Form_Current:
MsgBox Err.Description
Resume Exit_Form_Current
End Sub


But I get the error message: Syntax error (missing operator) in query expression "max(tblAbsence.AbsDate FROM tblAbsence wheer tblAbsence = EmployeeID102 (0r 103, 104 - whichever employee record I go to).

I have probabaly done something really silly bus as I am completely useless with VBA I can't tell!!!

Thank you

You need the "as" bit in there -

txtSql = "SELECT Max(tblAbsence.AbsDate) AS AbsDate FROM tblAbsence where tblAbsence.EmployeeId = " & Me.EmployeeID

I've just tried this and it works. Copy and paste it from here.

Hello Nikki
Your txtSQL does not match the txtSQL that Aoh posted.

I downloaded a copy of your database and changed that txtSQL statement to match Aoh's post and it does indeed do what you requested.

Have another look at it.
HTH

Chris

Aha thanku everyone it works now! I knew I'd probably missed something silly out! :-)

Hi, Sorry I actually still have one other problem - some employees have never been absent (due to illness anyway) but when you go to an employee who has no absence records I get the error message Syntax error in date in query expression Absdate between ## and ## and EmployeeID = 102 (or whichever employee it ealtes to). Any ideas? sorry to be a pain!!!

Also just to add to that, it works for the eployee absneces that I input before I changed the code, but then when I go to a new employee now the message box appears, so I then input their absenses but the start date end date and 12 month totals are not calculated.!

For the first one, I rearranged the start of the code a bit:



For the second one, you just need to add a Me.requery after the absence is saved - this could be sfter the save line behind the save button, or on the afterupdate of one of the fields in the subform (in wchich case you'll need to specifiy which form you want to requery.

Hope this helps

Anne