Checking the last record in a loop over a Recordset

[wim1981]

can you place the adjusted code?

This is the 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
Dim hours_followed_valid As Integer
Dim total_hours_next_year_valid As Integer
Dim exemption_next_year_valid 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
    
      
   
  
    'Get the date from this year
    yearr = rs!Id_jaar
    hours_followed_valid = DCount("uren", "dbo_Lan_opleiding", "Id_landmeter ='" & landmeterID & "' and Year(datumopleiding) = " & yearr & "")
    Debug.Print hours_followed_valid
    If (hours_followed_valid > 0) Then

    hours_followed = DSum("uren", "dbo_Lan_opleiding", "Id_landmeter ='" & landmeterID & "' and Year(datumopleiding) = " & yearr & "")
    End If
    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_valid = DCount("Uren", "dbo_Lan_verplichttevolgen", "Jaar = " & nextYear & "")
      If (total_hours_next_year_valid > 0) Then
      total_hours_next_year = DLookup("Uren", "dbo_Lan_verplichttevolgen", "Jaar = " & nextYear & "")
      End If
      exemption_next_year_valid = DCount("Urenvrijgesteld", "dbo_Lan_vrijstelling", "Id_landmeter = '" & landmeterID & "' and Jaar = " & nextYear & "")
      If (exemption_next_year_valid > 0) Then
      exemption_next_year = DLookup("Urenvrijgesteld", "dbo_Lan_vrijstelling", "Id_landmeter = '" & landmeterID & "' and Jaar = " & nextYear & "")
      End If
      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

 

[fvdb]

     If (hours_followed_valid > 0) Then
        hours_followed = DSum("uren", "dbo_Lan_opleiding", "Id_landmeter ='" & landmeterID & "' and Year(datumopleiding) = " & yearr & "")
    Else
        hours_followed = 0
    End If

Because the next years hours_followed_valid = 0 and you not set hours_followed to 0 it uses the 28 from 2015

 

[wim1981]

     If (hours_followed_valid > 0) Then
        hours_followed = DSum("uren", "dbo_Lan_opleiding", "Id_landmeter ='" & landmeterID & "' and Year(datumopleiding) = " & yearr & "")
    Else
        hours_followed = 0
    End If

Because the next years hours_followed_valid = 0 and you not set hours_followed to 0 it uses the 28 from 2015

And i then need to do this also with the other DCounts?

That don't help.

 

[fvdb]

Try this one. For dlookup values you can use Nz function so if it not finds a value it will return 0.

 Do While Not rs.EOF
    'Get the date from this year
    yearr = rs!Id_jaar
    hours_followed_valid = DCount("uren", "dbo_Lan_opleiding", "Id_landmeter ='" & landmeterID & "' and Year(datumopleiding) = " & yearr & "")
    'check if there are registred hours
    If (hours_followed_valid > 0) Then
        hours_followed = DSum("uren", "dbo_Lan_opleiding", "Id_landmeter ='" & landmeterID & "' and Year(datumopleiding) = " & yearr & "")
    Else
        hours_follewed = 0
    End If
    
    'Get values for hours exemption
    exemption_this_year = Nz(DLookup("Urenvrijgesteld", "dbo_Lan_vrijstelling", "Id_landmeter = '" & landmeterID & "' and Jaar = " & yearr & ""), 0)
    
    'Set values for hours obligated to follow
    total_hours_this_year = Nz(DLookup("Uren", "dbo_Lan_verplichttevolgen", "Jaar = " & yearr & ""), 0)
      
    '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
    
    'check if hours obligated is there
    total_hours_next_year_valid = DCount("Uren", "dbo_Lan_verplichttevolgen", "Jaar = " & nextYear & "")
    
    'Determine hours to follow
    If (total_hours_next_year_valid > 0) Then
        total_hours_next_year = DLookup("Uren", "dbo_Lan_verplichttevolgen", "Jaar = " & nextYear & "")
    Else
        total_hours_next_year = 0 'or the minimum they have to follow every year
    End If
    
    exemption_next_year = Nz(DLookup("Urenvrijgesteld", "dbo_Lan_vrijstelling", "Id_landmeter = '" & landmeterID & "' and Jaar = " & nextYear & ""), 0)
    
    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 & "'"
     
    rs.MoveNext
    
Loop