Autonumber

When i delete some records in tabel in which primary key field is set for autonumber, and enter again in this tabel the autonumber starts from deleted numbers.

how i can set the autonumber start from 1.?

The simple answer is you can't.

The autonumber is set by Access and it doesn't reset to 1 just because you deleted the records. Running compact and repair facility in the Database Utilities section of the Tools menu usually resets this. However, this is not a practical solution and by no means guaranteed to work.

Usually if you need a specified numbering system you wouldn't use the autonumber data type but set the number to increment programmatically using vba behind the data entry form that populates this table. If you want to know how to do this let me know.

to add further

If u deleted ALL records then did a compact/repair, that should restart the numbering. Ive yet to see otherwise, but if u still have records there, it does not restart from the last record

There is also a way to restart the numbering...

Using Append or better Update query on the respective Autonumber...


Using this maner I control my autonumbering....

Be....en, hein...., yeah.... good...., sometimes it arrives that it generates a number that exists, but it's my fault ;)

:)

The simple answer is you can't.

The autonumber is set by Access and it doesn't reset to 1 just because you deleted the records. Running compact and repair facility in the Database Utilities section of the Tools menu usually resets this. However, this is not a practical solution and by no means guaranteed to work.

Usually if you need a specified numbering system you wouldn't use the autonumber data type but set the number to increment programmatically using vba behind the data entry form that populates this table. If you want to know how to do this let me know.

I want to learn how to do it. please let me know.

I have a table named tblReceive with a field [ReceiveNo] having the format "HPRec 07"00
Then I have a form named frmReceive with a textbox bound to
tblReceive![ReceiveNo]

I want the form to generate the ReceiveNo incremented by 1 everytim
a user adds a new record. I tried this code:

But it doesn't work. as soon as I opne the fomr in Add Entry mode
it always just shows the [ReceiveNo] Field as HPRec 0700.

It just does not add 1 to it. can you help me please?

The simple fact is that Autonumber is not intended to be used/manipulated by either the developer or the end user! Its meant to be used by Access for housekeeping (behind the scenes) operations! If you need a number to use for manipulating your records, you should follow Mary's advice above.

The simple answer is you can't.

The autonumber is set by Access and it doesn't reset to 1 just because you deleted the records. Running compact and repair facility in the Database Utilities section of the Tools menu usually resets this. However, this is not a practical solution and by no means guaranteed to work.

Usually if you need a specified numbering system you wouldn't use the autonumber data type but set the number to increment programmatically using vba behind the data entry form that populates this table. If you want to know how to do this let me know.

How do you do this? I'm stuck on a similar problem and I think this would help.

As for all you who may want to know this as well, look at the next post.

How do you do this? I'm stuck on a similar problem and I think this would help.

I finally learned how to do it.


nyx, heads up.


I wanted to have a customized autonumber.
This is a receiving number for all items received in a warehouse.
The format I wanted would be HPREC 070800001
Where in HPREC is a standard prefix for all received numbers.
0708 represents the current year and the current month which automatically changes everymonth (0709, 0710, etc)
and the 00001 is the number that automatically increments by one for every new record of a received item.
(this autonumber assumes that there is a maximum of 99,999 items received every month.)


To do this, I removed the autonumber field in my table and I changed it with a text field. I made that field the index key.


Then havning that table as the source of my form, i put this code on the on current event of the form.



How does it work?
First, the DMax() tells acces to find the largest number in the [ReceiveNo] field of the tblReceive table.
After finding it, the Right([ReceiveNo], 5) tells access to strip that
largest number of any characters from the left and only look on the last 5 characters of that largest number in the [ReceiveNo] field.
(ex: the largest number in my field is HPREC 070800001, then acces only looks at the 00001 part)

After locating the last 5 characters of the largest number, the + 1 adds one to it.

However, this is not possible if there are no entries in the table yet, meaning there is nothing to looks at the last 5 characters. That is why then the Nz() tells access that the largest number is 0. Therefore, 0 + 1 = 1 which is gonna be the new entry number.

then access assings that to my variable named NextNumber.

After finding that nextnumber, access now assigns this to the [ReceiveNo] of the form.

"HPREC " tells access to put HPREC and a space in front of the nubmers.
Format(Date, "yymm") tells access to look at the current year and month based on the computer settings and format it as yymm (0708 if it is august 2007). Then CStr() converts that into a string or text.

finally, Format(NextNumber, "00000") tells access to see the value of the NextNumber variable above and no matter what number that is, format it as a 5 digit number. (so if it is 1, then it would be 00001 and so on)
CStr() converts that to a string again.

the & sign puts them all together.
HPREC + 0708 + 00001

and there you go, your own customized autonumber.


Disadvantages:
1. Since this coding is made on the form, the autoassigning of the Receive Number will not work if you are inputting data directly into the table.

2. If the user clicks the UNDO button a lot of times, then the autoassignment of the ReceiveNo will be undone as well, therefore if the user accidentally undoes it and then makes entry again and save it, access shows an error saying that the primary key cannot be null.

However, you can fix that by doing an encoding on the save or close button of the form.



This just simply tells access that if the Save button is clicked, check first if the ReceiveNo field is empty, if it is, then autoassign the incremented ReceiveNo first and then save it. If it is not empty, then just save it right away.


I hope this helps you. :)

Wow thanks so much... going to try it right now.

How can I have it generate an autonumber if I want to restart the numbering each year?

Another disadvantage with this type of scheme, if used in a multi-user environment, is that two users could retrieve the same value with DMax() and thus you could have two records using the same ReceiveNo.

And please, stop calling these Autonumbers! Call them incrementing ID numbers or whatever else you like, but don't call them Autonumbers! Autonumber is an Access datatype; it is a long Integer, can only be generated by Access and cannot be manipulated by the user. Calling anything else an "autonumber" is being needlessly confusing.

Linq ;0)>

The simple answer is you can't.

The autonumber is set by Access and it doesn't reset to 1 just because you deleted the records. Running compact and repair facility in the Database Utilities section of the Tools menu usually resets this. However, this is not a practical solution and by no means guaranteed to work.

Usually if you need a specified numbering system you wouldn't use the autonumber data type but set the number to increment programmatically using vba behind the data entry form that populates this table. If you want to know how to do this let me know.

Hi MMCCarthy

I would love to know how to do this...I really don't like using autonumbers, they've given me so much trouble, but I need a way to assign account and order number (I'm a newbie),,,,so if you have an example please share.
thanks
Rosie

Another disadvantage with this type of scheme, if used in a multi-user environment, is that two users could retrieve the same value with DMax() and thus you could have two records using the same ReceiveNo.

And please, stop calling these Autonumbers! Call them incrementing ID numbers or whatever else you like, but don't call them Autonumbers! Autonumber is an Access datatype; it is a long Integer, can only be generated by Access and cannot be manipulated by the user. Calling anything else an "autonumber" is being needlessly confusing.

Linq ;0)>

that's right!!! i agree :)

How can I have it generate an autonumber if I want to restart the numbering each year?

The example i have given you restarts the number every month, and everyyear. :)
well, actually I am not sure. but I would assume so.

Missingling, could you give us some light to this ?

Hi MMCCarthy

I would love to know how to do this...I really don't like using autonumbers, they've given me so much trouble, but I need a way to assign account and order number (I'm a newbie),,,,so if you have an example please share.
thanks
Rosie

the post i have made above is a direct example of this.

how do i generate an incrementing ID that restarts at the beginning of every year?

how do i generate an incrementing ID that restarts at the beginning of every year?

the only way i can see it possible is to include the year in the incrementing ID.


remember? my incrementing ID is HPREC 070800001? the 07 part is the year part. now if you do the dmax with look up beginning from the 07, then as soon as it is 08 already, it restarts to 080100001.

does that make sense to you? :p

Hi, everyone.

My clever 2 cents to the discussion. :)

One dealing with numbering records via DMax function or something like this should do it with big care in at least two cases:

• table is being updated by multiple users asynchronously
• the field is PK related to FK(s) in other tables

Possible solutions:
anyway the field should be indexed with "No duplicates"

• multiple users, possible solutions:
  • add new record via ADO/DAO recordset locking the whole table
  • catch an "Index violation" error and try to generate a valid "autonumber" until it will be accepted
• when "autonumber" is being used as PK one can encounter such a situation when the last entered record was deleted while the value had been already entered to FK side table(s). Possible solutions:
  • set all relationships of the PK to [Enforce referrential integrity]->[Cascade delete]
  • check whether FK records are present, increment "autonumber" until no correspondent FK(s) found, BTW at this point Access Autonumber assumes FK(s) are present and never uses number once assigned

