Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old October 12th, 2005, 01:15 PM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default AutoNumber field

Hi All,

Is there any way that I can enter the number into AutoNumber field in Access database?

Thanks,

-Tulin

  #2 (permalink)  
Old October 12th, 2005, 01:20 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Why are you wanting to enter a number?

Are you trying to set the number it starts from?

or

Are you trying to INSERT a new record?

If it is the former, goto the table design, and set the initial value. If the latter, use the format INSERT INTO tblName (Field1, Field2) VALUES (Value1, Value2) - Just don't specify the autonumber field and it will insert it automatically.

Hope that helps - if not, elaborate a little more, and we'll see what we can come up with.

Mike

Mike
EchoVue.com
  #3 (permalink)  
Old October 12th, 2005, 01:47 PM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Let me specified my problem.

I have 2 tables:

TableA TableB
I II III IV I II III IV
1 R T G 3 G Y U
2 J S M
4 U C N

I would like to recall/add Row 3 from TableB to TableA. Column 1 is formatted to AutoNumber in TableA. Whenever I tried to enter row 3 fields to TableA, Access generates new number in Column I automatically. My question is that is there anyway that I can enter Row 3 from TableA to TableB without loosing 3 in ColumnI.

Thanks,



  #4 (permalink)  
Old October 12th, 2005, 01:58 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

That makes more sense. I am afraid there is no way of doing this with the Autonumber field. You could try and cheat to get the number included, but if you have a lot of data, this may be prohibitive, and if the autonumber field is referenced as a foreign key from other tables, this will break that relationship.

To cheat it, just add the new row - it will probably show up as 5 in the Autonumber field. Then goto the design view of the table, and delete the autonumber field. Save the table, close it, and then reopen it in design view, add a new field of type autonumber and Access will reassign the numbers - hence 4 will become 3 and the added row will become 4.

Not sure if that is the best solution, because it may be best just to live with the missing 3.

Mike

Mike
EchoVue.com
  #5 (permalink)  
Old October 13th, 2005, 08:35 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

You're not understanding autonumbers, primary key, and foreign key very well. Primary keys are usually set to autonumber. That number does not mean anything to the user. It's the computer's way of relating data in Table A to other tables, forms, and reports.

That means both Table A and Table B have a primary key set to an autonumber field. This number means nothing to you; you never see it. If you want something to mean something, you need to create your own Long Integer field (not autonumber) in both, like lngRecNo.

TableA
I lngRecNo II III IV
123456 1 R T G
123457 2 J S M
123458 4 U C N

Table B
I lngRecNo II III IV
234567 3 G Y U

Combining tables, you get Table A as

I lngRecNo II III IV
123456 1 R T G
123457 2 J S M
123458 4 U C N
123459 3 G Y U

Notice the autonumber doesn't matter; it's just giving the record a unique identifier. You're more interested in the record number. If you were to put those records in a report and sort them by record number, you'd get all of them you wanted. Record number IS something you look at.

This is a very simple example. Look up help in normalizing a table, primary key, and foreign key.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
  #6 (permalink)  
Old June 6th, 2006, 03:44 PM
Registered User
 
Join Date: Jun 2006
Location: , , Finland.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

There might be an easy way to add your tableB to tableA.
Create a new query: select tableB and all its fields to the query.
In the design view of the query, change the query type to an append query, from menu: Queries – Append query.
You will be asked the name of the table where to append the records, in your case tableA.

Check the query before you run it (with View – Table) to see that all the records of tableB are selected.
Last, run the query: Query – Run (or use the red exclamation mark).

If tableA has a primary key, and there are same values in the same field in tableB, these records will not be added. You will have a report of these.

Rita


Similar Threads
Thread Thread Starter Forum Replies Last Post
Retrieve Autonumber field and update it carrie09 Access VBA 3 November 2nd, 2007 07:32 AM
Determine next autonumber field value U.N.C.L.E. SQL Server ASP 2 February 22nd, 2007 04:01 AM
Hiding Autonumber Field Brendan Bartley Access 4 November 14th, 2005 11:34 AM
Autonumber display in form field ianc Access 2 January 14th, 2005 07:11 AM
Deriving an Autonumber field paulkholt Pro VB Databases 0 August 20th, 2004 10:51 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.