Changing Empty Field Format To AutoNumber With Macro

T

The Brown Growler

Registered User.
Local time
Today, 18:38
Joined
May 24, 2008
Messages
85
Hi,

I have used a data definition query to add a new field to a populated table and set the format of the new field to number. The new field contains no data but other table columns do contain data.

I would like to be able to change the format of the new field from Number to AutoNumber so that it auto populates but rather than do it manually I would like to change the field format from within a macro.

I have looked at the runcommand option available as a macro action but cannot find any method to make the change from within a macro. If I had some code in a module I would be able to use the OpenModule or possibly the RunCode action in the macro but my coding skills are nil.

If there are any solution or suggestions as to how I can go about changing the field format from Number to AutoNumber from within a macro I would be most grateful for any help.


Rgds
Growlos
 
AFAIK you cannot add a new autonumber field to a table that already contains data.
 
DCrake said:
AFAIK you cannot add a new autonumber field to a table that already contains data.
Click to expand...

Yes you can. I have just tried it using Acc2003

Code: 
Sub apr8()
Dim db As DAO.Database
Set db = CurrentDb
Dim sql As String
sql = "Alter TABLE czip ADD COLUMN MyID Counter "
db.Execute sql, dbFailOnError
End Sub
 
Galaxiom,

Thanks for the link.

The datatype needs to be AutoIncrement and then it should run OK. However, I have come across 2 issues when running the query, the first of which I resolved.

Firstly, I got the "File Sharing Lock Count Exceeded" error message which I resolved by editing the registry value upwards from the default 9500 to a couple of million.

Secondly, I have the "Not Enough Space On Temporary Disk" error message and I cannot recall how to resolve this one. I think it is to do with some buffer size but I am not sure where to locate the setting.

If I can resolve the 2nd issue then I think the query will run OK and create my autoincrement field in the table.


Rgds
Growlos
 
Sounds like it might be a very big table.

Maybe increase the size of the swap file.
 
jdraw,

Would you please be able to let me know how I would call up that code from the actions available to a macro?

Would I need to paste the code into a module and run the module from the macro?

My VBA skill is practically nil so any help with storing and running the code most appreciated.



Rgds
Growlos
 
jdraw said:
Yes you can. I have just tried it using Acc2003

Code: 
Sub apr8()
Dim db As DAO.Database
Set db = CurrentDb
Dim sql As String
sql = "Alter TABLE czip ADD COLUMN MyID Counter "
db.Execute sql, dbFailOnError
End Sub
Click to expand...


I pasted the above code into a module then checked it using the Debug/Compile option and got a "User Type Not Defined" error message from the 2nd line of code "Dim db As DAO.database"

I am using Office 2003 with the access file format set at access 2000 and have the following libraries ticked under References:

VBA
Microsoft Access 11 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library

Do I need more references to get round the "User Type Not Defined" error message?


Any help most appreciated



Rgds
Growlos
 
Yes you need a reference to Microsoft DAO 3.6 Object Library
 
jdraw,

Thanks for the tip re the reference, I now have the code in a module and it is OK with a check from Debug/Compile.

Do I need any declarations etc so that it will run, all I have is the exact code pasted into a module, no declarations or anything els.

Also, how do I get it to run, sorry for these possibly obvious questions but as I indicated earlier my VBA level of skill is next to none !


Rgds
Growlos
 
Is this a one time thing? Do you want to run it under some condition or what exactly?
 
jdraw said:
Is this a one time thing? Do you want to run it under some condition or what exactly?
Click to expand...

It will be a regular occurrence.

I have a macro that imports data from external sources and makes tables etc. For one on these tables I want to insert an autoincrement field AFTER I have imported the data. The whole process is run by a macro from a button on a form and I want the insertion of the autoincrement field to be activated from within the macro. There seem to be several options available as macro actions to run the code, ie, OpenModule, RunCode etc. All I need to do is to use the correct option to get the code to run and make sure that the code and declarations are OK before testing it.

There are no conditions, the table is just sitting there waiting for the autoincrement field to be inserted. There are steps in the macro after this point but once the auto increment field has been inserted the macro will process these as normal.



Rgds
Growlos
 
Last edited:
So if the vba adds the autonumber field to an existing table does it also populate the contents?
 
DCrake said:
So if the vba adds the autonumber field to an existing table does it also populate the contents?
Click to expand...

Yes, in my sample it numbered each record starting at 1 and incrementing by 1.
 
Brown Growler,

I don't use macros, But I did just try to see what is involved.

I removed the autonumber field from table czip.
I then tried the RunCommand macro passing Apr8() as the parameter. It failed, but said I must use a function.

So I redid the Apr8() which was a Sub, to a Function
Code: 
Function apr8()
Dim db As DAO.Database
   On Error GoTo apr8_Error

Set db = CurrentDb
Dim sql As String
sql = "Alter TABLE czip ADD COLUMN MyID Counter "
db.Execute sql, dbFailOnError

   On Error GoTo 0
   Exit Function

apr8_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure apr8 of Module AWF_Related"
End Function

And redid the Macro RunCommand with Apr8()
and it worked just fine.

So however you were going to call/execute your macros, I think this just goes to the top of the list.
 
Last edited:
I seem to remember trying to add an autonumber field to a large table and getting a similar error. Do you get the same error if you add the field using the table design view?

How about making a new table with the autonumber field included then using an Append query to add the records from the old table?
 
You must log in or register to reply here.

Similar threads

A
Field Formatting
Replies
10
Views
519
Pat Hartman
P
C
Strange behavior after macro conversion
Replies
14
Views
568
Gasman
Gasman
S
Incremental Sequential Numbering by Year via Calculated Field
Replies
17
Views
465
CJ_London
C
M
Data Macro - Strange Performance Issue
2
Replies
32
Views
932
mellamokb
M
A
Storing data with format
Replies
2
Views
225
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom