FindFirst problem (how do I use it?)

[Niklas Östergren]

Hi!

I´m trying to write a simple function to lookup a string value in a linked
table (NOT local). And If I don´t remember wrong I can´t use SEEK-method for
this so I try to use *FindFirst* method but can´t get it to work.

I´m sure I´m making it more difficult than it is and I have tried for some
time but can´t get it right. Any help are apreciated!

TIA!
// Niklas


Her´s my code:
============================================
Public Function FindFirstStringRecord(strTableName As String, strFieldName
As String, intRecordID As Integer) As String


Dim db As Database
Dim rec As Recordset
Dim SQL As String
Dim strSearchString As String
Dim strMsg As String

strSQL = "SELECT * FROM " & strTableName
strSearchString = " WHERE " & strFieldName & " = " & intRecordID

'Check validity of passed parameters.
' Exit sub presenting Msg if not valid
If strSQL = "" Or strSearchString = "" Then
strMsg = "SQL-string and/or string to search for is missing."
MsgBox strMsg
Exit Function
Else
Set db = Currentdb()
Set rec = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rec
' rec.Index = "PrimaryKey"
.FindFirst strSearchString

' If found, get value
If Not .NoMatch Then
' If value <> 0 send value to calling sub else exit function
If .Fields(strFieldName) <> "" Then
FindFirstStringRecord = .Fields(strFieldName)
End If
End If

' Close the recordset
.Close
End With

End If

Set rec = Nothing
Set db = Nothing

End Function

 

[Wayne Morgan]

You don't mention the error you're receiving or your version of Access, so
there are a few possibilities.

1) strSearchString includes the word WHERE. For the criteria of FindFirst,
the WHERE is assumed and shouldn't be part of the string.

2) >Dim rec As Recordset< - DAO recordset or ADO recordset? I recommend
changing this to
Dim rec As DAO.Recordset to avoid Access picking the wrong one.

3) Also, in the code window go to Tools|References and verify that you have
the reference to DAO checked. This should only be a problem in Access 2000
or newer. In Access 2000 or newer, the one you are looking for is "Microsoft
DAO 3.6 Object Library". If there is an older one checked, uncheck it and
check this one.

 

[Niklas Östergren]

Yes it works!

I just removed "WHERE" from strSearchString and then it worked. I also had
to add another string variable containing the resultfiledname from the
table.

Thank´s a lot!
// Niklas

 

[Roger Carlson]

You CAN used SEEK with a linked table, but you have to open the database
differently. Instead of
Set dbs = CurrentDb
you have to open it explicitly
Set dbs = OpenDatabase("C:\the\path\SeekWithLinkedTableBE.mdb")

On my website (see sig below) is a small sample database called
"SeekWithLinkedTable2k.mdb" which illustrates. Also, there is a sample
called "UpdateImportSeekFind2k.mdb", which illustrates the difference
between SEEK and FIND.

 

[Niklas Östergren]

Hi Roger!

Thank´s for your reply!
I did get it to work so I´ll stick to what I already got. But it´s good to
know that it´s possible and I´ll keep your webseit in mind!

// Niklas

 

[Roger Carlson]

Great. Glad to hear it.

However, if you have performance issues, you might want to revisit the SEEK.
It is MUCH faster, as the SeekFind example illustrates.

 

[Albert D. Kallal]

You can use the built in dlookup commnd

=dlookup("fieldName","TableName", "Condtions")

So, get the part description for partnumber 123 in table tblParts, you can
go:

dim strPartsDesc as string

strPartsDesc = dlookup("Description","tblParts","partID = 123")

msgbox "Description for part 123 = " & strPartsDesc

However, lets fix your code anway. I removed a ton of stuff you don't
need...

Public Function FindFirstStringRecord(strTableName As String, strFieldName
As String, intRecordID As Integer) As String

note that intRecordID needs to be a long var...not a integer as you have

Dim rec As dao.Recordset
Dim SQL As String
Dim strSearchString As String

strSQL = "SELECT * FROM " & strTableName
strSearchString = " WHERE " & strFieldName & " = " & intRecordID

'Check validity of passed parameters.
' Exit sub presenting Msg if not valid
If strSQL = "" Or strSearchString = "" Then
MsgBox "SQL-string and/or string to search for is missing."
Exit Function
end if

strSql = strSql & strSearchString
Set rec = Currentdb.OpenRecordset(strSQL)
if rec.RecordCount > 0 then
FindFirstStringRecord = rec(strFieldName)
endif
rec.Close
Set rec = Nothing

End Function

 

[Niklas Östergren]

Hi Albert!

Thank´s for your advice I allready know this one though, but that couldnt
you know. The reason to why I didn´t wanted to use DLookUp methode is due to
performance. I have been told that it´s much slower so I´ll try to look for
other solutions. Besides it also develops me as a programmer which I enjoy!

Thank´s anyway!
// Niklas