Recordset before doesn't exist (1 Viewer)

[monkeyman77]

Hello,

I am having some trouble with a line of code:

If rstBefore![Total] >= 8 Then
OTHours = rst![Total] - rstBefore![Total]

My issue arises when there is no previous recordset for the formula to look at.

How can I add that in?

I was hoping something like this would work but not having any luck:

If rstBefore![Total] IsNull or rstBefore![Total] >=8 Then
OTHours = rst![Total] - rstBefore![Total]

Any help is much appreciated.
-Steven

 

[bob fitz]

Perhaps you could test for rstBefore.BOF or rstBefore.EOF being true in which case the recordset has norecords

 

[monkeyman77]

Hi Bob,

I am not really good with VBA and am having trouble adding the rstBefore.BOF in to the code. Can you show how?

Function CalculateHours(TheDate As Date, TheName As String, Time_In As Date, DTApproved As Boolean, HourType As String)
  Dim dbs As DAO.Database, rst As DAO.Recordset, rstBefore As DAO.Recordset

  Set dbs = CurrentDb
  
  If HourType = "Reg Hours" Then
    Set rst = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
    & "FROM Employee_Log " _
    & "WHERE Time_In<= #" & Time_In & "# " _
    & "GROUP BY WorkDay, Employee_Name " _
    & "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")

    
    If rst![Total] > 8 Then
      Set rstBefore = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
      & "FROM Employee_Log " _
      & "WHERE Time_In< #" & Time_In & "# " _
      & "GROUP BY WorkDay, Employee_Name " _
      & "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")
      If rstBefore![Total] >= 8 Then
        OTHours = rst![Total] - rstBefore![Total]
      Else
        CalculateHours = (8 - rstBefore![Total])
        OTHours = rst![Total] - 8
      End If
    Else
      OTHours = 0
      CalculateHours = rst![Total]
    End If
  ElseIf HourType = "OT Hours" Then
    CalculateHours = OTHours
  ElseIf HourType = "DT Hours" And DTApproved Then
    Set rst = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
    & "FROM Employee_Log " _
    & "WHERE Time_In= #" & Time_In & "# " _
    & "GROUP BY WorkDay, Employee_Name " _
    & "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")
    CalculateHours = rst![Total]
  End If
End Function

 

[bob fitz]

Perhaps:

Function CalculateHours(TheDate As Date, TheName As String, Time_In As Date, DTApproved As Boolean, HourType As String)
  Dim dbs As DAO.Database, rst As DAO.Recordset, rstBefore As DAO.Recordset

  Set dbs = CurrentDb
  
  If HourType = "Reg Hours" Then
    Set rst = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
    & "FROM Employee_Log " _
    & "WHERE Time_In<= #" & Time_In & "# " _
    & "GROUP BY WorkDay, Employee_Name " _
    & "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")

  [COLOR="RoyalBlue"] If rst.BOF = True Then
     Msgbox"rst has no records"
   End If[/COLOR]
  
    If rst![Total] > 8 Then
      Set rstBefore = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
      & "FROM Employee_Log " _
      & "WHERE Time_In< #" & Time_In & "# " _
      & "GROUP BY WorkDay, Employee_Name " _
      & "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")
      If rstBefore![Total] >= 8 Then
        OTHours = rst![Total] - rstBefore![Total]
      Else
        CalculateHours = (8 - rstBefore![Total])
        OTHours = rst![Total] - 8
      End If
    Else
      OTHours = 0
      CalculateHours = rst![Total]
    End If
  ElseIf HourType = "OT Hours" Then
    CalculateHours = OTHours
  ElseIf HourType = "DT Hours" And DTApproved Then
    Set rst = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
    & "FROM Employee_Log " _
    & "WHERE Time_In= #" & Time_In & "# " _
    & "GROUP BY WorkDay, Employee_Name " _
    & "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")
    CalculateHours = rst![Total]
  End If
End Function

 

[Gasman]

Bob,

PMFJI, but my understanding is that it is the rstbefore recordset that is causing the problem?

Perhaps:

