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!