Autonumber start at 500

DBL

DBL

Registered User.
Local time
Today, 17:20
Joined
Feb 20, 2002
Messages
659
Is there an easy way to have the autonumber start at 500 rather than 1 when I compact a table that I've cleared out?
 
Set the format field to \500

IMO
 
IMO,

your method only changes the "display format" of the autonumber.

eg: 1 is displayed as 501

This becomes an issue when you reach > 99 records.

ie: autonumber 100 is displayed as 5100

The "\" format only displays a literal character

As for an easy way DBL, I am not aware of one.

I use an append query to insert a record (and then remove the record if it is irrelevant) .

You could set up a form to:
- activate the deletion of all table records (delete query)
- compact db
- append 1 record with PK500 (append query)
- Then reactivate same delete query as initial

But then again, you did ask for easy... so :confused:

HTH

Brad.
 
Thanks for those suggestions. I'm trying to incorporate this in to a split database that has already been deployed and has data in it. Therefore, I'm not keen to add additional tables or change the tables that are there. Is there anyway of setting the autonumber to start at 500 using VBA?
 
Why not import the data that you are trying not to interfere with? You can add rows containing existing autonumbers if you use an append query.
 
Thanks Pat. It's actually been deployed to someone else's machine that I don't have access too so I can't work on the backend tables. I had hoped to be able to send him a new frontend which could automatically start the autonumber at 500. The contents of the tables are archived at the start of each new year and the autonumber is reset at that point. That's when I would like it to start from 500.
 
If I weren't brain dead, I would have remembered this earlier. I did once (because of another problem) have to reset the autonumber seed in code. Here's the function:

Code: 
Function ChangeSeed(strTbl As String, strCol As String, LngSeed As Long) As Boolean
'You must pass the following variables to this function.
'strTbl = Table containing autonumber field
'strCol = Name of the autonumber field
'LngSeed = Long Integer value you want to use for the next AutoNumber

Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column

'Set connection and catalog to current database
Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn
Set col = cat.Tables(strTbl).Columns(strCol)
[COLOR=red]' sorry missed these when typing original post
col.Properties("Seed") = LngSeed
cat.Tables(strTbl).Columns.Refresh[/COLOR]
If col.Properties("Seed") = LngSeed Then
    ChangeSeed = True
Else
    ChangeSeed = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing
End function

Warning - I typed this rather than using cut and paste since I only had hard copy available. I promise it does work if there are no typos.
 
Last edited:
Excellent, thanks Pat.
 
DBL -

What would we do without Pat? Access becomes bearable with her incredible wealth of knowlege and her ability to explain it so that even I can get it.

Thanks Pat for all that you do for us.

Jack
 
Pat -

You are very welcome, but maybe you won't be glad to see me now... I tried the code and it does not change the seed of the autonumber field.

When I run the code it sees if the current seed is equal to the requested seed and if it is not then the SeedChange returns Flase and that is all that happens. I am very good at missing things so I would never challenge your expertise, but I can't get the code to work...

What am I missing?

Jack
 
Duh! Is my face red. I warned you that I typed this rather than doing a cut and paste and guess what? I missed the ONLY important line of code. Give me a sec if you're online. I'm going to go back and edit the code rather than fixing it here.
 
Pat -

I am here so do what works best for you. And your face need not be red. I have learned more from you than all others combined. I am in your debt!

Jack
 
Pat -

Amazing what 2 lines of code can do. The code no works like a champ! Thanks once again for all that you do....

Jack
 
Thank you so much, both of you. I'm very grateful for all the time that you give to others on the forum, I certainly wouldn't be doing what I am today without all the help I've received from you both.

Dawn
 
Autonumber starting at a set value

Ok, I need to use this code, but what is ADOX.Catalog and how do I include it in my code? I am using Access 2000

Thanks!
 
The code works as it stands. Be sure you have 'Microsoft ADO Ext. 2.7 DDL and Security' checked in your references.

Jack
 
Hi Pat, sorry to come back to you about this again. I can get the code to work perfectly when the database isn't split but as soon as I split it (and I'm going to use the frontend to update an existing split database that already has data in it) the code stops at "col.Properties("Seed") = LngSeed" and I get an invalid argument error message. Any suggestions?
 
Last edited:
You need to set the connection to the be db. I haven't done this with ADO so I can't post a sample. You'll need to look up connection in help
 
The end to the saga! Here is the final code that is now working for the split database.

Function ChangeSeed(strTbl As String, strCol As String, LngSeed As Long) As Boolean
'You must pass the following variables to this function.
'strTbl = Table containing autonumber field
'strCol = Name of the autonumber field
'LngSeed = Long Integer value you want to use for the next AutoNumber

Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column
Dim strCnn As String
'Set connection and catalog to current database
Set cnn = CurrentProject.Connection
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C:\program files\Douglas Penman\show database be.mdb"
cat.ActiveConnection = strCnn
Set col = cat.Tables(strTbl).Columns(strCol)
col.Properties("Seed") = LngSeed
cat.Tables(strTbl).Columns.Refresh
If col.Properties("Seed") = LngSeed Then
ChangeSeed = True
Else
ChangeSeed = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing
End Function

I had real problems getting the code to work, kept giving me error messages and then the wonderful and gorgeous Jack Cowley spotted that the backend had been converted to '97 (which I'd forgotten all about) and once we upgraded it to 2000 it worked perfectly.

Thanks again to everyone for their help with this one, I've learnt a lot!

D
 
You must log in or register to reply here.

Similar threads

J
Database has one specific table causing lock issues
Replies
4
Views
222
Pat Hartman
P
C
UPdate Query not working
Replies
11
Views
460
Pat Hartman
P
L
Solved Lock file errors on FE. Multiple access tasks in task manager.
Replies
2
Views
91
likecricketilovesit
L
B
When a bound form is opened, I need to change, via vba, the autonumber/primary key field from "(New)" to an actual new autonumber
Replies
11
Views
294
Pat Hartman
P
J
How to find client who has been provided service in more than one category
Replies
5
Views
304
johnny_swindle
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom