Solved

Force Autonumber to produce a bigger number

Posted on 2000-03-18
46
450 Views
Last Modified: 2009-12-16
So I made a database using the access wizard (2000) and now got the problem that it gives me customer number like 1,2,3 which really doesn't look good. The probelm is that I want to force the autonumber in the order table and the one in the customer table. Is there a rule I can enter? How can it be done?
0
Comment
Question by:BRAUN
  • 13
  • 9
  • 9
  • +4
46 Comments
 

Expert Comment

by:FWAllan
Comment Utility

Execute the following from SQL text view


Insert into <table>
( <autonumber_col> , any other fields that are not null/validate and the default value not allowed)
VALUES
(<starting number -1> , remaining fields)

OR using the QBE grid

If you have 1 record, copy all the fields, alter the update of the autonumber with 1 less than starting number and add a where cluase matching a value already in the database.




0
 

Expert Comment

by:FWAllan
Comment Utility

To be a little more verbose

Open the QBE

Select your table

Change the query type to an update.

In the prompt for what table, reselect the same table again.

select all the fields in the table except the *, drag these to the field list.

Switch to table view, use the first autonumber value in the table.

Switch back to QBE.

In the autonumber field, remove the append to.

Immediatly below, in the where part, enter the number you got from the table.

In the QBE where the table is displayed, select the autonumber field and drag it to the field grid.

Change the field line to the number you want to start from minus 1.

Execute.

Delete the record you have created.

0
 

Author Comment

by:BRAUN
Comment Utility
are you sure you are talking about  microsoft access 2000 (office premium editon) because I cant find the QBE or the sql view...
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
The autonumber is there to automatically give a unique key. You shouldn't bother what the contents of the key are.
(Especially as you don't have control about his contents!)

When you want a "controlled" key, you should:
1) change the fields from autonumber to numeric or
2) Add your own customer number.

However all related problems for keeping keynumbers are yours.

A shortcut solution could be to add an extra own customer number and store the autonumber +1000 (or something like that) in it. However when adding new row's you will have to make sure that this field is added.
0
 

Author Comment

by:BRAUN
Comment Utility
For me it is just important that the customer doesn't get a bill saying invoice nr "3"
0
 

Author Comment

by:BRAUN
Comment Utility
and how do I make that setting autonumber +1000 as a new row???
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 50 total points
Comment Utility
I think it would be important to make sure that the invoice number is unique and can't be changed.

As the autonumber can change (after table reconstruction), I wouldn't use it for this purpose.
(When you are going to distribute your database and use replication, Access is generating a random autonumber and does no nice adding up anymore!)

Just start with a very basic question:
Why do you need an invoice number?
When you are making an application for a not to large business, it will be unusual to have multiple invoices to the same customer on the same day, so just printing the customer name and invoicedate as reference should do.
To be absolutely sure that you can uniquely trace them all you could add a reference (and tablefield) containing the time (12:34:56)
(Internally you still can use the autonumber fields)

For the +1000 solution:
1) Add numberfield (like Auto1000).
2) Make a query containing:
UPDATE tblAutonumber SET tblAutonumber.Auto1000 = [autonumber]+1000;


0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
Hello BRAUN,

Another solution would be using Format() function (can be used to add leading zeroes) or Expression field (calculate new value) to show customer number like you want. The customer number is actually stored in the table 1,2,3.. but in forms/reports etc. it's shown as 1000, 1002, 1003 or 001000, 001001, 001002 or what you like.

You could make a query in your order form, which has a new field fCustomerNumber, which you can use in your form as a Control source.

Here's an example (customer form):
SELECT *,
       [Customer_ID]+1000 As fCustomerNumber
FROM tblCustomers;

(Customer_ID is your autonumber field)

And same with order form's query:
SELECT *,
       [Customer_ID]+1000 As fCustomerNumber
FROM tblOrders AS O,
     tblCustomers AS C
Where O.Customer_ID = C.Customer_ID;

If you update the customer number foreign key in order table you lose the data integrity - because autonumber field in customer table cannot be updated.

Hope this helps,
Paasky
0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
Also you can change the ControlSource of your customer number form textbox field:

=[Customer_ID]+1000

will show 1001, if the [Customer_ID] field actual value is 1.

Paasky
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Paasky, the proposal for a seperate +1000 customer number was intended to be an initial fill of the customer number field, after which it should be maintained by the application, as (to my experience) the autonumber can cause serious problems when used as a key that you would like to communicate with with your customers!
(See my replication remark and the fact that building the table from scratch resets autonumber, thus corrupting the invoice_id/customer_id !)

0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
OK. Sorry I read the above comments poorly before sending my suggestion.

I've also used own Sequence table in (Access) applications which has the fields "SEQUENCE_NAME", "CURRENT_VALUE", "INCREMENT" where I can maintain starting value and increment and get there new values with a function.

Paasky

