using seek

[PrincessBretti]

Hi,
I have a problem using the seek command, here is the code:

Dim rstQuest As Recordset
Dim dbsBDC As Database

Set dbsBDC = OpenDatabase("BDC.mdb")

Set rstQuest = dbsBDC.OpenRecordset("Questionnaire", dbOpenTable)
With rstQuest
.Index = "ID"
.Seek "=", Me.ID
If .NoMatch Then
MsgBox "ID not found!"
End If

.Close
End With

It always comes up with no match even when it shouldn't!

When I changed the primary key in the table to "ida" rather than "ID" and changed the .index value to the same in the above code, I recieve an error saying that the field "ida" is not a primary key in the table, therefore I think the above is pointing to the wrong place but don't know why, can anybody help?

Thanks

Bretti

 

[charityg]

change your index to
rst.index="primarykey"
by this I mean it exactly as typed above, primarykey is not a variable.

 

[MHM]

Hi Bretti,

probably you haven't set .Index to the right or an existing index. Escpecially be careful about using index-names: When changing a field name, the index is refered to with the old name, its name can't be changed is Access directly.

Mic

 

[PrincessBretti]

Thanks but it still doesn't work!

Any more ideas?

Bretti

 

[chrismcbride]

Have never used Seek method but, should this line...
.Seek "=", Me.ID
be formatted like...
.Seek "= " & me.ID
HTH
Chris

 

[PrincessBretti]

Nope,
But thanks anyway

Bretti

 

[DMC]

Bretti,

Try changing ME.ID to something like ctlID

Dim ctlID as Control
set ctlID = me!ID

and

.seek "=", ctlID
(Access is a bit dodgy about direct references to objects when using seek)

also check that your control data type is the same as the field in the recordset otherwise the seek method will not work.

cheers

 

[Sorrells]

This is to the side of the main issue here, but I have a function to delete records that works fine with the name of a indexed field in a table where it is one of two primary keys. However, when using the same function for a different table but with the same indexed field name and also a primary key, I received the error (Access97)of "FormID isn't an index to this table. Run time error 3015."
After reading this thread, I used an if statement to make the Index "primarykey" for this table giving me the error and it worked. So I use the field name if there are two primary keys in the table and "primarykey" if there is one primary key in the table. Looks like an Access glitch to me but interesting nevertheless.

I then had another table, same single primary key. I could not assign the field name here either but the use of "primarykey" worked again.

In conclusion, thanks a lot folks! and thanks to this forum!

Sorrells