BTW if one wants just to enumerate records, then it could be easily done dynamically with SELECT query and some VBA coding.

the only way i can see it possible is to include the year in the incrementing ID.


remember? my incrementing ID is HPREC 070800001? the 07 part is the year part. now if you do the dmax with look up beginning from the 07, then as soon as it is 08 already, it restarts to 080100001.

does that make sense to you? :p

Yup
may have been a brain fart

I finally learned how to do it.


nyx, heads up.


I wanted to have a customized autonumber.
This is a receiving number for all items received in a warehouse.
The format I wanted would be HPREC 070800001
Where in HPREC is a standard prefix for all received numbers.
0708 represents the current year and the current month which automatically changes everymonth (0709, 0710, etc)
and the 00001 is the number that automatically increments by one for every new record of a received item.
(this autonumber assumes that there is a maximum of 99,999 items received every month.)


To do this, I removed the autonumber field in my table and I changed it with a text field. I made that field the index key.


Then havning that table as the source of my form, i put this code on the on current event of the form.

Expand|Select|Wrap|Line Numbers

1. Private Sub Form_Current()
2.  
3. If Me.NewRecord Then
4.   Dim NextNumber As Long
5.   NextNumber = Nz(DMax("Right([ReceiveNo], 5)", "tblReceive"), 0) + 1
6.  
7.   Me![ReceiveNo] = "HPREC " & CStr(Format(Date, "yymm")) & Format(CStr(NextNumber), "00000")
8.  
9. Else
10.   Me![ReceiveNo] = Me![ReceiveNo]
11. End If
12.  
13. End Sub

How does it work?
First, the DMax() tells acces to find the largest number in the [ReceiveNo] field of the tblReceive table.
After finding it, the Right([ReceiveNo], 5) tells access to strip that
largest number of any characters from the left and only look on the last 5 characters of that largest number in the [ReceiveNo] field.
(ex: the largest number in my field is HPREC 070800001, then acces only looks at the 00001 part)

After locating the last 5 characters of the largest number, the + 1 adds one to it.

However, this is not possible if there are no entries in the table yet, meaning there is nothing to looks at the last 5 characters. That is why then the Nz() tells access that the largest number is 0. Therefore, 0 + 1 = 1 which is gonna be the new entry number.

then access assings that to my variable named NextNumber.

After finding that nextnumber, access now assigns this to the [ReceiveNo] of the form.

"HPREC " tells access to put HPREC and a space in front of the nubmers.
Format(Date, "yymm") tells access to look at the current year and month based on the computer settings and format it as yymm (0708 if it is august 2007). Then CStr() converts that into a string or text.

finally, Format(NextNumber, "00000") tells access to see the value of the NextNumber variable above and no matter what number that is, format it as a 5 digit number. (so if it is 1, then it would be 00001 and so on)
CStr() converts that to a string again.

the & sign puts them all together.
HPREC + 0708 + 00001

and there you go, your own customized autonumber.


Disadvantages:
1. Since this coding is made on the form, the autoassigning of the Receive Number will not work if you are inputting data directly into the table.

2. If the user clicks the UNDO button a lot of times, then the autoassignment of the ReceiveNo will be undone as well, therefore if the user accidentally undoes it and then makes entry again and save it, access shows an error saying that the primary key cannot be null.

However, you can fix that by doing an encoding on the save or close button of the form.

Expand|Select|Wrap|Line Numbers

1. Private Sub cmdSave_Click()
2.  
3.      If IsNull(Me![ReceiveNo]) Then
4.         Dim NextNumber As Long
5.         NextNumber = Nz(DMax("Right([ReceiveNo], 5)", "tblReceive"), 0) + 1
6.  
7.         Me![ReceiveNo] = "HPREC " & CStr(Format(Date, "yymm")) & Format  (CStr(NextNumber), "00000")
8.      DoCmd.RunCommand acCmdSaveRecord
9.  
10.  
11.      Else
12.           DoCmd.RunCommand acCmdSaveRecord
13.      End If
14.  
15. End Sub

This just simply tells access that if the Save button is clicked, check first if the ReceiveNo field is empty, if it is, then autoassign the incremented ReceiveNo first and then save it. If it is not empty, then just save it right away.


