1) Copy the table to the Clipboard
2) Paste the table with a new name, but choose Structure Only.
3) Open the new table and change the Number to AutoNumber (you can do that
because the table has no data).
4) Paste the clipboard data again, but this time choose append to existing
table and select the new table name.
Your new table will now have an AutoNumber with all the original data.
Rename tables, and you're all set.
If you want to generate your own new number, you need to put code in the
BeforeUpdate event of the form:
Private Sub Form_BeforeUpdate(Cancel As Integer)
' If a new record
If Me.NewRecord Then
' Generate the "next" number
Me.ID = NZ(DMax("ID", "Encroachments"), 0) + 1
End If
End Sub
John Viescas, author
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
Running Microsoft Access 2000
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
For the inside scoop on Access 2007, see:
http://blogs.msdn.com/access/
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf
Of irelandmorro
Sent: Wednesday, May 10, 2006 6:07 PM
To: [email protected]
Subject: [ms_access] changing an existing number field (containing data) to
an autonumber field
I have an Access table with 564 records. One of the fields is
called "ID" and has a data type "Number". It has unique values up to
565. I want users to be able to add records to this table but I want
the ID to be generated automatically and to increment by one each
time. Since there is already data in this field, I can't change its
field type to "Autonumber". So I guess I'm looking instead at creating
a default value of Max+1.
The name of my table is "Encroachments". I have tried just putting the
following in the Default Value spot:
Max([Encroachments].[OldID])
However, even though this doesn't have the "+1" yet, it causes an error
message: Could not find field 'Encroachments].[OldID'
I have tried various other ways, but none of them has worked.
Yahoo! Groups Links
SPONSORED LINKS
| Microsoft access database | Database development software | Database management software |
| Database software | Inventory database software | Membership database software |
YAHOO! GROUPS LINKS
- Visit your group "ms_access" on the web.
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
