Record Set - how to check all records

[Janet95]

Hi,
I'm having a problem with some code (record set) I wrote. I want to check for members of a group to see if they have been absent for 30, 60 and 90 consecutive meetings (not calendar days).

Basically a person will have a record in the db for everyday a meeting took place. Their record will either have a "1" for attended or "2" for absent in the Attended column.

The main table has the columns Absent30, Absent60 and Absent90. I want to put a value of 30, 60 or 90 in that column if the person was absent for 30, 60 and 90 consecutive meetings (not calendar days).
[COLOR=red yellow]
The code I wrote below works, but it only looks at the very first record/memmber .[/color]

It takes the ID form the very first record and checks the whole table only for that ID and says if that person was absent. [COLOR=red yellow]How can I get this to work for all members/ID in the table?[/color]

I created a continuous form and placed this code in the “On Open” event and the “On Current” event. I’ve been beating myself over the head for over a week now to get it work correctly. Any help would be tremendously appreciated.

Here is the code I wrote. It does a record set on a query I wrote to sort the table by most recent date.

Private Sub Form_Open(Cancel As Integer)
    'absent 90 consecutive days code
Dim intErrCnt As Integer
     
    Dim Absent90No As Integer
    Dim Absent90Yes As Integer
    Dim Absent90True As Integer
    
    
    
    Set dbAB = CurrentDb
    Set rstAbsent = dbAB.OpenRecordset("qryAbsent90")
    
    'here I get the error "cant assign value to object"
    Me.txtAbsent90.SetFocus
    Me.txtAbsent90.Text = "0"
    
   
   
    rstAbsent.MoveFirst
    
        Do While rstAbsent.EOF = False
      '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
      'check for attended = no
            If (YouthID = rstAbsent!YouthID And rstAbsent!Attended = 2) Then
            
            
            Absent90No = Absent90No + 1
            
            
                            'here I just put in the number 5 for testing (will replace later with 90)
                If Absent90No = 5 Then
                     Me.txtAbsent90.Value = 90
                     
                End If
            
                
            
            'to here
            Else
                      
           
                      
            End If
            
       '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            'check for attended = Yes
            If (YouthID = rstAbsent!YouthID And rstAbsent!Attended = 2) Then
            
            
            Absent90Yes = Absent90Yes + 1
            
            
            
                
            
            'to here
            Else
                      
           
                      
            End If
            
      '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
      
                'if total records checked = 90 then stop
                 Absent90True = Absent90No + Absent90Yes
                 
                  'here I just put in the number 10 for testing (will replace later with 90)
                 If Absent90True = 10 Then
                     
                     Exit Sub
                     
                 End If
            
            
            
        rstAbsent.MoveNext
        Loop



End Sub

Thanks Janet

 

[Zion7]

Text property is read only!
Not This
Me.txtAbsent90.SetFocus
Me.txtAbsent90.Text = "0"
But this
Me.txtAbsent90 = "0"

WHY STRING DATA for txtAbsent90?

Both your "IF" statements, are identical;
If (YouthID = rstAbsent!YouthID And rstAbsent!Attended = 2) Then
???

 

[Janet95]

Thanks Zion7,

I don't get the error any more for the txtAbsent90. That part works and I also fixed the if statement. I made a last min. change and forgot to switch one of them back to 1.

Here is the current code.

It still works only for one memmber id. [COLOR=red yellow] How can I get this to run for all record ID's in the database. [/color]

Private Sub Form_Current()
'absent 90 consecutive days code
Dim intErrCnt As Integer
     
    Dim Absent90No As Integer
    Dim Absent90Yes As Integer
    Dim Absent90True As Integer
    
    
    
    Set dbAB = CurrentDb
    Set rstAbsent = dbAB.OpenRecordset("qryAbsent90")
    
    Me.txtAbsent90.SetFocus
    Me.txtAbsent90.Text = "0"
    
   
   
    rstAbsent.MoveFirst
    
        Do While rstAbsent.EOF = False
      '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
      'check for attended = no
            If (YouthID = rstAbsent!YouthID And rstAbsent!Attended = 2) Then
            
            
            Absent90No = Absent90No + 1
            
            
                            'here I just put in the number 5 for testing (will replace later with 90)
                If Absent90No = 5 Then
                     Me.txtAbsent90.Value = 90
                     
                End If
            
                
            
            'to here
            Else
                      
           
                      
            End If
            
       '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            'check for attended = Yes
            If (YouthID = rstAbsent!YouthID And rstAbsent!Attended = 1) Then
            
            
            Absent90Yes = Absent90Yes + 1
            
            
            
                
            
            'to here
            Else
                      
           
                      
            End If
            
      '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
      
                'if total records checked = 90 then stop
                 Absent90True = Absent90No + Absent90Yes
                 
                  'here I just put in the number 10 for testing (will replace later with 90)
                 If Absent90True = 10 Then
                     
                     Exit Sub
                     
                 End If
            
            
            
        rstAbsent.MoveNext
        Loop



