Autonumber Tricks?

Apr 17, 2007

I apologize if this has been covered, I couldn't find it when I searched.

Access 2003:

I am building a new access database from the remains of an older database that was not Microsoft based. For audit purposes, I need to maintain 2 years of back history data from the previous database. After getting an Excel dump, arranging tables, etc I encountered a problem.

My primary key for the main table is a number, starting at 5000+ and ranging to 6000+ for current data, with gaps in between. I want this primary key to be set to autonumber starting from the last entry (which happens to be 6420). I know there is a way similar to setting the start value for autonumber via append query that will 'trick' access into changing the field settings from number to autonumber despite the table being populated, but I can't for the life of me remember how to do it. Any help would be appreciated.

View Replies

Tricks And Tips With Exporting Tbl/qry To Text

Jan 4, 2007

This is not a question.

I have recently been playing with exporting a query to a delimited text file and have learned some things along the way. Some of this is bound to be old-hat to some of you. However, I thought that it might be useful to post a summary of what I've learned for the benefit of others, like me, who may face some of the frustrations I've been dealing with.

Firstly, in order to export a query or table to a text format other than the default csv, you need to create what is called an 'export specification'. As has been mentioned elsewhere (http://www.access-programmers.co.uk/forums/showthread.php?t=59828&highlight=export+wizard)on this forum, you do this by clicking on the advanced button on the 'Export Wizard'. Pick the delimiter you prefer, click save as, and save the specification with a name that you can remember.

To use the specification in an export, you use the specification's name as the second argument in the DoCmd.TransferText method. (See access help for more detail)

However, things can go wrong!

Firstly, on some occasions, the export wizard fails to open thereby preventing you from accessing the specifications you have created or making new ones. This happened to me and it turns out that the reason was that some code I was running got interupted at one stage and DoCmd.Setwarnings was set to false when it happened. This prevents the export wizard opening for whatever reason.

The solution is to create a sub that sets DoCmd.Setwarnings to True and run it. (Credit to BobLarson here (http://www.access-programmers.co.uk/forums/showthread.php?t=37696&highlight=export+wizard))

Secondly, you may get an error message saying 'too few parameters...expected #' where the # represents a number.

This problem seems to be caused by using a control in a form as a criteria in a query that you're trying to export.

One solution is to simply get rid of the form references in the criteria of the query (Credit Jon K (http://www.access-programmers.co.uk/forums/showthread.php?t=54237&highlight=export+wizard))

But if you need to keep the criteria reference like I did, the best workaround I came up with was to enclose each reference to a form control in the criteria of the query with an Eval("").

Thus, if I were wanting to use a control called 'mycontrol' on a form called 'myform' as a criteria, I would use Eval("[Forms]![myform]![mycontrol]") instead of just [Forms]![myform]![mycontrol] in the criteria line of the query.

Thirdly, you may encounter an error that helpfully says 'Invalid Argument'.

This is due to the presence of a numerical field in the table/query that has its fieldsize property set to 'Decimal'. This is a known bug with access 2000 and solutions can be found at Microsoft's knowledge base (http://support.microsoft.com/kb/263946).

In my case, I resorted to changing the fieldsize properties to Double instead of decimal, and deleted/re-created my export specification after the change and this solved the problem for me.

I hope this post is helpful for others.
Cheers! :D

View 1 Replies View Related

Controlling Autonumber Field - Start Autonumber From 1 Everyday (Composite Key)

Nov 21, 2013

I have two tables linked to each other in one to many relationship. Instead of auto number, the date and shift (Text) is being used as the primary keys (Composite Primary Key). Here is the tables structures,

Payouts Table:
Date: Primary Key
Shift (Day or Night) : Primary Key

Bills Table:
Date: Primary Key
Shift (Day or Night): Primary Key
Autonumber: Primary Key

The tables Payouts and Bills has one to many relationship. One payout row can have many bills. The problem is that I want to start the Autonumber in bills table everyday from 1. As date and shift are different for every day so even if i start bills from 1 everyday, it wont make same primary key. I can do it manually but I want to make it automatically.

View 4 Replies View Related

AutoNumber

Apr 20, 2006

Hello,

I have a question. I would like to be able to have an autonumber in the main form be applied to text boxes on the subform. So If I enter a name in the main form, an autonumber is generated, then that number is automatically entered into the boxes in my subform. My subform is a datasheet view with many records. I want all the records to have the autonumber generated in the main form. Is there a way to do this. Currently I have the boxes in the subform retrieve the autonumber by setting it equal to the autonumber text box on the main form. I would think that there would be an easier way to accomplish this seemingly simple task.

Thanks,

Adam

View 5 Replies View Related

Autonumber

Feb 26, 2005

hello,
I am using SQL INSERT INTO where I insert values from my form Me!value1 etc
I have an autonumber in the table where I am using INSERT INTO, and when I exclude the autonumber it says that the number of columns does not match. I tried assigning a number manually in SQL and it works fine, but I can't use autonumber anymore. Is there a way to use autonumber and still do INSERT INTO or is there a way I can find the highest number and put the next highest in there through VBA?

Thanks in advance

View 2 Replies View Related

ID Autonumber Gets Set As 5E+08

Mar 7, 2005

In the main table of my d/base (just testing) - when a new record is entered using a form, the Autonumber Key ID becomes 5E+08.

Whats that imply ? There are some One-To-Many sub tables running off the main table, but I can't see how they could cause a problem.

NoVoiceLeft

View 4 Replies View Related

Autonumber

May 26, 2005

How do I assign an autonumber to a table after the table is created. I know it ask after you create a table, but I answered no. Now I want it to assign each record an autonumber?

Thanks

View 1 Replies View Related

ID (Autonumber)

Oct 9, 2005

Hi:

In the table,

I set ID (auto number),
How large the number? What is the limit number?
After it used, will it set back to 1 again?

Please let me know, thanks

Thanks a lot. Thanks.

View 2 Replies View Related

Autonumber

Oct 13, 2005

Can any body help please

Is there any way you could renumber the autonumber, I have deleted some records in my table and the numbering orders have a gap and I would like the numbers to rearange automatically as the record or records are deleted.

Thank you.

View 2 Replies View Related

Autonumber

Jun 2, 2006

Hello everybody,

I have a problem which I couldn't solve.
I have two tables. There're relationships is one to many (1:N).
The first one is Order and the other one is OrderItems.
Order(order_ID,...) and OrderItems(order_ID, lineitem,...).
The compound key of the relation OrderItems is order_ID and lineitem). What I exactly want? I want for every new Order_ID to have restarting count of lineitem.

example:

Order_ID, LineItem
1 1
1 2
1 3

Order_ID, LineItem
2 1
2 2
2 3

Thanks in advance.

View 1 Replies View Related

I Want To Add An Autonumber To...

Jan 11, 2007

I have a fairly small database I recently built that includes a particular table in which I meant for the primary key to be an Autonumber field. Apparently I forgot to set it this way and it currently sits as just a Number field. There are nine tables in this database with a couple hundred records in the main tables.

Is it possible to change this field to an Autonumber at this point? I have attempted to change it and it wants me to remove all the relationships. It also seems to be necessary to copy all the records out before reseting and then copy them all back in or it will not let me proceed.

Will doing it this way work? Is there a better way to do it? Please advise. Thanks.

View 1 Replies View Related

Add Autonumber

Jun 5, 2007

Apologies if this has been asked before:

I have a small table in access 2003 with autonumbers. I managed to delete two records by mistake (autonumbers 23 & 24).

Is there any way to add them back in?? It is important as these numbers have been specific to those records for a couple of years.

Thanks in advance!

View 14 Replies View Related

AutoNumber ?

Jan 8, 2008

Good Morning,
Is it possible to have an autonumber field start at a given number, say 9999999 and count backwards?

I have a program that is generating badge ID numbers but I have a bunch that already exist and going backwards seems like the easiest way to mesh the 2.

View 3 Replies View Related

Autonumber

Apr 2, 2008

Hai all,

My name is Narasareddy. i am using the MS ACCESS.

i am creating one table in design mode that time its allow the autonumber.

problem is i am create the table in rum time that time its not allow the autonumber.

any body help pls

Thanks & Regards

Narasareddy

View 2 Replies View Related

AutoNumber

Jul 20, 2005

I know you can have Access create an Auto-number field when you make a table....

Is there any way (via. code, or gui) to create an auto-number field within a query?

Thanks,
Gary

View 3 Replies View Related

AutoNumber Help

Sep 13, 2004

Id like to know if there is a way to create an autonumber field via a create table query. ive been searching a lot but without any success. any help would be appreciated.

View 1 Replies View Related

Autonumber Help

Sep 14, 2005

Hello All.

I ahve 2 pages, one is a form and the other adds records to a database and prints the result out on the page which all work fine.

My problem is that I am unable to retrieve the autonumber created in the database and also display that.

Attached is my code and db, if anyone can help i would be very grateful.

Thanks

IanWillo.

View 9 Replies View Related

Autonumber

Feb 15, 2006

is there anyway i can edit data in autonumber, or work around?

thanks

View 4 Replies View Related

Autonumber Problem

Apr 28, 2005

The record I was using as a key changed from an incremental to a random number. When I try to change it it won't allow me. I created a new project and imported the tables from the old one but all the attributes followed.
Short of starting from scratch, what can I do to change back to incremental autonumbering.

View 6 Replies View Related

Specialized Autonumber

Jun 2, 2005

I am creating a database for my company to register projects. And each project needs to have a unique Project ID number, which (due to the conformity of another software tool) must be 4 characters long. The first character is a specific letter based on the group the project is created by, followed by a 3 digit number. So, for example, one of the group code letters is C, so the first "C Project" would have the ID 'C001'.

I want my database to assign these automatically after selecting the group.

Any ideas?

View 2 Replies View Related

Autonumber Reset

Nov 1, 2005

Hi,

I have a autonumber function in my form, but i have deleted all the records and want to start again but the autonumber has not reset back to 1, is there a way to do this??

Thanks in advance.

View 5 Replies View Related

Not The Same Autonumber Problem

Dec 16, 2005

I have an access database that I have used autonumber to assign order numbers for several years. I am now wanting to split the database and it is causing a problem with the order numbers. My situation is different than other posts because I delete orders from the table when I assign serial numbers, but I still do not want to use that order number.

Example:
Enter order number 1000
Enter order number 1001
Assign a serial number to Order 1001 and delete that record.
Enter a new order, need order number to be 1002

Because I delete 1001 but still do not want to use 1001, I can't use any max number function that I've seen in some other posts.

Could I make a table to somehow keep track of the next order number. I am not very knowledgeable about access so please bear with me.

View 4 Replies View Related

Big Trouble With Autonumber

Mar 27, 2006

Hi

I have genreated an autonumber in my table, at this is used as key. after importing 135 records it starts to jump 48 records before it creates a new. This means that with every record i import after 135 i have a space of 48 between each. So what sort of thing can cause this? and even better how do i fix it?

Best Regard
Roald

View 2 Replies View Related

Setting AutoNumber

Jul 18, 2006

How do I change an AutoNumber that will output 1, 2, 3 to output 0001, 0002, 0003??

Thanks
Jessie

View 11 Replies View Related

Alter Autonumber

Sep 27, 2006

if i create 100 records with autonumbers as primary keys (record_id)

then deleted 30 at random

could i update the record ids so they become 1-70?

View 4 Replies View Related

Newbie Needs AutoNumber Help

Oct 7, 2006

I am trying to change my autonumber field to start at 2000500 for a workorder ID instead of 1. I have read the instructions but still don't sem to understand. Can someone give a more detailed set of instructions than is currently in the online access help?

View 3 Replies View Related