Autonumber start at 500

[DBL]

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?

 

[IMO]

Set the format field to \500

IMO

 

[bradcccs]

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

HTH

Brad.

 

[DBL]

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?

 

[DBL]

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.

 

[DBL]

Excellent, thanks Pat.

 

[Jack Cowley]

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

 

[Jack Cowley]

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

 

[Jack Cowley]

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

 

[Jack Cowley]

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

 

[DBL]

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

 

[tsteinh]

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!

 

[Jack Cowley]

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

Jack

 

[DBL]

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?

 

[DBL]

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

 

[Jack Cowley]

Pat -

I tried various versions of the code that Dawn posted but the only way I could get it to work was by using the two variables. I tried your code but it balks at the 'Set cnn = ...' line of code with a Type Mismatch error.

We both sincerely appreciate your assitance and willingness to give of your time and expertise. Your code may in fact work, but I can't get it to do what it is supposed to do....

Thank you very much for your suggestion.

Jack

PS. I actually found the solution of using the two variables in Access help as I had tried variations on the basic code you suggested above without success...

 

[Jack Cowley]

You are more than welcome. Just because I failed does not mean that your code won't work. It just means that I could not get it to work....

Thank you for all your efforts on Dawn's and my behalf.

Jack

 

[servoss]

A couple follow-on questions...

I, too, am very interested in being able to resent the autonumber seed to a specific value.

I have coded the ChangeSeed funtion as described by DBL in the post dated 04-05-2003 07:21 PM, but it isn't working quite right and I have a couple questions:

1) I have checked a reference library that seems to be the right one, but please verify. I have checked "Microsoft ADO Ext. 2.5 for DDL and Security" - this seemed to be the most logical selection, but I want to be sure, of course.

2) I notice in the code, there is a specific path referenced (the value of strCnn). I assume this is the path to the database within which the auotnumber field to be changed resides. Therefore, I changed this to point to my database, of course. This, too, seems to be ok, however I get an error when I execute the function (see attached error messagebox) that seems to indicate that I have the database open and therefore cannot change it. Am I interpreting this correctly and if so, how do I get around it? The function is called from one of the form's VBA procedures.

3) This probably ties into the last question, but could you explain what a split database is and how/why it is useful?

As always, I appreciate you assistance.

Tom

 

[Jack Cowley]

1) My version reads ""Microsoft ADO Ext. 2.7 for DDL and Security" but yours should still work.

2) Here is the code to change an autonumber in the current database with tables attached:

Function ChangeSeed1(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)
col.Properties("Seed") = LngSeed
cat.Tables(strTbl).Columns.Refresh
If col.Properties("Seed") = LngSeed Then
ChangeSeed1 = True
Else
ChangeSeed1 = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing
End Function

3) From Access Help "You can put only the tables on a network server, and keep other database objects on users' computers. In this case, the Access database's performance is faster because only data is sent across the network. Also, users can customize their forms, reports, and other objects for their individual needs and preferences without affecting other users.

You can separate the tables from the other database objects by using the Database Splitter Wizard."

For more information search the MS KB on the subject of spitting a database.

hth,
Jack

 

[mtairhead]

It's always the stupid one...

Sorry for being an airhead, but I cannot get Pat's code to work. It's probably because I have no formal training....It's very much because of that.

I attached a sample database, with a single table and the module in question. Can anyone help a babbling buffoon?