Autonumber Corruption after each Compaction and Repair

[ions]

One table with an autonumber field becomes corrupt after each Compaction and Repair.

When I add a new record to the table the new autonumber is an already existing autonumber, hence, I get a duplicate value error.

I fix the corrupt table by importing it to a new database. (Access seems to recreate indexes / autonumber fields during an import). This always fixes the autonumber problem. I then import the properly working table back into my original database.

However, next time I Compact and Repair the same table becomes corrupt on the autonumber field.

Any suggestions?

 

[ions]

I could try that... it's Access 2002

 

[RuralGuy]

Here's a link to an explaination of the problem. Fixing AutoNumbers when Access assigns negatives or duplicates

 

[bugleboy]

How do I run the module to correct the autonum

Sorry, stupid question. I added the module to fix the autonumber issue, but now I need to know how to make it run!

Thanks,
bugleboy

 

[KeithG]

call the function from the module you add.

 

[RuralGuy]

It looks to me like you can execute it from the immediate window (^G).

 

[bugleboy]

Exactly how do I "call the function". Sorry, but I am a newbie.

 

[FoFa]

Easiest way is to create a macro with the RUNCODE command, and put the function name in it.
Then just run the macro

 

[RuralGuy]

Just open the immediate window and type AutoNumFix. It is a function and need not be called. You open the immediate window by looking at your code and pressing ^G (hold down the control key and press G).

 

[bugleboy]

OK, I opened the immediate window and typed "? AutoNumFix()" without the quote, nogo. I tried creating a macro to run AutoNumFix, but when I click Run it doesn't do anything.

 

[FoFa]

It doesn't do anything, or it has not feedback?
Question, is the function still named AutoNumFix?

 

[bugleboy]

No feedback. Per the instructions, I typed "? AutoNumFix()" in the Immediate field without the quotes.

 

[KeithG]

Did you make it a Private of Public function?

 

[bugleboy]

How do I do that?

 

[bugleboy]

Do I add Public to the beginning of the Function?

 

[RuralGuy]

Change the first line of the function to:
Public Function AutoNumFix() As Long
I believe it defaults to public but it couldn't hurt. Make sure the name of your standard module is *NOT* AutoNumFix. Name it anything else.

 

[RuralGuy]

FYI - from VBA help:

Remarks

If not explicitly specified using Public, Private, or Friend, Function procedures are public by default.

 

[RuralGuy]

Have you tried to compile the code yet? Debug>Compile

 

[bugleboy]

This is what I did:

1. Deleted existing module and macro so I could start over.
2. Created a new module exactly as the instructions said by doing the following:
- Copied the function, and pasted it into the code window.
- Chose References from the Tools menu, and verified the box was checked next to "Microsoft ADO Ext. 2.x for DDL and Security".
- From the Debug menu, I chose Compile to check there are no problems.
- Pressed Ctrl+G to open the Immediate Window. Entered:
? AutoNumFix()

When I compiled, absolutely nothing happened except the compile option grayed out.

I created a macro called macroAutoNumFix, chose the Runcode Action, then searched the Expression Builder and chose the Function Name. I then clicked the Run button (red exclamation point on toolbar), and again, absolutely nothing happened.

FYI, the module name is "moduleAutoNumFix" and the macro name is "macroAutoNumFix_run".

 

[RuralGuy]

I just added it to one of my db's and after keying ? AutoNumFix() in the immediate window it came back in a little bit with a "0". Just a numeric zero.