Autonumber start at 500

[ya5irha55an]

Hi All,

Sorry for this question, I know its simple but im not very experience with Access: this question is exectly what I am looking for, I also have an autonumebr which I would like to begin from a number other than 0, but I am not sure how to use this code, where do I put it?

 

[Banana]

You put it in a Module; you can make a new module by using database window or selecting New Class Module from menu in VBA editor. You will need to pass the table and fieldname of the autonumbering column with number you want to start with.

HTH.

 

[ccg_0004]

Access 2007

Does anyone know if this code works with Access 2007? When I click RUN in the VB Editor it asks me selecting a macro. Why is this?

CG

 

[boblarson]

You would have to add a reference (Tools > References) in the VBA Window to Microsoft ADO because 2007 doesn't have that selected.

 

[raskew]

Hi -

As this thread moves into three pages, you might want to take a look at Allen Browne's solution at

http://www.allenbrowne.com/ser-26.html

Bob

 

[datAdrenaline]

The easiest way, IMO, to reset the seed value and increment is through a SQL statement ...

Public Function ChangeSeed(lngStartAt As Long, intIncrementBy As Integer) As Byte
    
    Dim strSQL As String
    
    strSQL = "ALTER TABLE yourTablename ALTER COLUMN yourAutonumberColumn" & _
              " AUTOINCREMENT(" & lngStartAt & "," & intIncrementBy & ")"
    
    CurrentDb.Execute strSQL, dbFailOnError
    
    '''''''''''''''''''''''''''''''''''''''''
    'You can also use ADO/JET OLEDB Provider with a connection object
    '
    'CurrentProject.Connection.Execute strSQL, ,adCmdText
    
End Function

 

[datAdrenaline]

If you choose to use the code posted by Pat .... As Bob indicated, you will need the ADO (Microsoft ActiveX Data Objects xx.xx) library reference. In addition you will need the ADOX (Microsoft ADO Ext. xx.xx for DDL and Security) library as well.

 

[ccg_0004]

If you choose to use the code posted by Pat .... As Bob indicated, you will need the ADO (Microsoft ActiveX Data Objects xx.xx) library reference. In addition you will need the ADOX (Microsoft ADO Ext. xx.xx for DDL and Security) library as well.

I appreciate all the help. I had enabled the two references before I posted so I diasbled them and tried again. No such luck. To be sure I checked the complete list of ref's- that thing is damn long.

Microsoft ADO Ext. 2.8 For DDL and Security
Microsof ActiveX Data Objects 2.1 Library

are the two ref's. They have checks next to them which I assume is correct. Maybe I will try to move them up in priority.

 

[Banana]

ccg,

I'd strongly advise you to uncheck all references except for what you had originally. Not only it will make your application run slower, you are running much higher risk of crashing the application due to conflicts or whatever unknown side effects this may have.

You already had the needed references checked, and it's something else, not the references that is the problem.

I think if you put your cursor within that procedure then click run, it will run. If cursor isn't within that procedure, you'll be asked to select a macro.

 

[ccg_0004]

I'd strongly advise you to uncheck all references except for what you had originally. Not only it will make your application run slower, you are running much higher risk of crashing the application due to conflicts or whatever unknown side effects this may have.

I was not clear. I searched through the list of unchecked references which was very long. There are only like 5 refs checked, the two in this thread, two that are in use, and OLE automation.

I think if you put your cursor within that procedure then click run, it will run. If cursor isn't within that procedure, you'll be asked to select a macro.

Tried but DOH! Still asking for macro. All codes provided ask for macro.

StepbyStep:
I copy the code. I open VB. INSERT->New Module. Paste.

above the paste i type "Call SetAutoNumber(InvoiceID, 550)" and then click Run. Suddenly I'm asked for a macro. Killer.

 

[Banana]

Is the Module you created a standard module or a class module? You can run code from standard module but not from a class module.

 

[datAdrenaline]

Hello ccg...

>> I think if you put your cursor within that procedure then click run, it will run. If cursor isn't within that procedure, you'll be asked to select a macro. <<

Clicking the Run will only invoke the procedure IF your procedure does not have any arguments. Your procedure has arguments, that is why Access if prompting you for a Macro ... You are asking VBA to run, but it can not run the procedure you're cursor is in because it does not know the values for those arguments, so it then asks for a Macro or Function procedure that does not have arguments to be passed.

If you want to invoke a procedure that has arguments, you can do that from the Immediate window of the VBA editor by typing the command, then hitting the enter key, NOT the "Run" button (the Run looks at your cursor position) ...

If you wish to see the result of a Function procedure that enter the command like this:

? SomeFunctionName(Arg1, Arg2, etc) {hit the enter key}
<the result will show here>

If you wish to invoke the execution of a Sub procedure or a Function that you have no need for the value returned (ie: a Function that is used like a Sub), then enter the command like this ..

SomeProcedureName Arg1, Arg2, Arg3, etc ... {hit the enter key}

....

For Example:

A "Function" (you want to know the result of a function or value of a property) type command from the immediate window ...

? 2 + 2
4

? CurrentDb.TableDefs("SomeLinkedTable").Connect
;Database=C:\SomePath\SomeFile.mdb
---------

A "procedural" (execute a the series of steps that make up the procedure) type call ...

DoCmd.DeleteObject acForm, "MyFormName" {hit the enter key}
<cursor will be flashing on the line below the command when the procedure>

---------
Aside from all this ... I would encourage you to use the code I posted as it is not AS library dependant (the constants are the only library dependant piece .. and you could use the numbers instead if you wish). Also, libraries become important if you declare object variables too! ... If you want to use the ADOX code, and you are getting errors, then please post the code you are using and the exact line the err is raised on.

Also ... your comment:
>> Maybe I will try to move them up in priority <<

If you have explicitly declared (ie: Dim rs As ADO.Recordset) your object variables, priority will not matter. I encourage explicit declaration of object variables.

Hope this helps!!

 

[Banana]

As usual, Brent gets it.

I totally spaced out on that argument parts, thinking that the code didn't have any.

 

[datAdrenaline]

Thanks Bannana ... but the tip about the Class vs Standard module you posted is good too!! ...

 

[datAdrenaline]

One more thing ccg.. you're step by step should be ....

copy the code. open VB. INSERT->New Module. Paste.

Display the immediate window (View -> Immediate Window),
type "SetAutoNumber InvoiceID, 550" and then hit the <Enter> key.

 

[ccg_0004]

Worked my way through it

One more thing ccg.. you're step by step should be ....

copy the code. open VB. INSERT->New Module. Paste.

Display the immediate window (View -> Immediate Window),
type "SetAutoNumber InvoiceID, 550" and then hit the <Enter> key.

will *A LOT* of help from my friends (beatles tune in bg...). Thanks so much y'all I got it working now. I never knew about the Immediate window or even how to call a procedure.

You learn something new everyday they say, so I guess I can go to sleep now! Thanks again for the help!

 

[datAdrenaline]

You're welcome! ...