Function CalculateHours(TheDate As Date, TheName As String, Time_In As Date, DTApproved As Boolean, HourType As String)
  Dim dbs As DAO.Database, rst As DAO.Recordset, rstBefore As DAO.Recordset

  Set dbs = CurrentDb
  
  If HourType = "Reg Hours" Then
    Set rst = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
    & "FROM Employee_Log " _
    & "WHERE Time_In<= #" & Time_In & "# " _
    & "GROUP BY WorkDay, Employee_Name " _
    & "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")

  [COLOR="RoyalBlue"] If rst.BOF = True Then
     Msgbox"rst has no records"
   End If[/COLOR]
  
    If rst![Total] > 8 Then
      Set rstBefore = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
      & "FROM Employee_Log " _
      & "WHERE Time_In< #" & Time_In & "# " _
      & "GROUP BY WorkDay, Employee_Name " _
      & "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")
      If rstBefore![Total] >= 8 Then
        OTHours = rst![Total] - rstBefore![Total]
      Else
        CalculateHours = (8 - rstBefore![Total])
        OTHours = rst![Total] - 8
      End If
    Else
      OTHours = 0
      CalculateHours = rst![Total]
    End If
  ElseIf HourType = "OT Hours" Then
    CalculateHours = OTHours
  ElseIf HourType = "DT Hours" And DTApproved Then
    Set rst = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
    & "FROM Employee_Log " _
    & "WHERE Time_In= #" & Time_In & "# " _
    & "GROUP BY WorkDay, Employee_Name " _
    & "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")
    CalculateHours = rst![Total]
  End If
End Function

 

[monkeyman77]

Correct, the problem i have is there is no record set before the current one being entered. So having a message box pop up doesn't solve the issue.

I'm hoping to add the rst.BOF in to the if statement.

 

[monkeyman77]

If you take a look at the attached database and run the report for 16-ABC-3, you'll see the error i am receiving.

 

[Gasman]

Correct, the problem i have is there is no record set before the current one being entered. So having a message box pop up doesn't solve the issue.

I'm hoping to add the rst.BOF in to the if statement.

It is up to you to decide what you need to do if no records for rstBefore exist, but the logic test is the same, just for rstBefore.EOF

Add that to next line after you run your SQL for rstBefore.

Perhaps exit function if rstBefore.EOF is true or run the rest of the code if rstBefore.EOF is not true?

Also unless you *know* that there will always be rst records, then you should be testing for the case when there is not.

Edit: You *might* be able to get away with setting the relevant Total field to 0 if the recordset EOF is true?

 

[monkeyman77]

I'm really struggling with this. I don't understand why this doesn't work:

If rst.BOF = False And rstBefore![Total] >= 8 Then

If current record is not the first one and the previous record has over 8

 

[bob fitz]

Bob,

PMFJI, but my understanding is that it is the rstbefore recordset that is causing the problem?

You are correct. So code would be:

Function CalculateHours(TheDate As Date, TheName As String, Time_In As Date, DTApproved As Boolean, HourType As String)
  Dim dbs As DAO.Database, rst As DAO.Recordset, rstBefore As DAO.Recordset

  Set dbs = CurrentDb
  
  If HourType = "Reg Hours" Then
    Set rst = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
    & "FROM Employee_Log " _
    & "WHERE Time_In<= #" & Time_In & "# " _
    & "GROUP BY WorkDay, Employee_Name " _
    & "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")
  
    If rst![Total] > 8 Then
      Set rstBefore = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
      & "FROM Employee_Log " _
      & "WHERE Time_In< #" & Time_In & "# " _
      & "GROUP BY WorkDay, Employee_Name " _
      & "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")

 [COLOR="Red"]  If rstBefore.BOF = True Then
     Msgbox"rst has no records"
   End If[/COLOR]

      If rstBefore![Total] >= 8 Then
        OTHours = rst![Total] - rstBefore![Total]
      Else
        CalculateHours = (8 - rstBefore![Total])
        OTHours = rst![Total] - 8
      End If
    Else
      OTHours = 0
      CalculateHours = rst![Total]
    End If
  ElseIf HourType = "OT Hours" Then
    CalculateHours = OTHours
  ElseIf HourType = "DT Hours" And DTApproved Then
    Set rst = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
    & "FROM Employee_Log " _
    & "WHERE Time_In= #" & Time_In & "# " _
    & "GROUP BY WorkDay, Employee_Name " _
    & "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")
    CalculateHours = rst![Total]
  End If
End Function

 

[Gasman]

I'm really struggling with this. I don't understand why this doesn't work:

If rst.BOF = False And rstBefore![Total] >= 8 Then

If current record is not the first one and the previous record has over 8

Not sure what you are trying there.

If rst.BOF then you would not want to do anything I would have thought.
If rstBefore.BOF then you have no before values, so either set to zero or skip the code altogether.

Only you know the logic of what you need to do whether records exist or not? for either recordset.

However I do not think you should be combining tests like that as BOTH have to be true at the same time. I think you would need to test seperately.

So if not rst.BOF then you get rstBefore recordset. Then test to see if any records for that recordset.

 

[monkeyman77]

