Solved Seek issue (1 Viewer)

[Dumferling]

I am trying to get the database to check if a record exists. I am using Seek to do this where the code will pick up the record number from the primary table and search on the related table for that index number (IDContract).

The code is:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim vblIndex As Integer
vblIndex = Forms!frmCContractBrowser!ID

'On Error GoTo ErrorHandler

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblCPOPIA", dbOpenDynaset)

' The index name for Employee ID.
rst.Index = "IDContract"
rst.Seek "=", vblIndex

If rst.NoMatch Then
Dim msg As String
msg = MsgBox("Record not found", vbOKOnly, "LMD Notification")
Else

End If

rst.Close
db.Close

Set rst = Nothing
Set db = Nothing

I am getting an error on the rstIndex = "IDContract" line - Operation is not supported for this kind of object.

I cannot see what I am doing wrong. It is something simple, I am sure but looking at examples, I can't see it.

 

[Ranman256]

use dlookup, only 1 line:

vRet = Dlookup("[IDContract]", "tblCPOPIA","[IDContract]=" & Forms!frmCContractBrowser!ID

if isnull(vRet) then
   msgbox "Not Found"
else
   msgbox "found"
endif

 

[jdraw]

Or use DCount

If Dcount("*", "tblCPOPIA","[IDContract]=" & Forms!frmCContractBrowser!ID) > 0 Then
..record exists
else
....record does not exist
end if

 

[arnelgp]

I am getting an error on the rstIndex = "IDContract" line - Operation is not supported for this kind of object.

you need to Open it using the option dbOpenTable:

Set rst = db.OpenRecordset("tblCPOPIA", dbOpenTable)
' The index name for Employee ID.
rst.Index = "IDContract"
rst.Seek "=", vblIndex

 

[Pat Hartman]

Some operations do NOT work on linked tables. I don't know if Seek is one of them. Either DLookup() or DCount() as have already been suggested are simple and work. I use DCount() for this task when I need to find out if a record exists..

 

[pbaldy]

The domain aggregate functions already suggested are simplest to do what you're doing. In other situations, I would never open a recordset on the entire table and then find the desired record with Seek. It would be very inefficient and slow on larger tables. I'd open the recordset on the desired record:

Set rst = db.OpenRecordset("SELECT * FROM tblCPOPIA WHERE IDContract = " & vblIndex, dbOpenDynaset)

Testing for rst.EOF would tell you if there were no matching records.

 

[arnelgp]

It would be very inefficient and slow on larger tables

remember, we are using Index.

 

[pbaldy]

remember, we are using Index.

Well, maybe I don't understand the inner workings, but it seems like you're pulling the entire table over the wire before you use your index. I'm letting the database engine do the work and pulling a single record over the wire. @Pat Hartman certainly knows more than I do, perhaps she can weigh in.

 

[Dumferling]

Thanks all. I though DLookup/DCount would be slower but quite possibly the Seek function is not working because it is a linked table, as suggested. There won't be thousands of records, more like a few hundred so I will use DLookup/ DCount. Thanks for the help

 

[isladogs]

See my article Check Record Exists which compares the speed (efficiency) of 4 different methods of doing this, both with indexed and non-indexed fields. Using indexed fields each of the methods should be almost instantaneous, even with large datasets

 

[arnelgp]

Seek function is not working because it is a linked table

of course it will work.
paste in a Module:

Public Function fnOpenLinkedTable(ByVal sTable As String, Optional ByVal IdxName As String) As Object
    ' arnelgp
    ' Note:
    '
    ' sTable must be a Linked (Access database Only) table.
    '
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim rs As DAO.Recordset
    Dim sDbPath As String
On Error GoTo Err_Handler
    Set db = CurrentDb
    Set td = db.TableDefs(sTable)
    sDbPath = Replace$(td.Connect, ";DATABASE=", "")
    Set db = DBEngine(0).OpenDatabase(sDbPath, False, False)
    Set rs = db.OpenRecordset(sTable, dbOpenTable)
    If Len(IdxName) <> 0 Then
        rs.Index = IdxName
    End If
    Set fnOpenLinkedTable = rs
exit_sub:
    Set td = Nothing
    Set db = Nothing
    Exit Function
Err_Handler:
    MsgBox err.Number & ": " & err.Description
    Resume exit_sub
End Function

your code:

Dim rst As DAO.Recordset
Dim vblIndex As Long
vblIndex = Forms!frmCContractBrowser!ID

'On Error GoTo ErrorHandler


Set rst = fnOpenLinkedTable("tblCPOPIA", "IDContact")

rst.Seek "=", vblIndex

If rst.NoMatch Then
    Dim msg As String
    msg = MsgBox("Record not found", vbOKOnly, "LMD Notification")
Else

End If

rst.Close

 

[gemma-the-husky]

another way is not to use seek.

You can't use indexed seek on a linked table. An alternative is to use find first,

see the second reply to this (from Marshall Barton)
trying to open a linked table.... what's going on? (microsoft.com)

 

[arnelgp]

You can't use indexed seek on a linked table

that's a myth? i already showed you the code.
Try it first. I have tried it.

Look also at the last post (the one with check marked, meaning acceptable answer to the OP) of that link.