0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
I do the same, even with a special form for the user to correct the startvalue when he wants to. (Ofcourse lower value's will not be accepted to prevent duplicates)

My user codes his ordernumbers like YYYY### and thus he can decide when to start numbering orders for the coming year when he wants)
0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
I have that form too, and also check value must be > MAX() !! :-)
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Check Help:
Change the starting value of an incrementing AutoNumber field

With new value AS Long Integer field in a table, Append this value to the AutoNumber field in original table.
0
 

Expert Comment

by:FWAllan
Comment Utility

QBE is query by design.  OK I haven't used Access 2000, but I would be suprised if this had been remove.

When you have the query design view, there is a drop down icon on the toolbar, or use the menu|view options to switch between design, datasheet and sql view.

0
 
LVL 7

Expert Comment

by:Believer
Comment Utility
I happened to recall that it is possible to set/change the value of an autonumber field thru code, so I did some experimenting.  I started with Table1 which contains two fields cntPrimaryID (autonumber) and strValue (a string).

Here's a quick routine I wrote:
   Dim rst As Recordset
   Set rst = DBEngine(0)(0).OpenRecordset("Table1")
   With rst
      .AddNew
            !cntPrimaryID = 500
      .Update
   End With 'rst

Ran it against an empty table and it succesfully added a record to the table with cntPrimaryID = 500.
Changed 500 to 450 (in the code), ran it again and it added 450 to the table.
Went into the table, began keying a new record and noticed the new autonumber value was 502 (I think), and then I cancelled creating the new record.
Changed 450 (in the code) to 425, ran it, and it added 425 to the table.
Keyed another new record and noticed the new autonumber value was 504, and cancelled creating the new record again.
Compacted the database, went in to create another new record and Access used 501.