I hope this helps you. :)

code: vb

Private Sub Form_Current()
If Me.NewRecord Then
Dim VarNextNumber As Long
VarNextNumber = Nz(DMax([ID], "tblCOLAs"), 0) + 1
Me![ID] = CStr(Format(Date, "yy")) & "-" & Format(CStr(VarNextNumber), "000")
Else
Me![ID] = Me![ID]
End If

End Sub

When I implement the above code I get the following error:
Invalid use of Null

Help....

code: vb

Private Sub Form_Current()
If Me.NewRecord Then
Dim VarNextNumber As Long
VarNextNumber = Nz(DMax([ID], "tblCOLAs"), 0) + 1
Me![ID] = CStr(Format(Date, "yy")) & "-" & Format(CStr(VarNextNumber), "000")
Else
Me![ID] = Me![ID]
End If

End Sub

When I implement the above code I get the following error:
Invalid use of Null

Help....

I encountered this before. just can't remember what caused it.

Have you set the properties of your [ID] as Text?
Everytime you see this message and hit Debug, which line
does it highlight in yellow?

can you post that line here so i can look at it?

I encountered this before. just can't remember what caused it.

Have you set the properties of your [ID] as Text?
Everytime you see this message and hit Debug, which line
does it highlight in yellow?

can you post that line here so i can look at it?

[ID] is set to text.

The line it points to is "VarNextNumber = Nz(DMax([ID], "tblCOLAs"), 0) + 1"

the only way i can see it possible is to include the year in the incrementing ID.


remember? my incrementing ID is HPREC 070800001? the 07 part is the year part. now if you do the dmax with look up beginning from the 07, then as soon as it is 08 already, it restarts to 080100001.

does that make sense to you? :p

Dear Hyperpau,

Can you please explain this further. I am just joining the forum today and found your instruction about the auto increase number code very useful. It works for me well, except that the function Format(date, "yyyy") did not produce any thing, i.e. blank.

Also I would like to know in the prefix, instead of having only one like HPREC in your example, I have 4, i.e. "BD", "UD", "BR", "UR" and all should start at 0001 each year. How can I do this please?

For example the first record of BD in 2007 should be BD07-0001, the first record of UD in 2007 should be UD07-0001

May thanks.

[ID] is set to text.

The line it points to is "VarNextNumber = Nz(DMax([ID], "tblCOLAs"), 0) + 1"

sorry for the VERY LATE REPLY. Been busy.
try putting some quotes there.
Change it to
VarNextNumber = Nz("DMax("[ID]", "tblCOLAs")", 0) + 1

tell me if it works or not. if not, I may have to try building one to test it myself using your format.

Dear Hyperpau,

Can you please explain this further. I am just joining the forum today and found your instruction about the auto increase number code very useful. It works for me well, except that the function Format(date, "yyyy") did not produce any thing, i.e. blank.

Also I would like to know in the prefix, instead of having only one like HPREC in your example, I have 4, i.e. "BD", "UD", "BR", "UR" and all should start at 0001 each year. How can I do this please?

For example the first record of BD in 2007 should be BD07-0001, the first record of UD in 2007 should be UD07-0001

May thanks.

should be CStr(Format(Date, "yyyy")) with Date having a capital D.
regarding different prefix, please let me know how would you like you dbase to assign this different prefixes? how does it know when to use BD, or UD, etc. What I can see is create different forms for these different transactions and change the prefix "HPRec " to "BD " and so on...

I have a correction to my previous posts. Tried to make sure so i checked and tested it myself. This will not reset to 1 every year. to do that, you have to strip the Max ID with its year prefix and put a filter to your dmax. hense it would be something like this:

If my increment format is
07001

Explanation

First, Left("[ID]", 2) asks the dbase to strip the increment field to only the first two characters from the left. Hence, if the record is 07001, only look at the 07 part.

Then =Format(Date, "yy") tells access to compare the number stripped above to the current year. Meaning filter the records to only looks at those IDs where the first two digits is the same as the current year.

After it filters the records, the DMax now asks to find the Max entry in that filtered records.

Then, If access does not find any record where the first two digit is the same with current year, the Nz part assigns the Max record as 0.

Then + 1 increments the Max number from the filtered records by 1.

