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) ...
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).
