Passing Vars with Seek Function

[dbarmer]

In a current database, a form is loaded and from there VBA code is launched.
I have a linked table to another database (which is a rather large table). I
have a function that uses the seek and index commands - Which works great!.
(You can't directly seek on a linked table). I use seek and index because of
the large table size and it returns very fast.

I can pass vars, but what I need to know is how can I pass the entire
record. Currently, I am using a PassVar$ variable in my call statement,
which works great for a field or two. In this table I have 30 or more
fields, and could use any of the fields depending on a user's option choice
or the environment it is launched from.

I am not familiar with using Arrays... Is this what will need to happen, and
if so, any help would be greatly appreciated!

 

[Allen Browne]

Pass the Recordset. Assuming the current record is the one you found (i.e.
the Seek worked), it's easy enough to read from there,

It's passed like this:

Function DoSomething(rs As DAO.Recordset)
If rs.Nomatch Then
Debug.Print "The seek didn't find anything"
Else
Debug.Print rs!FirstName, rs!Surname, rs!Address, rs!City
End If
End Function

Although Seek is fast, my preference is to OpenRecordset() on a SQL
statement that fetches only the record(s) you need.

 

[Tom van Stiphout]

On Thu, 12 Nov 2009 17:58:04 -0800, dbarmer

You might want to consider a Dictionary object. It supports a list of
Name/Value pairs similar to what is used by a web page's querystring.
Set a reference to "Microsoft Scripting Runtime" and then write (off
the top of my head):
dim dict as new scripting.dictionary
dict.Add "FirstName", "Tom"
dict.Add "City", "Phoenix"
dict.Add "Zip", 85000
'etc.

Then pass this to a function that will seek for a match, and if found
return the ID value:
public function FindMyRecord(dict as scripting.dictionary) as long
dim strWhere as string
For i = 0 To dict.Count - 1
strWhere = strWhere & dict.Keys(i) & "=" &
WrapSingleQuotes(dict.Items(i)) & " & "
Next i
strWhere = Left$(strWhere, Len(strWhere) - 3) 'strip trailing " & "

dim rs as dao.recordset
set rs=dbBackEnd.OpenRecordset("myTable", dbOpenTable)
rs.Index = "PrimaryKey"
rs.Seek "=", strWhere
if not rs.NoMatch then
FindMyRecord = rs!ID
end if
end function

Private Function WrapSingleQuotes(ByVal s As String) As String
If Not IsNumeric(s) Then s = "'" & s & "'"
WrapSingleQuotes = s
End Function

-Tom.
Microsoft Access MVP