Seek Method for Multiple-field index

  • Thread starter Thread starter Bill Herrick
  • Start date Start date
B

Bill Herrick

I'm adding records to a table from a text file and need to verify that
records from this file don't already exist in the table. Insurance against
multiple imports.

A multiple-field index named EERecordIndex (strEEId, strJCID, ShortWrkDate,
intDataType - an employee can work on one job on one date) exists and I'm
trying to use it for comparison, but the rec.Seek code throws a 3019 error -
Operation invalid without a current index.

The code I'm using follows. What am I doing wrong?

Dim db As DAO.Database
Dim rec As DAO.Recordset

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblPRDailyEarnTrans", dbOpenTable)
rec.Index = EERecordIndex

With rec

rec.Seek "=", strEEId, strJCID, ShortWrkDate, intDTP (strEEId, strJCID,
ShortWrkDate, intDTP are the input field names)

If rec.NoMatch Then
process the update
else
don't
end if

end with
 
Bill Herrick said:
I'm adding records to a table from a text file and need to verify that
records from this file don't already exist in the table. Insurance against
multiple imports.

A multiple-field index named EERecordIndex (strEEId, strJCID,
ShortWrkDate,
intDataType - an employee can work on one job on one date) exists and I'm
trying to use it for comparison, but the rec.Seek code throws a 3019
error -
Operation invalid without a current index.

The code I'm using follows. What am I doing wrong?

Dim db As DAO.Database
Dim rec As DAO.Recordset

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblPRDailyEarnTrans", dbOpenTable)
rec.Index = EERecordIndex

With rec

rec.Seek "=", strEEId, strJCID, ShortWrkDate, intDTP (strEEId,
strJCID,
ShortWrkDate, intDTP are the input field names)

If rec.NoMatch Then
process the update
else
don't
end if

end with
Click to expand...


Unless you've defined a string variable or constant named "EERecordIndex", I
think this line:
rec.Index = EERecordIndex
Click to expand...

.... should be:

rec.Index = "EERecordIndex"

That is, the name of the index should be specified as a string. Do you have
Option Explicit set, to catch the use of undefined variables?
 
The index variable definition was the problem. I was so concerned with the
method I neglected the obvious. Thanks very much for the quick response.

Option Explicit is set and quotes are in place.
 
Back
Top