Basically, what i'm trying to do is evaluate the current day. If there is a previous record in the day, then i need to know if there are 8 hours in that record already. If so then current record hours will be all overtime.

If there is no previous record, then the first 8 hours of the current record set will be regular hours and the remaining will be overtime.

 

[static]

You have one function calculating three different things. Split them up. Your code will be easier to manage.


If you write the sql to a variable you can print it for debugging.

    If rst![Total] > 8 Then
    
        s = "SELECT Day, Name, Sum((DateDiff('n',[TimeIn],[TimeOut])/60)-[Lunch]) AS Total " _
      & "FROM EmployeeLog " _
      & "WHERE TimeIn< #" & TimeIn & "# and " _
      & " Day=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Name='" & TheName & "' " _
      & "GROUP BY Day, Name "
      
      Set rstBefore = dbs.OpenRecordset(s)
      
      If rstBefore.EOF And rstBefore.BOF Then
        Debug.Print s
      Else
        If rstBefore![Total] > 8 Then

prints

SELECT Day, Name, Sum((DateDiff('n',[TimeIn],[TimeOut])/60)-[Lunch]) AS Total FROM EmployeeLog 
WHERE TimeIn< #07:00:00# and  Day=#12/19/2016# AND Name='Steven' GROUP BY Day, Name

time in for #12/19/2016# is #07:00:00# so there is no matching record for <#07:00:00#

 

[Gasman]

Basically, what i'm trying to do is evaluate the current day. If there is a previous record in the day, then i need to know if there are 8 hours in that record already. If so then current record hours will be all overtime.

If there is no previous record, then the first 8 hours of the current record set will be regular hours and the remaining will be overtime.

Again, I do not know your logic re records, but....

Would it not be cleaner to just add previous total to current total and then do the math?

If so, then if rstBefore.BOF set that total to 0. Then just add as normal.
Less complicated than all the if statements

Also if rst.BOF is true, set that to zero and still look for rstBefore?
Can you just have a Before record on it's own?

Bob has shown you what you need to check. Where you put that code depends on your process.

HTH

 

[monkeyman77]

Appreciate all the help, unfortunately this is all a foreign language to me.... I think I may have to find another way.

 

[Gasman]

Basically, what i'm trying to do is evaluate the current day. If there is a previous record in the day, then i need to know if there are 8 hours in that record already. If so then current record hours will be all overtime.

If there is no previous record, then the first 8 hours of the current record set will be regular hours and the remaining will be overtime.

Take a step back and think I again.

I believe you need to get all the hours for a certain day.?
Then you check if the total is greater than 8. If so you subtract 8 to get Normal hours and the remainder is Overtime hours.? If not greater than 8, then all Normal hours.

So what happens when the person has not worked that day? There will be no records I would have thought?, so no rst recordset.

So the way I would approach it is
Get rst recordset. If BOF, exit routine with zero hours. If not BOF, possibly add Hours to an initialised variable. This will need to be initialised as the start of the sub/function if you go this route.
Now get rstBefore recordset. If it does not exist set rstbefore total to zero.

Now add rstBeforeTotal to the above variable if used or the rst Total if not.

Now check for the amount of hours in whatever variable you are using to hold the total, and process from there.

Things to possibly check. ?
Is there only one before record? Only one rst record?

Forget the code for now. Think on how you would need to do it on paper, then apply that logic to your code if the code appears a little daunting.

HTH

 

[The_Doc_Man]

My issue arises when there is no previous recordset for the formula to look at.

the problem i have is there is no record set before the current one being entered.

I'm going to make a leap of logic. (Probably because I'm part lemming and enjoy cliff diving, I guess).

You have two recordsets, "current" and "before" - but sometimes you don't have a "before" set, only a current set. To solve this problem, you have to adopt a scrupulous methodology of recordset handling. If you are going to use a recordset, always remember that when you are done you need to do TWO things.

<recset>.Close
Set <recset> = Nothing

THEN... when you are wondering about whether the "before" recordset is going to hose you to tears, the test is

If rstBefore Is Nothing Then
    GoTo AintThereNoMore
Else
    <do my thing>
End If

The test of "If rst.BOF AND rstBefore = <something>" is nonsensical in that to protect your code, YOU DON'T CARE what rst contains. You want to know if rstBefore is instantiated or not. Further, the AND doesn't help anyway because unless you happen to catch a case where the code optimizer will take a short-cut, you have to evaluate the "rstBefore" side of that statement if rst.BOF is TRUE, but you are implying that this is exactly the condition in which you DON'T have an "rstBefore" to test. (I have to admit that your explanation is convoluted and therefore a bit hard to follow, so I might have misunderstood some of your logic.)