473,320 Members | 1,987 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Auto Increment and Reset Number Sequence in a Table

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
8 12475
nico5038
3,080 Expert 2GB
Perhaps my sample database gives an idea:
http://www.geocities.com/nico5038/xC...Insert2000.zip

Nic;o)
Oct 31 '07 #2
Wayne L
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
3,080 Expert 2GB
Keep me posted :-)

Nic;o)
Nov 7 '07 #4
Wayne L
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
3,080 Expert 2GB
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
Wayne L
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
Wayne L
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
3,080 Expert 2GB
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

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

Similar topics

2
by: Tom | last post by:
I am trying to store information into a table that has an auto increment field. There is currently no data in the table. Using the code below I cannot insert data into the table. I get an error...
0
by: Earl Anderson | last post by:
KB Article Q140908 provided the following function to create an Auto Incrementing Counter: Function Next_Custom_Counter () On Error GoTo Next_Custom_Counter_Err Dim MyDB As Database Dim...
16
by: John Baker | last post by:
Hi: I know this is a strange question, but I have inherited a system where files are copied and records re auto numbered (as an index field) )frequently, and I am wondering how high the number...
8
by: JD via AccessMonster.com | last post by:
I am trying to create a field where the primary key field will produce JDP- 001; where the three letters come from the first name, middle intial and last name of my table. I want to auto increment...
3
by: Chris | last post by:
Before I started to create table, etc to track unique form field record number assigments I thought I'd check to see if there is now a better way to do this in .NET. I have a parent form (table)...
5
by: Paulovič Michal | last post by:
hi all, I have problem with SERIAL field type (or sequence functionality). I have table with three columns - ID, IDS, NAME. I want auto-increment IDS grouped by ID. Example: 1, 1, Ferdo 1, 2,...
13
by: S.Dickson | last post by:
I had an access database that i use as an ordering system. I have a form for entering customer details. When i add a new customer on the form the customer number is an auto number that appears when...
0
chumlyumly
by: chumlyumly | last post by:
Hello scripters - OS: Mac OSX Language: PHP w/ MySQL database I've created an insert page where a user inputs his info, which then goes to four different tables in a MySQL database. The...
3
by: Wayne L | last post by:
Ok now everyone has mentioned not to use auto number if it means anything to the user. My application uses the auto number for exporting only. I append the mastertbl column with my starting number of...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.