End Sub

Thanks
Janet

 

[JoeAtWork]

It still works only for one memmber id. How can I get this to run for all record ID's in the database.

Your code is nested in the following IF block, which appears to be for just one specific "YouthID":

If ([COLOR=red]YouthID = rstAbsent!YouthID[/color] And rstAbsent!Attended = 2) Then

 

[Zion7]

Joe hit the nail on the head!

If I understand your objective correctly,
just to update current record, try something like this


Private Sub Form_Current()
'absent 90 consecutive days code
Dim intErrCnt As Integer

Dim Absent90No As Integer
Dim Absent90Yes As Integer
Dim Absent90True As Integer


Absent90No = DCount("pkAbsent90ID","qryAbsent90, _
"YouthID = " & YouthID & _
" And Attended = 2")

If Absent90No = 90 Then
Me.txtAbsent90 = 90
End If

 

[Janet95]

Hi Zion7 and JoeAtWork,

Thanks for the feed back.

My objective is to update all records at the same time.

I'm sort of self taught at programming (which means amateur and probably doing things the hard way or wrong). "Your code is nested in the following IF block, which appears to be for just one specific "YouthID":" I know it's nested for just one specific "YouthID". How can I make it "un-nested"?


I have tried to add the code you suggested Zion7 and I receive an error: "Compile Error: List Separator or )"

This shows up on the YouthID in the added code.

Any ideas?





Thanks
Janet

 

[JoeAtWork]

In coding, it is usually best to figure out your logic first (the "algorithm"), then write code that matches this logic. One method I sometimes use is to write out the logic as pseudocode (as comments in the VBA code editor), then for each line of pseudocode there is generally one line of real code (sometimes it might be two or three lines, such as opening a database connection).

Now, after rereading your original post, I believe your goal is to simply find out the maximum number of consecutive absent days of a youth. Accordingly, my pseudo code would be something like

   'Set a MaxDaysInRowAbsent variable to zero
   'MaxDaysInRowAbsent = 0
   'Set a ConsecutiveDaysAbsent variable to zero
   'ConsecutiveDaysAbsent = 0

   'Create recordset of youth's attendance records, sorted by date
   'Let's call it rsAttendance

   'Start looping through rsAttendance
      'Is this an "absent" day?
      'If rsAttendance!Attended = False
         'Increase the count of consecutive days absent
         'ConsecutiveDaysAbsent = ConsecutiveDaysAbsent + 1

         'Does this beat our current record of consecutive absent days?
         'If ConsecutiveDaysAbsent > MaxDaysInRowAbsent
             'Store as the new highest count of absences
             'MaxDaysInRowAbsent = ConsecutiveDaysAbsent
         'End If
      'Else
         'This breaks the streak of consecutive absences
         'so reset the counter to zero
         'ConsecutiveDaysAbsent = 0
      'End If

      'Move to the next attendance record
      'rsAttendance.MoveNext

   'Finished looping through attendance records (at EOF)

   'Fill appropriate textbox according to consecutive absences
   'txtMaxConsecutiveDays = MaxDaysInRowAbsent

The other thing I'm not sure about is which event this should go in. I would think it would be in the Form_Current event, but I hardly ever use continuous forms so I'm not sure the effect the above pseudocode would have in that event.

 

[Zion7]

Janet, I'm missing a double quote after qryAbsent90.

You should not be looping through a recordset for every record on a continuous form.

I would make a function, that accepts "one" youth parameter.
___________________________________________________________
Function DaysAbsent(intYouthID As Integer)As Integer

Dim intAbsent As Integer

