Index function in coding.

[Guest]

Hi ppl,

I have the following coding:

Set tblLot = CurrentDb.OpenRecordset("Lot")
tblLot.Index = "LotNumber"
tblLot.Seek "=", LotTxt

When the program runs, the following error msg will pop out:
'LotNumber' is not an index in this table

But the fact is, Lotnumber is an index in the "Lot" table. Can check the
index property when we enter design view. Setting of the 'Indexed" = Yes
(Duplication OK)

Pls advise. All helps are appreciated deeply. Thank you

 

[Allen Browne]

LotNumber might be the name of an indexed field, but is it the name of the
index?

The index name might be "PrimaryKey", or some previous name of that field.
In table design view, open the Indexes box (View menu), and look at the
names of the indexes to see what it is called.

BTW, most of us abandoned the code you are using years ago. Although it's
fast, it ceases to work when you split your database, so it is generally
better to use:
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Lot WHERE LotNumber =
99;")

 

[Douglas J Steele]

You sure LotNumber is the name of the index, and not just the name of the
field in the index?

 

[Guest]

Hi Allen,

Thank you for the advice. It works.

Anyway, I'm a beginner in Access. Have a project on hand. Went lib for a
book using programming to manipulate all the actions. And the book was meant
for Access 2000. So guess that's why I'm using an outdated method.

Know that there are several ways to get a thing done in Access. Found this
to be a very useful program, thus will like to learn more abt it.

Can you recommend me a book that covers most of Access functions??

Thank you!!!

 

[Guest]

Hi Douglas,
Didn't know there is a difference in name of Index and name of Field.

Well, 1 more thing learnt.

Anyway, is there anyway we can define the name of the index ourselves??

Thank you for the reply.

 

[Douglas J Steele]

Sure. With the table open in Design view, select View | Indexes from the
menu. The name of the index is the left-most column. (Note that you only use
the Name on the first row of multfield indexes)

 

[Allen Browne]

The Access 2000 book will be more than adequate to get you through the
basics (assuming it's a reasonable one). Everything in that is still
relevant for Access 2003.

When you get a bit more advanced, anything by Alison Balter or John Viescas
would be good.