Highest number plus 1

[Guest]

instead of using an autonumber is there an expression I can use to find the
highest number plus 1 for my primary key or ID

 

[Bill Edwards]

dmax("FieldName", "TableName") + 1

 

[aaron.kempf]

ROFL

what's performance like on that?

ROFL

use Access Data Projects; and it would be a lot easier you can set a
seed and an increment for your identity integers

and you can have a smallint or a tinyint for an autonumber field.. does
MDB even support that??

 

[John Vinson]

instead of using an autonumber is there an expression I can use to find the
highest number plus 1 for my primary key or ID

One easy way is to use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!ID = DMax("[ID]", "[YourTableName]") + 1
Me.Dirty = False ' force a save of the record to disk
End Sub

John W. Vinson[MVP]

 

[Guest]

Hi John

Found your answer to the previous question really helpful. However, is there
a way to do the same with a combination of letters and numbers: e.g. V101,
V102. I've tried your way but it only seems to work with numbers only.

Thanks

instead of using an autonumber is there an expression I can use to find the
highest number plus 1 for my primary key or ID

One easy way is to use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!ID = DMax("[ID]", "[YourTableName]") + 1
Me.Dirty = False ' force a save of the record to disk
End Sub

John W. Vinson[MVP]

 

[Thomas [PBD]]

You can just add the code in as:

Me!ID = "V" & DMax("[ID]", "[YourTableName]") + 1

However, this would require that your starting number be 100 or 101. As it
is currently written, this would start a blank table as "V1".

Hi John

Found your answer to the previous question really helpful. However, is there
a way to do the same with a combination of letters and numbers: e.g. V101,
V102. I've tried your way but it only seems to work with numbers only.

Thanks

instead of using an autonumber is there an expression I can use to find the
highest number plus 1 for my primary key or ID

One easy way is to use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!ID = DMax("[ID]", "[YourTableName]") + 1
Me.Dirty = False ' force a save of the record to disk
End Sub

John W. Vinson[MVP]

 

[Douglas J. Steele]

Actually, as currently written, it would start a blank table with V, because
DMax would return Null.

You need

Me!ID = "V" & Nz(DMax("[ID]", "[YourTableName]"), 0) + 1

to handle the Null value.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You can just add the code in as:

Me!ID = "V" & DMax("[ID]", "[YourTableName]") + 1

However, this would require that your starting number be 100 or 101. As
it
is currently written, this would start a blank table as "V1".

Hi John

Found your answer to the previous question really helpful. However, is
there
a way to do the same with a combination of letters and numbers: e.g.
V101,
V102. I've tried your way but it only seems to work with numbers only.

Thanks

On Mon, 23 Oct 2006 10:04:02 -0700, cacacaconnie

instead of using an autonumber is there an expression I can use to
find the
highest number plus 1 for my primary key or ID

One easy way is to use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!ID = DMax("[ID]", "[YourTableName]") + 1
Me.Dirty = False ' force a save of the record to disk
End Sub

John W. Vinson[MVP]

 

[Nigel]

GreatDoug, just what I wanted. Many thanks

Nigel

Actually, as currently written, it would start a blank table with V, because
DMax would return Null.

You need

Me!ID = "V" & Nz(DMax("[ID]", "[YourTableName]"), 0) + 1

to handle the Null value.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You can just add the code in as:

Me!ID = "V" & DMax("[ID]", "[YourTableName]") + 1

However, this would require that your starting number be 100 or 101. As
it
is currently written, this would start a blank table as "V1".

Hi John

Found your answer to the previous question really helpful. However, is
there
a way to do the same with a combination of letters and numbers: e.g.
V101,
V102. I've tried your way but it only seems to work with numbers only.

Thanks

:

On Mon, 23 Oct 2006 10:04:02 -0700, cacacaconnie

instead of using an autonumber is there an expression I can use to
find the
highest number plus 1 for my primary key or ID

One easy way is to use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!ID = DMax("[ID]", "[YourTableName]") + 1
Me.Dirty = False ' force a save of the record to disk
End Sub

John W. Vinson[MVP]

 

[Abdul Shakeel]

I try your code but it not work in my database I tblAllottees in which
primary key ID is AID with Data type Number

instead of using an autonumber is there an expression I can use to find the
highest number plus 1 for my primary key or ID

One easy way is to use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!ID = DMax("[ID]", "[YourTableName]") + 1
Me.Dirty = False ' force a save of the record to disk
End Sub

John W. Vinson[MVP]

 

[John W. Vinson]

On Sun, 8 Jun 2008 22:21:00 -0700, Abdul Shakeel

"Not work" is rather hard to fix.

Please post your actual code, describe how you used it, and describe what
happened. Post the actual text of any error messages, and your table
fieldnames and the names of the controls on your forms.

I try your code but it not work in my database I tblAllottees in which
primary key ID is AID with Data type Number

instead of using an autonumber is there an expression I can use to find the
highest number plus 1 for my primary key or ID

One easy way is to use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!ID = DMax("[ID]", "[YourTableName]") + 1
Me.Dirty = False ' force a save of the record to disk
End Sub

John W. Vinson[MVP]