intAbsent = DCount("pkAbsent90ID","qryAbsent90", _
"YouthID = " & intYouthID & _
" And Attended = 2")

Select Case intAbsent
Case Is =>90: DaysAbsent = 90
Case Is > 60 And < 90: DaysAbsent = 60
Case Is < 60aysAbsent = 0
End Select

End Function
____________________________________________________________
put this in the control source for txtAbsent90
ControlSource "= DaysAbsent([YouthID])"

You could another argument for the "Attended" criteria

 

[Janet95]

Hi Zion7 and JoeAtWork,

I was able to figure it out. Thanks so much for both of your help. With everything going on in the world its a refreshing feeling to know there are people willing to lend a helping hand and a word of advise or encouragement. Again thank you soooo much!!!!!


What I did was created invisible form. I set the forms properties to
continuous and do not allow additions. This form is only used to open all records with YouthID’s and run the code in the background.

The key to get it to run for all records was to added this DoCmd.GoToRecord , , acNext

The continuous form opened up with my set of records of the youth id’s and the unpopulated text fields of absent30, 60 and 90. It started from the first record and ran the code. Then in move to the next record and ran the code again and so on.


In the On Current event of the form I placed the following code:

Private Sub Form_Current()
On Error GoTo Err_Form_Current
 'absent 90 consecutive days code

     
        Dim Absent90Yes As Integer
        Dim Absent90True As Integer
        Set dbAB = CurrentDb
        Set rstAbsent = dbAB.OpenRecordset("qryAbsent90")
        
        Me.txtAbsent90.SetFocus
        Me.txtAbsent90.Text = "0"
    
    
        
       
       
        rstAbsent.MoveFirst
        
            Do While rstAbsent.EOF = False
          '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
          'check for attended = no
                If (YouthID = rstAbsent!YouthID And rstAbsent!Attended = 2) Then
                
                
                Absent90No = Absent90No + 1
                
                
                                'here I just put in the number 5 for testing (will replace later with 90)
                    If Absent90No = 5 Then
                         Me.txtAbsent90.Value = 90
                         
                    End If
                
                    
                
                'to here
                Else
                          
               
                          
                End If
                
           '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                'check for attended = Yes
                If (YouthID = rstAbsent!YouthID And rstAbsent!Attended = 1) Then
                
                
                Absent90Yes = Absent90Yes + 1
                
                
                
                    
                
                'to here
                Else
                          
               
                          
                End If
                
          '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
          
                    'if total records checked = 90 then stop
                     Absent90True = Absent90No + Absent90Yes
                     
                      'here I just put in the number 10 for testing (will replace later with 90)
                     If Absent90True = 10 Then
                         
                         Exit Sub
                         
                     End If
                
                
                
            rstAbsent.MoveNext
            Loop
            
            'get max id of youth
            'if max is of youth is
        
        DoCmd.GoToRecord , , acNext
        
        
        
Err_Form_Current:
        
        
        If Err.Number = 3048 Then
            Exit Sub
        End If
               
End Sub

Thanks again so much for your help



Thanks
Janet

 

[TheAceMan1]

How are ya Janet95 . . .

Here's a function that should make things easier for ya:

[blue]Public Function AttendOK(YouthID As Long, Days As Integer) As Boolean
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT TOP " & Days & " YouthID, Attended " & _
         "FROM [purple][b][i]tablename[/i][/b][/purple] " & _
         "WHERE [YouthID] = " & YouthID & " " & _
         "ORDER BY [MeetDate] DESC;"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then
      rst.FindFirst "[Attended] = 1"
      If Not rst.NoMatch Then AttendOK = True
   End If
   
   Set rst = Nothing
   Set db = Nothing
      
End Function[/blue]

And an example of calling the function:

[blue]   If AttendOK(Me!YouthID, 90) = True then
      [green]'at least on attendance in the past 90 meetings[/green]
   Else
      [green]' no attendance in the past 90 meetings[/green]
   End If[/blue]

The function looks at as many records as the [blue]Days[/blue] arguement (90 days = 90 records = 90 meetings).

[blue]Cheers! . . .[/blue]

See Ya! . . . . . .

 

[Janet95]

Hi TheAceMan1 ,

I'm doing better now this is working. Thanks for the function. I knew there were several ways to accomplish this.

I'll give your code a try also. This should help my learning process.

Thanks again everyone

Thanks
Janet