Report to Run a Macro

[chrisrigoni]

I am trying to run a report to output information from a field with a lot of characters. I am using string manipulation to get the information I want from the field which is in a table. Also, the key is located in the table and i need the code to find a specific key, then return the information requested with the string manipulation. I am trying to run it from a report, but as of now, I have yet to have the macro output anything. It says it cannot find the expression "K" which is the key.

Here is the code:

Public Function MREquip(K As String) As String
Dim x As String
Dim db As DAO.Database
Set db = CurrentDb
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SRSS", dbOpenDynaset)
rs.MoveFirst
rs.FindFirst "[Key]='" & K & "'"              ' look for existing record
If rs.NoMatch Then                             ' no match ---
    MREquip = ""
Else
    x = rs!SpecialInstr.Value
    x = Trim(Right(x, Len(x) - InStr(1, x, "EQUIPMENT DESCRIPTION:") - 6))
    MREquip = Trim(Left(x, InStr(1, x, "<") - 1))
End If
rs.Close
Set rs = Nothing
End Function

 

[vbaInet]

Maybe the DLookup() function could be a sufficient alternative:

http://www.techonthenet.com/access/functions/domain/dlookup.php