How do I add +1 to a autonumber or start numbering at 64?

[Guest]

I am trying to set up a auotmatic numbering system for projects. Is there a
way to start a autonumbering at 64 or can I set the number to 64 and add +1
each time I add a new project? How do I do this???

 

[Joseph Meehan]

I am trying to set up a auotmatic numbering system for projects. Is
there a way to start a autonumbering at 64 or can I set the number to
64 and add +1 each time I add a new project? How do I do this???

You may be able to do what you want using the DMAX function.

I suggest you may not want to use Autonumber for that use. Autonumbers
are designed to provide unique numbers. It in not designed to provide
numbers in order and for a number of reasons may not do so. As a result
using them in any application where the user sees the numbers is likely to
end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.

 

[Arvin Meyer [MVP]]

Build a new empty table with a new empty autonumber field. Append a dummy
record with the number 63 in the autonumber field. Add 1 real record (it
will be number 64). Delete the dummy record.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Steve Schapel]

JR,

You can use an Append Query to put a record with 63 in the Autonumber field.

Open a blank query in design view, and enter 63 in the Field row of the
first column of the query design grid. Make it an Append Query (select
Append from the Query menu), and nominate your table. In the Append To
row of the grid, enter the name of the Autonumber field. The SQL of the
query will look something like this....
INSERT INTO [YourTable] ( YourAutoNumberField )
SELECT 63 AS Expr1;

Run the query (click the toolbar button with the red [!] icon). Close
the query, open the table, and delete this record just added. After
that, the next record added will have 64 in the autonumber field.

 

[(PeteCresswell)]

Per JR:

I am trying to set up a auotmatic numbering system for projects. Is there a
way to start a autonumbering at 64 or can I set the number to 64 and add +1
each time I add a new project? How do I do this???

I'm guessing that the project number is visible to users and is also being used
as the PK.

If that's the case, I'd use a plain-vanilla AutoNumber as the PK (let's call it
"ProjectID" and have a separate ProjectNumber field for what the users see.

Then you can keep something like stblRecordNumbers with the latest project
number in it and retrieve/increment it when needed. That would allow you to
start with 64. It would also accommodate breaks in the sequence and, in the
extreme, changes in the format/nature of a project number.

Here's what I do. It can be used for multiple field names.
------------------------------------------------------------
Function recordNumberGet(ByVal theName As String) As Long
4000 debugStackPush mModuleName & ": recordNumberGet"
4001 On Error GoTo recordNumberGet_err

' PURPOSE: To retrieve the latest number from a table of latest numbers,
' increment it by one, update the table, and return the number
' ACCEPTS: The name of the field for which we want the next record number
' RETURNS: The new record number or, in case of failure, zero

4010 Dim myRS As Recordset
Dim myQuery As QueryDef

Dim myNextNumber As Long
Dim myAttempts As Integer
Dim i As Integer
Dim v As Variant
Dim myDots As String
Dim beginTime As Double

Const somebodyElseHasRecordLocked = 3188
Const attemptsPerLoop = 50
Const maxLoops = 20
Const myTableName = "zstblRecordNumbers"

4030 beginTime = Now

4040 Set myQuery = curDB().QueryDefs("qryRecordNumberGet")
4041 With myQuery
4042 .Parameters("theName") = theName
4043 Set myRS = .OpenRecordset(dbOpenDynaset)
4049 End With

4061 With myRS
4160 If .BOF And .EOF Then
4170 .AddNew
4180 !FieldOrTableName = theName
4190 !LatestValue = 2
4200 .update
4210 recordNumberGet = 1
4220 Else
4230 .Edit 'Attempt to Lock that suckah!
4240 recordNumberGet = !LatestValue
4250 myNextNumber = !LatestValue + 1
4260 !LatestValue = myNextNumber
4270 .update
4280 recordNumberGet = myNextNumber
4290 End If
4299 End With

recordNumberGet_xit:
debugStackPop
On Error Resume Next
v = SysCmd(acSysCmdRemoveMeter)
Set myQuery = Nothing
myRS.Close
Set myRS = Nothing
Exit Function

recordNumberGet_err:
Select Case Err
Case somebodyElseHasRecordLocked
If myAttempts > maxLoops Then
DoCmd.Hourglass False
MsgBox "Table '" & myTableName & "' appears to be deadlocked after " &
Str(attemptsPerLoop * maxLoops) & " attempts made over a period of" &
Str(DateDiff("s", beginTime, Now)) & " seconds.", 16, "Fatal Error"
Resume recordNumberGet_xit
Else
For i = 1 To attemptsPerLoop
v = SysCmd(acSysCmdSetStatus, "Waiting for somebody else to update
" & theName & myDots)
DoEvents
Next i
myAttempts = myAttempts + 1
myDots = myDots & " ."
Resume
End If
Case Else
DoCmd.Hourglass False
MsgBox "Line " & Str(Erl) & ", Error " & Str(Err) & ": " & Error$, 48,
"recordNumberGet()"
Resume recordNumberGet_xit
End Select
End Function
------------------------------------------------------------

qryRecordNumberGet:
------------------------------------------------------------
PARAMETERS theName Text ( 255 );
SELECT DISTINCTROW zstblRecordNumbers.fieldOrTableName AS Expr1,
zstblRecordNumbers.latestValue AS Expr2
FROM zstblRecordNumbers
WHERE ((([zstblRecordNumbers].[fieldOrTableName])=[theName]));

 

[Guest]

It's only 64 :~)

Put in 63 records, then delete them.

 

[erinb-tsc]

JR,

You can use an Append Query to put a record with 63 in the Autonumber field.

Open a blank query in design view, and enter 63 in the Field row of the
first column of the query design grid. Make it an Append Query (select
Append from the Query menu), and nominate your table. In the Append To
row of the grid, enter the name of the Autonumber field. The SQL of the
query will look something like this....
INSERT INTO [YourTable] ( YourAutoNumberField )
SELECT 63 AS Expr1;

Run the query (click the toolbar button with the red [!] icon). Close
the query, open the table, and delete this record just added. After
that, the next record added will have 64 in the autonumber field.

Steve

I am trying to accomplish your instructions above however once I hit Run
nothing happens. There is a message, in the bottom left hand corner, that
states "The action or event has been blocked by Disabled Mode." What am I
doing or not doing to screw this up? Please help.

Erin