Hi All,

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

Thanks,

-Tulin

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

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,

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

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

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