Order By produces duplicates (1 Viewer)

[tabitha]

I need to be able to assign a title to a set of dependents that is not what they are normally called. As in, every dependent is either called "Dependent" or "Step Child", but I need to call them "Child1" "Child2" "Child3" etc, for however many children an employee has.

I have a function that does this, however, I need the oldest child to be Child1 and so on, but every time I add an ORDER BY clause to my sql, it duplicates the records. I confirmed this by adding the child's name to the end, and instead of this:

Child1 Martha (eldest)
Child2 Edward

I got:

Child1 Martha
Child2 Martha
Child3 Edward
Child4 Edward

I don't understand how an Order By could cause this. As soon as I remove it, I get:

Child1 Edward
Child2 Martha

Here is my code:

Function GetChildNum()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
Dim Current As String
Dim EmpID As String
Dim holdFirst As String
Dim holdLast As String
Dim ChildNum As String


EmpID = 5482
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT [Employee ID], [Dependent First Name], [Dependent DOB]" & _
                            " FROM Dependents " & _
                            " WHERE (([Employee ID] = " & EmpID & _
                            " AND Relationship = " & Chr(34) & "Dependent" & Chr(34) & _
                            ") OR ([Employee ID] = " & EmpID & _
                            " AND Relationship = " & Chr(34) & "Step Child" & Chr(34) & "))" & _
                            " ORDER BY [Dependent DOB];")
Current = 0
holdFirst = 1
holdLast = rst.RecordCount

Do Until rst.EOF
    
    
    For i = holdFirst To holdLast
        Current = Current + 1
        ChildNum = "Child" & Current & " " & rst![Dependent First Name]
        MsgBox ChildNum
    Next
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
End Function

 

[tabitha]

Nevermind, I solved it. Since I'm reducing the recordset to only those that have the correct [Employee ID] anyway, I don't need the For..Next. Originally I was going to run this on the whole table, but I'm going to pass the [Employee ID] to this function for each employee.. Somehow lol

Once I took out the For..Next, it pulled them correctly.