What should I pass into this Function?

[Barry Wright]

Hi All,

The error I am getting is 3265:
"Item not found in this collection."

How should I be passing in the vReturnFieldName so it will be recognized as
a value in the collection?
Thanks for your assistance.

Information:
I pass four variables to the FindNameString function and want it to return
the value of field:
strProjectPrime in table 'Project Primes"

The table "Project Primes" has two fields
lngProjectPrimeID and strProjectPrime

Private Sub TestRun()
MsgBox FindNameString("Project Primes", "lngProjectPrimeID",
"strProjectPrime", 21)
End Sub

Public Function FindNameString(vTblName As String, vFindFieldName As String,
_
vReturnFieldName As String, vFindFieldValue As Long) As String
On Error GoTo FindNameString_Error

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset(vTblName, dbOpenSnapshot)
If rs.RecordCount > 0 Then
rs.FindFirst "[" & vFindFieldName & "]=" & vFindFieldValue
If rs.NoMatch = False Then
FindNameString = rs![vReturnFieldName] 'rs![ProjectPrime]
Else
FindNameString = 0
End If

End If
rs.Close
Set rs = Nothing

On Error GoTo 0
Exit Function

FindNameString_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
FindNameString of Module Conversion"

End Function

[jimfortune]

Barry Wright wrote:

Hi All,

The error I am getting is 3265:
"Item not found in this collection."

How should I be passing in the vReturnFieldName so it will be recognized as a value in the collection?
Thanks for your assistance.

When I pasted your code into Access, the vTblName just before
dbOpenSnapshot came in as vTblNa-me. When I removed the hyphen and
used rs(vReturnFieldName) instead of rs![vReturnFieldName] it worked.

James A. Fortune

[Barry Wright]

Indeed it did work!
Fantastic James and thank you!

Barry

<ji********@compumarc.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Barry Wright wrote:

Hi All,

The error I am getting is 3265:
"Item not found in this collection."

How should I be passing in the vReturnFieldName so it will be

recognized as

a value in the collection?
Thanks for your assistance.

When I pasted your code into Access, the vTblName just before
dbOpenSnapshot came in as vTblNa-me. When I removed the hyphen and
used rs(vReturnFieldName) instead of rs![vReturnFieldName] it worked.

James A. Fortune