Variable populations

[aguest]

Hi, can someone tell me please why my strforname and strsurname aren't getting popualted in this loop.

When it gets to the right record, it's finding the surname and forename but then not putting them in the string (inside the If statement) so I can show them in a text box.

The staffcode is an integer being passed to the function.

Public Function StaffDetails(staffCode)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strForename As String
Dim strSurname As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("StaffDetails", dbOpenTable)

Do While rst!ID <> staffCode
If rst!ID = staffCode Then
strForename = rst!Forename
strSurname = rst!Surname
End If

rst.MoveNext
Loop
[Forms]![frmFundingRequest]![txtSurname] = strSurname
[Forms]![frmFundingRequest]![txtForename] = strForename
End Function

Much appreciated.

 

[MarkK]

I don't see any reason why that wouldn't work.

But typically, I would try to find the exact record so I wouldn't need a loop. Consider these changes to your routine . . .

Public Sub SetStaffNames(staffCode As Long)
    Dim rst As DAO.Recordset
    
    Set rst = CurrentDb.OpenRecordset( _
        "SELECT Forename, Surname " & _
        "FROM StatffDetails " & _
        "WHERE StaffCode = " & staffCode)
    With rst
        If Not .EOF Then
            [Forms]![frmFundingRequest]![txtSurname] = !Surname
            [Forms]![frmFundingRequest]![txtForename] = !Forename
        End If
        .Close
    End With

End Sub

. . . does that solve the problem?

 

[aguest]

hi MarkK, yes that solved my problem. Thanks you.