By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,508 Members | 1,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,508 IT Pros & Developers. It's quick & easy.

Auto Increment and Reset Number Sequence in a Table

P: 31
I have searched and could not find my solution to my problem.
I have a table that has a field that I want to start at 0001 and go to 1999. Once it reaches 1999 I want the field to reset to 0001. I am importing unique records and the date and number separates the records but some days there may be more than one import.
So if I set the field to auto number and compact and repair on exiting I have the possibility of duplicating the same date and number, which the database receiving the exported data will not except.
There is no user interaction with the numbers. It is a straight import and create a table and export.
Any suggestions?
Oct 30 '07 #1
Share this Question
Share on Google+
8 Replies


nico5038
Expert 2.5K+
P: 3,072
Perhaps my sample database gives an idea:
http://www.geocities.com/nico5038/xC...Insert2000.zip

Nic;o)
Oct 31 '07 #2

P: 31
Perhaps my sample database gives an idea:
http://www.geocities.com/nico5038/xC...Insert2000.zip

Nic;o)
Thanks Nic;o)
I looked at it briefly and it may be what I need. My database is pretty simple. The table is imported to one table and appended to another table with standard default data. That is where I need to insert the sequenced numbers a total of 4. then the data is exported. Thanks for the sample database. I am going to look at it closer today.
Regards,
Wayne L
Nov 7 '07 #3

nico5038
Expert 2.5K+
P: 3,072
Keep me posted :-)

Nic;o)
Nov 7 '07 #4

P: 31
Nico,
I have been trying to get the module to update my table and I seem to be running into trouble. I am using your sample but can not get the table to update when new data is added to it. Can I just use DMax on an autonumber field and have the number reset when it reaches a certain number?
Thanks,
Wayne L
Nov 8 '07 #5

nico5038
Expert 2.5K+
P: 3,072
Sorry Wayne, but you can't (or better shouldn't) update an autonumber field.
In your case with multiple runs to add data I would use the DMax() function combined with the unique identifier of the run (e.g. runnumber of the date imported when working daily) when appending data to the table.

Could look like:
Expand|Select|Wrap|Line Numbers
  1. Me.RunSequence = NZ(DMax("RunSequence","YourTable","RunID=" & Me.txtRun))+1
  2.  
The NZ() is needed for the first row when there's no entry with the RunID yet.

Nic;o)
Nov 8 '07 #6

P: 31
Nico,
Thanks for the input. I was looking at the DMax also. I am kind of new writing code. I just want to throw an idea towards you about the autonumber. My table starts with a number of 9000 (Now) and the number can never reach 10000. I have a compact and repair set up on exit which resets the number now but if the user goes in twice a day the number will be duplicated because the orig table is truncated before exit.
Now the solution to resetting the number. Can I set the MS Windows scheduler to compact and repair each day at 11:00 P.M. This will allow the user to do a days worth of transactions (which will never be over 1000) during the day and the number will restart new each day. comments?
Regards,
Wayne L
Nov 9 '07 #7

P: 31
Ok, I have read enough about autonumber not to use this for my output file. I am trying to use the DMax function that you have sited and i have inserted it into my form "BeforeInsert" It is not working properly. My numbers are still null in my table when new records are being added. I may not have the knowledge on the proper way to insert the DMax function.
Nov 12 '07 #8

nico5038
Expert 2.5K+
P: 3,072
When you start with an empty table, use in the Form for the Default value property of the Keyfield:
Expand|Select|Wrap|Line Numbers
  1. =NZ(Dmax("keyfield","tablename")) + 1
  2.  
Thus even with an empty table the number will work.

This does require a FORM as the default value in a table won't accept the DMAX() function.

Nic;o)
Nov 12 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.