Create Access VBA function to find a value corresponding to record id

[ashish]

Hi,
I am trying to create a function to read a value from against a record ID in a table. When I run the function within the query it does not run and the program hangs. This is what I am trying:

Table:
CLAIMREF SCRAPVALUE
1 1344
2 1315
3 2315
4 1215
5 1244

Function:
Function GetTSV(Claimref As Long) As Double



Dim db As Database
Dim Lrs As DAO.Recordset
Dim LSQL As String
Dim LGST As String

'Open connection to current Access database
Set db = CurrentDb()

'Create SQL statement to retrieve value from GST table
LSQL = "SELECT ScrapValue FROM QFS_Claims_backup2 WHERE [CLAIMREF] = '" & Claimref & "'"

Set Lrs = db.OpenRecordset(LSQL)

'Retrieve value if data is found
If Lrs.EOF = False Then
LGST = Lrs("ScrapValue")
Else
LGST = "Not found"
End If

Lrs.Close
Set Lrs = Nothing

GetTSV = LGST

End Function
-----------------------------------
Finally, I am using the GetTSV(ClaimNo) function to give me the value but it is failing without giving any error.

E.g. GetTSV(4)=1215 but I am not getting the right result. I am doing something wrong in the VBA code?

Please Help!!!

Regards,
Ashish

[ItsMe]

Have you tried debugging? I would set a break point at Set db = CurrentDb() and then use F8 to step through the code.

You are converting a string to a double here
GetTSV = LGST

Perhaps you should use
Function GetTSV(Claimref As Long) As String

Cross posted
https://www.accessforums.net/modules...ing-46038.html

[ashish]

Hi,
Thanks for the suggestion. I have changed the function to: Function GetTSV(Claimref As Long) As String
Added, a break point but on the form it does not give the chance to step through. The value in the field or query is appearing as #ERROR

Thanks,
Ashish

[amrut]

Function GetTSV(Claimref As Long) As Double

Claimref is declared as Long
But you are using it as String (using ' delimeter)

LSQL = "SELECT ScrapValue FROM QFS_Claims_backup2 WHERE [CLAIMREF] = '" & Claimref & "'"

I am not sure if it is the reason for error. Try removing single quote around Claimref.

[ashish]

Hi Amrut,
You are a star! It worked straightaway after removing the single quote. I had already changed the Function to String on ItsMe's advice.
Thanks to both of you.
Ashish