Loop Is Reading Empty After Filling

[Laneyshia]

Hello,

So I finished my code but I have this one problem.

The Table That Is To Start The Loop Is Reading Empty.

Below is all the code to the program.

Public Function Fill_Job_Positions()
        'On Local Error GoTo Fill_Job_Positions_Err
    
    '-------------------------Summary--------------------------------
    '
    'Step 1:
    '
    '   Sets "dbs" as current Database of type "Data Acess Object"
    '
    '   Opens and Runs a Query that Makes a Table that may already exist.
    '       Click "Yes" to delete existing table and paste to remake Table.
    '
    '   Opens a new recordset within the table "New Schedule"
    '       For later usag3
    '
    '   Opens a new recordset within the table "Jobs Not Yet Filled"
    '       For later usage
    '
    '-------------------------Summary--------------------------------
    
    
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    
    DoCmd.OpenQuery "Make Jobs To Fill Query", acViewNormal, acReadOnly
    
    DoCmd.OpenQuery "Put It Query - Replacing"
    
    Dim Replacements As DAO.Recordset
    Set Replacements = dbs.OpenRecordset("Put It Query - Replacing")
    
    Dim Jobs As DAO.Recordset
    Set Jobs = dbs.OpenRecordset("Jobs To Fill")
        
    Dim NotFilled As DAO.Recordset
    Set NotFilled = dbs.OpenRecordset("Jobs Not Yet Filled")
    
    Dim RemainNF As DAO.Recordset
    Set RemainNF = dbs.OpenRecordset("Jobs Remain Not Filled")
    
    Dim Schedule As DAO.Recordset
    Set Schedule = dbs.OpenRecordset("New Schedule")
    

    While Not Schedule.EOF
        
        'MsgBox "Schedule Record Before Deleting: " & Schedule![First Name]
        
        Schedule.Delete
        
        Schedule.MoveNext
        
    Wend
    
    While Not RemainNF.EOF
        
        'MsgBox "RemainNF Record Before Deleting: " & RemainNF![Main Positions]
        
        RemainNF.Delete
        
        RemainNF.MoveNext
        
    Wend
    
    While Not NotFilled.EOF
        
        'MsgBox "NotFilled Record Before Deleting: " & NotFilled![Main Positions]
        
        NotFilled.Delete
        
        NotFilled.MoveNext
        
    Wend
    
    
    '-------------------------Summary--------------------------------
    '
    'Step 2:
    '
    '   Sets "dbs" as current Database of type "Data Acess Object"
    '
    '   Opens and Runs a Query that Makes a Table that may already exist.
    '       Click "Yes" to delete existing table and paste to remake Table.
    '
    '   Creates a new table and names it "New Schedule"
    '       For later usage
    '
    '-------------------------Summary--------------------------------
    
    'Testing the extraction method (fields from one table into another)
    'Test ~ Copying Jobs (DAO) into NotFilled (DAO)
    
    'Declare the strings needed to individually extract data
    Dim FirstName As String
    Dim LastName As String
    Dim JMP As String 'Jobs Main Positions
    Dim RMP As String 'Replacements Main Positions
    Dim Count As Integer 'Tracker for Jobs
    Dim Count2 As Integer ' Tracker for Replacements
    Count = 0
    
      
    Jobs.MoveFirst
    
    While Not Jobs.EOF
    
        JMP = Jobs![Main Positions]
        
        Count2 = 0
        
        'MsgBox "Jobs Main Position Number: " & Count & " " & JMP
        
        Count = Count + 1
        
        While Not Replacements.EOF
        
            RMP = Replacements![Main Position]
            
            'MsgBox "Replacements Main Position Number: " & Count2 & " " & RMP
            
            If RMP = JMP Then
            
                Schedule.AddNew
                
                Schedule![First Name] = Replacements![First Name]
                
                Schedule![Last Name] = Replacements![Last Name]
                
                'In "Put It Query - Replacing" it is called "Main Position" because if references "Put It" the table
                Schedule![Main Positions] = Replacements![Main Position]
                
                Schedule![Replacement Positions] = Replacements![Replacement Position]
                
                Schedule.Update
                
                'MsgBox "Count2 In First IF (before addition): " & Count2
                
                Count2 = Count2 + 1
                
                'MsgBox "Count2 In First IF (after addition): " & Count2
                
            End If
            
            Replacements.MoveNext
            
        Wend
        
        If Count2 = 0 Then
            
            'MsgBox "Count2 In Secound IF: " & Count2
            
            NotFilled.AddNew
            
            NotFilled![Main Positions] = Jobs![Main Positions]
            
            NotFilled.Update
            
            'MsgBox "Jobs in NotFilled Table: " & NotFilled![Main Positions]
            
        End If
        
        Replacements.MoveFirst
        
        Count = Count + 1
        
        Jobs.MoveNext
        
    Wend
    
    '----------------------------------------------------------------
    'Not Compares Replacement Positions with Jobs Not Yet Filled
    '----------------------------------------------------------------
    Dim SRP As String 'Schedule Replacement Positions
    Dim NFMP2 As String
    Dim Count3 As Integer
    
    'MsgBox "Notfilled: " & NotFilled![Main Positions]
    
    While Not NotFilled.EOF
        
        Count3 = 0
        
        NFMP2 = NotFilled![Main Positions]
        MsgBox "NFMP2: " & NFMP2
        
        While Not Schedule.EOF
            
            SRP = Schedule![Replacement Positions]
            MsgBox "SRP: " & SRP
            
            If SRP = NFMP Then
                
                Schedule![Scheduling Comments] = "Employee Needed For Both Of Their Positions"
                
                Schedule.Update
                
                MsgBox "Schedule![Scheduling Comments] = " & Schedule![Scheduling Comments]
                
                MsgBox "Count3 (before add): " & Count3
                
                Count3 = Count3 + 1
                
                MsgBox "Count3 (after add): " & Count3
                
            End If
            
            Schedule.MoveNext
            
        Wend
        
        If Count3 = 0 Then
        
            MsgBox "There is no one to fill the following Position: " & NFMP
            
        End If
        
        NotFilled.MoveNext
        
    Wend
            
                
        
            
    
    
    