Right(NextNum, 3) strips whatever value we had above to the last 3 digits only. CStr of this part converts it to a string since NextNum was declared as Integer.
Format(Date, "yy") adds the current year in yy format as a prefix to the number stripped in Right(NextNum, 3).

There you go, you now have a customized incrementing number that resets everyyear.

should be CStr(Format(Date, "yyyy")) with Date having a capital D.
regarding different prefix, please let me know how would you like you dbase to assign this different prefixes? how does it know when to use BD, or UD, etc. What I can see is create different forms for these different transactions and change the prefix "HPRec " to "BD " and so on...

Thanks a lot Hyperpau. I like your instruction so much as you explain very clearly.

1. I tried CStr(Format(Date, "yy")) and it returns with blank again. Do you know any reason for this? But if I tried Format(Now(), "yy") then its resulted in "07".

2. As for different type of transactions, i.e. BD, UD, BR, UR - I make queries based on current years and these type of transactions, then built 4 different forms on these 4 queries. In such a way, it only looks at a certain type of transaction and one year at a time. It works for me but I am not certain if there is anything wrong with such an approach?.

3. Now, I move to a very difficult task (for at least it is to me) and I hope you will be able to help. If I need to insert a record in between two record, the new record should have the current record's number and blank form to enter date, while those records with higher number should be shifted by 1 (increase by 1). Similarly for deleting record action.

For examples, I now have a 4 records:

1. HPREC 070001
2. HPREC 070002
3. HPREC 070003
4. HPREC 070004

I have to insert a record in between record 1 and 2, then the result should be:

1. HPREC 070001
2. HPREC 070002 (New Record)
3. HPREC 070003 (Record No2. before insertion)
4. HPREC 070004 (record No3 before insertion)
5. HPREC 070005 (record No4 before insertion)

Hope it is not to big for an ask from you.
Many thanks.

Hi, everyone.

My clever 2 cents to the discussion. :)

One dealing with numbering records via DMax function or something like this should do it with big care in at least two cases:

• table is being updated by multiple users asynchronously
• the field is PK related to FK(s) in other tables

Possible solutions:
anyway the field should be indexed with "No duplicates"

• multiple users, possible solutions:
  • add new record via ADO/DAO recordset locking the whole table
  • catch an "Index violation" error and try to generate a valid "autonumber" until it will be accepted
• when "autonumber" is being used as PK one can encounter such a situation when the last entered record was deleted while the value had been already entered to FK side table(s). Possible solutions:
  • set all relationships of the PK to [Enforce referrential integrity]->[Cascade delete]
  • check whether FK records are present, increment "autonumber" until no correspondent FK(s) found, BTW at this point Access Autonumber assumes FK(s) are present and never uses number once assigned

BTW if one wants just to enumerate records, then it could be easily done dynamically with SELECT query and some VBA coding.

Hi FishVal,

I want to use this 'Incrementing Number' scheme, but my concern is the "multiple users" asynchronously updating.
Can you provide a code example of how use the "ADO/DAO recordset locking the whole table".....I want to stay away from actual autonumbers, so I need to try ADO as my solution.

I will ensure that the PK relationships are set to [referential integrity] and the indexed field has no dup's set...

Also in my case I don't need to reset numbers at the beginning of each yr. the month and yr changing is sufficient for my scenario.

thanks
Rosie

Hi FishVal,

I want to use this 'Incrementing Number' scheme, but my concern is the "multiple users" asynchronously updating.
Can you provide a code example of how use the "ADO/DAO recordset locking the whole table".....I want to stay away from actual autonumbers, so I need to try ADO as my solution.

I will ensure that the PK relationships are set to [referential integrity] and the indexed field has no dup's set...

Also in my case I don't need to reset numbers at the beginning of each yr. the month and yr changing is sufficient for my scenario.

thanks
Rosie

Hi, Rosie.

Actually the 2nd method - catching index violation error is simpler and seems to be more reliable.

Here is an example.

Table name: tbl
lngAN, Long number, PK or Indexed(No duplicates)

Form module:

Hi, Rosie.

Actually the 2nd method - catching index violation error is simpler and seems to be more reliable.

Here is an example.

Table name: tbl
lngAN, Long number, PK or Indexed(No duplicates)

Form module:

Expand|Select|Wrap|Line Numbers

