Why does this Seek Method not work?

[Guest]

I have the following code in my Access 2000 application. The EntrantID and
CompID fields form a joint primary key in the EntrySummary table (and fields
in my form share the same names).

Dim MyDB As DAO.Database
Dim rstEntrySummary As DAO.Recordset

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set rstEntrySummary = MyDB.OpenRecordset("EntrySummary")

rstEntrySummary.Index = "PrimaryKey"
rstEntrySummary.Seek "=", Me![EntrantID], Me![CompID]
etc, etc

This code worked fine when my EntrantID and CompID fields were both numeric.
However I have just changed the EntrantID to a text field and the Seek
statement now triggers a Data Type Conversion Error.

To add to my problems, the Visual Basic Help system on my PC seems to be
corrupted in some fashion and refuses to list any info on the Seek Method.

Any suggestions?

 

[Ken Snell \(MVP\)]

You need to delimit the text string with ' characters:

rstEntrySummary.Seek "=", "'" & Me![EntrantID] & "'", Me![CompID]

 

[Guest]

Ken,
Thanks for the suggestion - which looks like a convincing solution. However,
to my surprise, it still fails with a Data Type Conversion Error.

David

You need to delimit the text string with ' characters:

rstEntrySummary.Seek "=", "'" & Me![EntrantID] & "'", Me![CompID]

--

Ken Snell
<MS ACCESS MVP>

I have the following code in my Access 2000 application. The EntrantID and
CompID fields form a joint primary key in the EntrySummary table (and
fields
in my form share the same names).

Dim MyDB As DAO.Database
Dim rstEntrySummary As DAO.Recordset

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set rstEntrySummary = MyDB.OpenRecordset("EntrySummary")

rstEntrySummary.Index = "PrimaryKey"
rstEntrySummary.Seek "=", Me![EntrantID], Me![CompID]
etc, etc

This code worked fine when my EntrantID and CompID fields were both
numeric.
However I have just changed the EntrantID to a text field and the Seek
statement now triggers a Data Type Conversion Error.

To add to my problems, the Visual Basic Help system on my PC seems to be
corrupted in some fashion and refuses to list any info on the Seek Method.

Any suggestions?

 

[Ken Snell \(MVP\)]

OK - I don't usually use Seek in my applications, so I'm learning right
along with you here. Let's try this to see if ACCESS will see EntrantID
control as Text:

rstEntrySummary.Seek "=", CStr(Me![EntrantID]), Me![CompID]

--

Ken Snell
<MS ACCESS MVP>

Ken,
Thanks for the suggestion - which looks like a convincing solution.
However,
to my surprise, it still fails with a Data Type Conversion Error.

David

You need to delimit the text string with ' characters:

rstEntrySummary.Seek "=", "'" & Me![EntrantID] & "'", Me![CompID]

--

Ken Snell
<MS ACCESS MVP>




message

I have the following code in my Access 2000 application. The EntrantID
and
CompID fields form a joint primary key in the EntrySummary table (and
fields
in my form share the same names).

Dim MyDB As DAO.Database
Dim rstEntrySummary As DAO.Recordset

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set rstEntrySummary = MyDB.OpenRecordset("EntrySummary")

rstEntrySummary.Index = "PrimaryKey"
rstEntrySummary.Seek "=", Me![EntrantID], Me![CompID]
etc, etc

This code worked fine when my EntrantID and CompID fields were both
numeric.
However I have just changed the EntrantID to a text field and the Seek
statement now triggers a Data Type Conversion Error.

To add to my problems, the Visual Basic Help system on my PC seems to
be
corrupted in some fashion and refuses to list any info on the Seek
Method.

Any suggestions?

 

[Guest]

Ken,
Another very plausible solution, but it still fails with the same error. Is
there a possibility that the index for EntrantID in the EntrySummary table is
still in the original numeric form? If so, how would one force it to change
to a string type index?

David

 

[Guest]

Ken,
I have now managed to fix this problem with the following modified version
of the Seek coding,

rstEntrySummary.Seek "=", Array(Me![EntrantID], Me![CompID])

A bit of Googling threw up an archived seven-year old MSDN document that
suggested the Array function was necessary when using the Seek method with a
multiple-field index. The article related to ADO, not DAO, but I still
thought it was worth a try. A Microsoft KB article about using DAO Seek shows
the use of a multiple numeric field index without the Array function, so this
appears to be an issue only relevant to a mixed string and numeric index.

Anyway, thanks for doing your best to help, Ken. It's genuinely appreciated.

David

 

[Ken Snell \(MVP\)]

Most intriguing.. thanks for posting that solution. This one goes in my
archives.
--

Ken Snell
<MS ACCESS MVP>

Ken,
I have now managed to fix this problem with the following modified version
of the Seek coding,

rstEntrySummary.Seek "=", Array(Me![EntrantID], Me![CompID])

A bit of Googling threw up an archived seven-year old MSDN document that
suggested the Array function was necessary when using the Seek method with
a
multiple-field index. The article related to ADO, not DAO, but I still
thought it was worth a try. A Microsoft KB article about using DAO Seek
shows
the use of a multiple numeric field index without the Array function, so
this
appears to be an issue only relevant to a mixed string and numeric index.

Anyway, thanks for doing your best to help, Ken. It's genuinely
appreciated.

David

I have the following code in my Access 2000 application. The EntrantID
and
CompID fields form a joint primary key in the EntrySummary table (and
fields
in my form share the same names).

Dim MyDB As DAO.Database
Dim rstEntrySummary As DAO.Recordset

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set rstEntrySummary = MyDB.OpenRecordset("EntrySummary")

rstEntrySummary.Index = "PrimaryKey"
rstEntrySummary.Seek "=", Me![EntrantID], Me![CompID]
etc, etc

This code worked fine when my EntrantID and CompID fields were both
numeric.
However I have just changed the EntrantID to a text field and the Seek
statement now triggers a Data Type Conversion Error.

To add to my problems, the Visual Basic Help system on my PC seems to be
corrupted in some fashion and refuses to list any info on the Seek
Method.

Any suggestions?

 

[Roger Carlson]

I'm jumping in late on this one, but I don't think a composite primary key
of mixed datatypes is your problem.
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "UpdateImportSeekFind.mdb" which uses a compound primary key.
My sample uses two text fields, so I changed one to a numeric. The code
worked exactly as before. My Seek line looks like this:

rstShoes.Seek "=", rstImport!StockNumber, rstImport!Width

The only difference between yours and mine is that I;m using a recordset
object and you are using Me!

rstEntrySummary.Seek "=", Me![EntrantID], Me![CompID]

I wonder if Me is somehow misrepresenting the datatype of EntrantID. I
don't have any way to test this easily, but I wondered if something like:

rstEntrySummary.Seek "=", cstr(Me![EntrantID]), Me![CompID]


would work.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Ken Snell \(MVP\)]

I wonder if Me is somehow misrepresenting the datatype of EntrantID. I
don't have any way to test this easily, but I wondered if something like:

rstEntrySummary.Seek "=", cstr(Me![EntrantID]), Me![CompID]


would work.

See my second reply to OP in this thread.....
;-)