End Function

Below is the code that I am having issues with.

 '----------------------------------------------------------------
    'Not Compares Replacement Positions with Jobs Not Yet Filled
    '----------------------------------------------------------------
    Dim SRP As String 'Schedule Replacement Positions
    Dim NFMP2 As String
    Dim Count3 As Integer
    
    'MsgBox "Notfilled: " & NotFilled![Main Positions]
    
    While Not NotFilled.EOF
        
        Count3 = 0
        
        NFMP2 = NotFilled![Main Positions]
        MsgBox "NFMP2: " & NFMP2
        
        While Not Schedule.EOF
            
            SRP = Schedule![Replacement Positions]
            MsgBox "SRP: " & SRP
            
            If SRP = NFMP Then
                
                Schedule![Scheduling Comments] = "Employee Needed For Both Of Their Positions"
                
                Schedule.Update
                
                MsgBox "Schedule![Scheduling Comments] = " & Schedule![Scheduling Comments]
                
                MsgBox "Count3 (before add): " & Count3
                
                Count3 = Count3 + 1
                
                MsgBox "Count3 (after add): " & Count3
                
            End If
            
            Schedule.MoveNext
            
        Wend
        
        If Count3 = 0 Then
        
            MsgBox "There is no one to fill the following Position: " & NFMP
            
        End If
        
        NotFilled.MoveNext
        
    Wend
            
      
    
End Function

After running the module I go back to access and look within the "Jobs Not Yet Filled" table and it has 2 positions listed in which it should. What I don't understand is why the look in the above code does not go through.

I have tested it could with "msgbox" code and the While loop does not even enter through.

Thank you,

For Your Help

[ADezii]

Before entering the Loop, try explicitly moving to the 1st Recordset in the Recordset, as in:

'Code intentionally omitted...
NotFilled.MoveFirst
While Not NotFilled.EOF
'Code intentionally omitted...

[Lysander]

Not sure what is going on, but between lines 23 and 27 of your code above, you have a

Schedule.Update

but no Schedule.addNew or Schedule.Edit.

Changing a record in Access requires 2 commands, not one.

Start with an rs.AddNew or rs.Edit

End with an rs.Update.

Hope this helps a little

[Laneyshia]

Thank You Both!!

It worked and I understand why it didn't without the lines of code.

I hope you guys have a great year.

Thank You!