Conclusions:
1. You can change/set an autonumber value thru code (as long as it doesn't violate any key constraints).
2. Access keeps track, internally, of the next autonumber field.  If the new record is greater than the internal number, Access will update the internal counter.
3. If the new record is *less* than the next internal number, Access still increments the internal counter.
4. As most of you probably already knew, Access reset the "next autonumber to use" when you compact the database.

(Whew... it's easier to look at the tests I ran that read that concusion!  Oh well.)
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
'Here is a way:
' to make 1001 as a new number
' this is similar to my previous comment but execute the code in an button_click event.

CurrentDb.Execute "INSERT INTO theTable( theAutoField)  values (1000);"


0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
' Here is a more general way
' In a form you need:
'      text field: newAutoNumber
'      A button  : new_auto
' here is the new_auto_click event
'>>>

Private Sub new_auto_Click()
   If DMax("an", "a1") < newAutoNumber - 1 Then
      CurrentDb.Execute "INSERT INTO a1 ( an )  values ( " & newAutoNumber - 1 & ");"
   else
    MsgBox ("Not a valid new autonumber")
   End If
End Sub
'<<<
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
' What I found through experimenting
' You can re-enter missing autonumbers



'>>>
Private Sub new_auto_Click()  
      CurrentDb.Execute "INSERT INTO a1 ( an )  values ( " & newAutoNumber - 1 & ");"
End Sub
'<<<

'where newAutoNumber  is a text field on a form
'      new_auto is a button to click


' if the new value exisits, nothing happens.
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
' my table : a1
' autofield: an
' cut&paste :)
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
hnasr:

Just make a .mdb replicate.
Then inspect the value's of the autonumber field when adding new entries!

Done, then you will see why I advise no to use autonumber for codes you sent to the outside world.

As last remark:
Just try to copy two autonumber related tables with referential integrety to another .mdb . . . .  (feel the troubles?!)
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
nico5038,

"The probelm is that I want to force the autonumber ..." (BRAUN)

Acutally we are trying to open doors for the questioner.  We don't claim the ultimate solution to his problem.  Your comments are highly appreciated.

But do you really mean it to have referential integrety with autonumber related tables?.  I feel that the autonumber is just for the sake of having a key to a table with no other keys.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 54

Expert Comment

by:nico5038
Comment Utility
I always use it as a (for the user) hidden unique key. Normally you will find that users like the possibility to change a once registered key. In that case my internal key (an autonumber) is static and used for the relations, the userkey on the other hand may be  assigned any value by the user. I just check whether it's unique, but that's all. All my references in the database are still valid, as I use the internal key!
0
 
LVL 7

Expert Comment

by:Believer
Comment Utility
I'm in agreement with nico here (except for the replication thingy...lol)
I have long been in the habit of using autonumber for primary keys and parent/child relationships.  That brings up an interesting point relative to this question... I also turn on cascading updates!  Since the autonumber field could theoretically be changed thru code, I decided it's better to be safe than sorry.

Another advantage of the autonumber parent/child is storage space.  A long integer uses a fixed 4 bytes per record.  A text field uses 1 byte per character (?I think).  If you happen to have a multi-field key relating the parent to the child, it can get very expensive.  I'll never forget the 50mb data-only (no forms, etc.) database that I converted for a "power user".  The original used all text linking fields; the first step of my re-design replaced the text keys with autonumber keys and reduced it to 36mb.
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Believer is correct about the length of the difference.
However, it's not once, the difference is also counting when there is an index for the field. This also consumes space extra !
0
 
LVL 1

Expert Comment

by:MVietri
Comment Utility
Look.

I agree woth most of the above technical comments. However, I do understand that you want the invoice numbers to start at like 1000. This should be your primary key. Want to know the easy way?

Create 10 records.
Copy them
Paste them 9 times
Copy 100 records.
PAste them 9 times
Delete all records

Now the counting will start at way over 1000. there you go. EASY.
0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
MVietri,

you could do the same with the following code:

Public Function AddRecords(HowMany As Long, TableName As String)
Dim l As Long
Dim rst As Recordset

     Set rst = CurrentDb.OpenRecordset(TableName)
     With rst
         For l = 1 To HowMany
            .AddNew
            .Update
         Next
     End With
     Set rst = Nothing

     ' remove records
     CurrentDb.Execute ("DELETE FROM " & TableName)
End Function

Usage example:
? Addrecords(999,"table1")

increases autonumber to 999 so the next value will be 1000.

hope this helps,
Paasky
0
 
LVL 7

Expert Comment

by:Believer
Comment Utility
mvietri: That's *easy*?  I like cut & paste code once & run it better... take your pick of all the code that's been posted already...
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
mvietri (and BRAUN ofcourse) to

This append-SQL does the same:
INSERT INTO tblAutonum ( autonum )
SELECT 1000 AS Expr1;

New records will get 1001, etc

It's even possible to "append" afterwards a lower value:
INSERT INTO tblAutonum ( autonum )
SELECT 100 AS Expr1;

After this and the autonumber will continue with 101 etc. However when an existing value is found, a message is displayed that the record can't be added due to a duplicate key value.....

Again proof that it's dangerous to fool around with this mechanism !

Just a selfcontrolled number as mentioned by paaky, believer and me is the safest way. The FORMAT and or query with adding +1000 is the quickest way.
0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
nico5038,

so it's possible in Access to insert particular value to autonum field and make it start from new position. Thanks for valuable information!

What happens if the next number 9999 and I try to insert value 10?

I am hoping M$ would add starting value and increment to properties of autonumber because so many people would like to change them. Or perhaps a customized sequence which could be used as field source (like in Oracle).

paasky
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Paasky,

The above mentioned sequence is what I did by using the append-query out of curiosity I tried a "lower" value.
And to my surprise Access had resetted the increment value too !

(This is under A2000, maybe your A97 acts different)

Your question:
So your 10 will insert if it's not there ad adding continues with 11!
0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
(I'm still working with A97)

so it's possible to "restart" autonumber - and cause key conflict when it reaches the 9999, if that number already exists, huh?
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
correct, doesn't it under 97?
0
 
LVL 7

Expert Comment

by:Believer
Comment Utility
This does *not* appear to be the behavior under 97! (re-read my comments dated Sunday, March 19 2000 - 11:47AM EST)
And I'm glad... it's scary to think that you could have autonumbers ranging from 100-1000, insert a 10, and Access starts adding new records with 11... what happens when it gets to 100?
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
As stated when an existing value is reached it will hangup on the insert of a duplicate key!

I tried the same sequence with the update-query under 97 and found out that it will insert lower values but the next new table row gets an autonum with the highest value + 2.
So somewhere it counts the INSERT into and a gap is created in autonumber with the number of records inserted!

So once again: Working with changes to an autonum field is tricky business!

BTW: A2000 also refuses to accept 2 autonum fields in one table!


BRAUN still able to catch up ?


0
 
LVL 7

Expert Comment

by:Believer
Comment Utility
nico:  Who??? Oh yeah, the guy/gal that "aksed" the question...lol!  Haven't seen him for a couple of days now...
0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
BRAUN, any news here? :o)
0
 
LVL 7

Expert Comment

by:Believer
Comment Utility
(paasky's on a clean-up mission!)
0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
See how much information this thread contains, unfortunately it will be lost soon.
0
 
LVL 7

Expert Comment

by:Believer
Comment Utility
Braun: Where the heck are you at?
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
<echo>

<echo

<ech

<ec

<e

<

0
 
LVL 7

Expert Comment

by:Believer
Comment Utility
"Stayin' alive, stayin' alive, woo hoo hoo hoo, stayin' ali-i-i-i-v-e...."
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Hi!
0
 
LVL 7

Expert Comment

by:Believer
Comment Utility
I'm a little tired of unanswered Q's that have been getting deleted.  Just the ones I'm involved in account for several hundred points...
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Hi braun,

Long time no see !

Still need an arrangement for the autonumber? If so, I've a sample DB with a function to assign autonumbers, both "single" or combined with a year, year/month or so.
Drop me a mail if interested (click my name for my profile with e-mail address)

Nic;o)
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now