Access-VBA - Name field parameter

May 2017




Issue


I am calling a function that will search for the value of a specific field for precise registration. To do this I pass a parameter to my office on behalf of my table, the record ID and field name.

FindLinkedString Function (ByVal Table As String, ByVal ID As Integer, ByVal Field As String) As String   

Sun As Database mydb2   
Sun LinkedTable As Recordset   
Sun linkedTableCount As Integer   

Set mydb2 = CurrentDb ()   
Set LinkedTable = mydb2.OpenRecordset (Table)   

LinkedTable.MoveFirst   
For linkedTableCount = 1 To LinkedTable.RecordCount   
If LinkedTable! ID = ID Then   
FindLinkedString = table & "! " Field &   
GoTo Fin_FindLinkedString   
End If   
LinkedTable.MoveNext   
Next   
Fin_FindLinkedString:   

LinkedTable.Close   
mydb2.Close   

End Function 



The problem is that FindLinkedString = table & "!" & Field, is obviously fair game since it will not find value in the db, it saves the string by turning "Table" and "Field" by the parameters (eg "reviews" Name "then I want that it appears "Smith")

Solution


You can use this:
... LinkedTable.MoveFirst While Not LinkedTable.EOF If LinkedTable.Fields ( "ID") = ID Then FindLinkedString = LinkedTable.Fields ( "Name") LinkedTable.Close mydb2.Close Set LinKedTable = Nothing Set mydb2 = Nothing Exit Function LinkedTable.MoveNext Wend End If '...



But it may be simpler:

... Set LinkedTable = mydb2.OpenRecordset ( "SELECT Name FROM" & Table & "WHERE ID =" & ID) FindLinkedString = LinkedTable.Fields (0) ...

Related


Published by aakai1056. Latest update on January 2, 2012 at 08:31 AM by aakai1056.
This document, titled "Access-VBA - Name field parameter," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).