Removing unused indexes with VBA

[LaBelette]

Is there a way to remove the unused indexes without using Compact and repair ?

My problem is that I create a new record on a form. The form is in 'data entry' mode, and the user has the choice to press 'Add' to save the record and close the form or 'Cancel' to close the form without saving.

But when he press 'Cancel', my primary key (an autonumber) get incremented. When I compact and repair, it goes back to the max value + 1, which is perfect. I want the same effect, but just for that field.

 

[llkhoutx]

You shouldn't be getting anything "new" following a repair and compact. What are you getting?

An incremented index only comes with a new record. What are you doing to get an incremented index.

 

[LaBelette]

I create a new record and delete it.

 

[llkhoutx]

Number fields in tables have default values of 0 when created in the design view, unless specified otherwise and in which event, moving/going to a new record actually creates a record.

An autonumber index does not update on a new record until data is entered in some field; defaults values cause the autonumber to update.

I had your problem (unwanted "empty" records) and now use no default values for table fields, absent some compelling necessity. I haven't found that necessity yet.

However, I often set field values, especially user and date stamping on the form BeforeUpdate event.