Hi,
when you loop over a recordset with Do While Not rs.EOF.
How can you check what is the last record and whats in it?
I need to know this because i have an invalid use of null when i loop over a recordset to get every year to calculate hours per year.
When the error occurs he points out the line where he wants to do the DSUM of the hours of each year. When the year is beond the last year thats in the table, he puts up the error message.
Here's my code:
when you loop over a recordset with Do While Not rs.EOF.
How can you check what is the last record and whats in it?
I need to know this because i have an invalid use of null when i loop over a recordset to get every year to calculate hours per year.
When the error occurs he points out the line where he wants to do the DSUM of the hours of each year. When the year is beond the last year thats in the table, he puts up the error message.
Here's my code:
Code:
Private Sub BtnNieuweOpleidingsuren_Click()
DoCmd.RunCommand acCmdSaveRecord
MsgBox "De gegevens zijn opgeslaan"
Dim yearr As Integer
Dim nextYear As Integer
Dim landmeterID As String
Dim SQL As String
Dim SQL1 As String
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim i As Integer
Dim currentYear As Integer
Dim hours_followed As Integer
Dim exemption_this_year As Integer
Dim total_hours_this_year As Integer
Dim transaction As Integer
Dim total_hours_next_year As Integer
Dim exemption_next_year As Integer
Dim result As Integer
Dim Jaar As Integer
Dim verplichtJaar As Integer
LanIDTxt.SetFocus
landmeterID = LanIDTxt.Text
Jaar = year(Me.startdatumTxt)
SQL = "Select Id_jaar from dbo_Lan_verplichtejaaropleiding where Id_landmeter = '" & landmeterID & "' and Id_jaar >=" & Jaar & " order by Id_jaar asc"
Set rs = CurrentDb.OpenRecordset(SQL)
Do While Not rs.EOF
If (rs.EOF = True) Then
Else
yearr = rs!Id_jaar
'Get the date from this year
hours_followed = DSum("uren", "dbo_Lan_opleiding", "Id_landmeter ='" & landmeterID & "' and Year(datumopleiding) = " & yearr & "")
exemption_this_year = DLookup("Urenvrijgesteld", "dbo_Lan_vrijstelling", "Id_landmeter = '" & landmeterID & "' and Jaar = " & yearr & "")
total_hours_this_year = DLookup("Uren", "dbo_Lan_verplichttevolgen", "Jaar = " & yearr & "")
'Calculate the amount of hours to transfer
transaction = total_hours_this_year - exemption_this_year - hours_followed
'Get the data from next year
nextYear = yearr + 1
total_hours_next_year = DLookup("Uren", "dbo_Lan_verplichttevolgen", "Jaar = " & nextYear & "")
exemption_next_year = DLookup("Urenvrijgesteld", "dbo_Lan_vrijstelling", "Id_landmeter = '" & landmeterID & "' and Jaar = " & nextYear & "")
If (transaction < 0) Then
result = total_hours_next_year - exemption_next_year + transaction
Else
MsgBox "De Landmeter heeft te weinig uren gevolgd!"
End If
CurrentDb.Execute "update dbo_Lan_verplichtejaaropleiding set te_volgen_uren =" & result & " where Id_jaar =" & nextYear & " and Id_landmeter = '" & landmeterID & "'"
Debug.Print yearr; hours_followed; exemption_this_year; total_hours_this_year; transaction
End If
rs.MoveNext
Loop
'End If
rs.Close
Forms![MainForm]![OpleidingOverzichtList].Requery
'Forms![MainForm]![OpleidingOverzichtList].Form.Requery
'[Forms]![MainForm]![OpleidingOverzichtList].Form.Requery
End Sub
.