1. Private Sub Form_BeforeUpdate(Cancel As Integer)
2.     Me.lngAN = Nz(DMax("[lngAN]", "[tbl]"), 0) + 1
3. End Sub
4.  
5. Private Sub Form_Error(DataErr As Integer, Response As Integer)
6.  
7.     Const conIndexViolation = 3022
8.     If DataErr = conIndexViolation Then
9.         Me.lngAN = Nz(DMax("[lngAN]", "[tbl]"), 0) + 1
10.         Response = acDataErrContinue
11.     End If
12.  
13. End Sub
14.  

FishVal,

Thanks, I trust your opinion....I'll put your code in and run it,,,then I'll report back for benefit of others...My plans are to replace PK CustomerID in 'Customers' and OrderID in Orders (both have utilized Autonumbers in the last db), with this "incrementing number" routine...Currently each PK is used in two different forms, so that should be fine.

I've had my fill of utilizing autonumbers for these critical PK's.
Rosie

FishVal,

Thanks, I trust your opinion....I'll put your code in and run it,,,then I'll report back for benefit of others...My plans are to replace PK CustomerID in 'Customers' and OrderID in Orders (both have utilized Autonumbers in the last db), with this "incrementing number" routine...Currently each PK is used in two different forms, so that should be fine.

I've had my fill of utilizing autonumbers for these critical PK's.
Rosie

Hi, Rosie.

I don't want to force you in your decision, but you should be aware that this change may affect the other parts of your project.
For example the code mentioned in
http://www.thescripts.com/forum/thread697714.html
will need to be modified in the terms of the same logic of programmatical autonumbering. I'm pretty sure that it is not the only place in your project need to be revised. ;)

To tell the truth, my post you've mentioned was against (even aggressively against) using programmatic autonumber as PK. Ye, I'm using Autonumber fields as PKs and happy having no problems with it. Sure, one can use programmatic autonumbers to generate PKs but I still stay the price is not worth the "advantages". :)

Regards,

Fish

Hi, Rosie.

I don't want to force you in your decision, but you should be aware that this change may affect the other parts of your project.
For example the code mentioned in
http://www.thescripts.com/forum/thread697714.html
will need to be modified in the terms of the same logic of programmatical autonumbering. I'm pretty sure that it is not the only place in your project need to be revised. ;)

To tell the truth, my post you've mentioned was against (even aggressively against) using programmatic autonumber as PK. Ye, I'm using Autonumber fields as PKs and happy having no problems with it. Sure, one can use programmatic autonumbers to generate PKs but I still stay the price is not worth the "advantages". :)

Regards,

Fish

Hi FishVal,

My situation, I'm using autonumber in sseveral tables (Customers, Orders, OrderDetails, Shipping Methods, and a couple others.

My assumption is that I need to change my autonumber fields in each table to number fields (remove PK, after 'breaking' the relationships). Next I'll put in your code with the appropriate modifations and restore relationships (and PK) to my new 'number' fields. Then, I"ll re-import the tables with autonumbers; since I"m leaving the names the same OrderID, CustomerID, OrderDetailID, all should work.

Again, my assumption is that since the 'new fields' are number fields, it should still work with the imported tables (with saved OrderID, CustomerID's)..

Hi, Rosie.

I don't want to force you in your decision, but you should be aware that this change may affect the other parts of your project.
For example the code mentioned in
http://www.thescripts.com/forum/thread697714.html
will need to be modified in the terms of the same logic of programmatical autonumbering. I'm pretty sure that it is not the only place in your project need to be revised. ;)

To tell the truth, my post you've mentioned was against (even aggressively against) using programmatic autonumber as PK. Ye, I'm using Autonumber fields as PKs and happy having no problems with it. Sure, one can use programmatic autonumbers to generate PKs but I still stay the price is not worth the "advantages". :)

Regards,

Fish

Hi FishVal,

You're not forcing a decision,,,the problems with my db are.
I'm got the entire db ready to go except for one last problem.
When I clear the Main Order form (not the person's name,address, but clear the Product info) to start a new product order, I can't clear the control 'OrderId'.......because it's an autonumber.

Again, my assumption is that wtih your code instead, when I'clear ('new record'), that I'll get a new OrderID in the control when I save the form...???

My assumption is that I can change my autonumber fields in just 2 tables, Customers & Orders) to number fields (remove PK, after 'breaking' the relationships). Next I'll put in your code with the appropriate modifations and restore relationships (and PK) to my new 'number' fields. Then, I"ll re-import the tables with autonumbers; since I"m leaving the names the same OrderID, CustomerID and all should work.

