CODEWITHSUNDEEP
vbams-accessms-access-2010
Celdor
Celdor

Reputation: 2607

VBA function to a field name

I have the same copy of a function in many forms that does exactly the same job. What changes in them is a field name. So the reason I keep it local is I don't know how I would refer to a particular field by name in a referenced form. This is the function:

Private Function getNewXNo(ByRef theForm As Form, ByVal strCode As String) As String
    Dim rs As DAO.Recordset
    Dim maxNo As Long
    Dim aNo As Long
    Set rs = theForm.RecordsetClone

    maxNo = 0
    If rs.RecordCount <> 0 Then
        rs.MoveFirst
        Do While Not rs.EOF
            aNo = CLng(Right(Nz(rs!applicationNo, strCode & "0000"), 4))
            If aNo > maxNo Then
                maxNo = aNo
            End If
            rs.MoveNext
        Loop
    End If
    getNewXNo = strCode & Format(maxNo + 1, "
    Set rs = Nothing
End Function

There are a lot of instances when I have to generate new codes, such as BB001, BB002, APP001, APP002, etc. The function reads all existing codes from a field in the referenced form and based on that creates a new one. Is there any way I can send a field name to a global function, such as

aNo = CLng(Right(Nz(rs!varThatContainsAFieldName, strCode & "0000"), 4))

Thanks

Upvotes: 1

Views: 634

Answers (1)

dwo
dwo

Reputation: 3636

You can access a field in a recordset like this:

rs("fieldname")

So you could make the field name a parameter for your function.

Upvotes: 3

Related Questions