Again, my assumption is that since the 'new fields' are number fields, it should still work with the imported tables (with saved OrderID, CustomerID's)..

Or am I missing something....I'm new so these assumptions are coming from a 'Newbie' presumption.
Rosie
BTW, thanks for commenting on my past Post situation,,,I really appreciate that. Now I've got to figure out if you're referencing my 'NotInList' routine requiring a change...(I think that was the post your referred to)...

Rosie

When I clear the Main Order form (not the person's name,address, but clear the Product info) to start a new product order, I can't clear the control 'OrderId'.......because it's an autonumber.

Hi, Rosie.

If this is the only reason then there is a couple of ways to work around without dealing with programmatical autonumbering.

Post your code to open new record as it looks now.

Regards,
Fish

Hi, Rosie.

If this is the only reason then there is a couple of ways to work around without dealing with programmatical autonumbering.

Post your code to open new record as it looks now.

Regards,
Fish

Great....I will put it here....but let me give you some backup information...

I have 1 main Order form with 2 subforms that I have bridged together.

Subform-1 ('Order Details Subform')supported by the Order Details table; used to hold data for products. Child link is OrderID, Master link is a 'bridge' control.

Subform-2 ('Order Ship Details Subform') supported by the Order table; holds Shipping and Order information.Master/Child link is CustomerID.

Of course there's the main form supported by the Customer table...Has the Combo box for selecting an existing customer. After a selection the persons data comes up,,,,(name, acct id, billing address, city, state...you get the picture), including their last order information (last Order ID, product name, unit price, quantity, etc.)

The clear button is on the Main form...I want it to clear only the OrderID, control called 'customerorderid' (from the last Order) and the other order information in Subform number 1.

Here goes:
There's obviously a lot I don't know, but I've been playing around with this thing for weeks,,,,

be forwarned...my OrderID is a darn Autonumber....
The code I have in there now works, but after you clear the control,,,,it does not give you a new "autonumber"...it actually nukes the control...it's weird...

thanks for all your help.
Rosie

To: FishVal

One more mention...the word 'nuked" defined:
The control (customerorderid) stops working all togeher. The next customer I select, NO customerorderid (OrderID) comes up with their product information. The control is blank. I've tried this, but it does nothing:

thanks,
Rosie

Hi, Rosie.

If this is the only reason then there is a couple of ways to work around without dealing with programmatical autonumbering.

Post your code to open new record as it looks now.

Regards,
Fish

I'm sorry,,,I must have been sleepy and not reading your post correctly...I inadvertantly posted the code for clearing the form.......
this is how the code looks now for opening a new record looks:

'custname' is a combo box; using Row/Source/Type query
and the Row Source is:
Additionally there's an AfterUpdate event.
On Load:
Record Source for the form is called trial-qryOrderSource (based only on the Customer table) filtered by CustomerID.

I think this is what you're looking for.
thanks
Rosie

Hi, Rosie.

I apologize having "ignoring" you posts, was very busy.
I've read them carefully but this didn't make much sense.
I think I should take a look at your db.
Plz send me a copy of your db with some minimal data necessary for testing purposes (3-4 records in each table). Don't forget to zip the copy first. I will temporarily open my vCard for download.

Hi, Rosie.

I apologize having "ignoring" you posts, was very busy.
I've read them carefully but this didn't make much sense.
I think I should take a look at your db.
Plz send me a copy of your db with some minimal data necessary for testing purposes (3-4 records in each table). Don't forget to zip the copy first. I will temporarily open my vCard for download.

OK...that's fine...I'll definitely zip it now. thanks FishVal,,,

BTW, I'm aware you're busy, I do really appreciate the help


Ignore the email msg....I've downloaded your Vcard and sent the file...
Rosie

Like the codes i posted here, the number will reset every hear if you include the year as a prefix to it. look at the posts I have in this topic. But please do follow fishval's important reminders.

what i could suggest is to still put an autonumber field for creating relationships. and then just add another field for this customized incrementing number. just hide the autonumber from your users (do not include it in your forms.) :)