Incremental Numbering NOT autonumber

[hsrour]

I need a field in a form to do the same thing as the autonumber, but use the last record and increment the value by one. I need this so I may cancel a save record and not increment this value.

Thanks in advance.

 

[thornmastr]

I question the validity or reasons why you are doing this; however, the following function takes two arguments as strings, the first argument is the name of your table, the second is the field name of your field you are using as an autonumber. It returns a long value with the next sequential number.


Public Function GetNextseqNbr(Tablename As String, FieldName As String) As Long: GetNextseqNbr = 0
Dim db As database
Dim rs As Recordset
Dim strSQL As String

strSQL = "SELECT max(" & FieldName & &quot AS HINUM FROM " & Tablename
Set db = CurrentDb
Set rs = db.openrecordset(strSQL, dbopensnapshot)
GetNextseqNbr = Nz(rs!hinum) + 1
rs.Close
Set rs = Nothing
Set db = Nothing

End Function

Watch out for the word wrap.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[gusbrunston]

Hi:

I accomplish an incrementing number by entering the following in Properties=>Data=>Default:

=DMax("[MyFieldName]","MyTableName")+1

Cheers, Gus Brunston 8-: An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[thornmastr]

Hi Gus,

The aggregate functions do work. All of them. But they are so slow and expensive you really are better off setting up your own replacement functions. In fact, there is a standardized set of replacements which I have found very useful, if you would like to look at them. I know being an old PICKER, you are used to the fantastic speed of list processing (PICK is the greatest invention since LISP).

http://www.mvps.org/access/modules/mdl0012.htm

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[gusbrunston]

Hi, Robert:
I'm dealing at the most with a few thousand records when I use DMax, and the response is instantaneous. But even so, with hundreds of thousands of records, if the field is "indexed" why would Dmax (or Dmin) be slow? Not doubting what you say, just wondering why.
I downloaded the file from the link you gave, and will look at it when I have some leisure time...but by then I will have lost my eyesight and who knows what kind of internet access I'll have in the nursing home.
Cheers, Gus Brunston 8-: An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[thornmastr]

Gus,

You lucked out. I was in the process off downloaded about three hundred articles on aggregate functions and speed when my dog decided she wanted to go out and chase squirrels and she needed a human to scare them into making noise, so you know what i was doing during lunch.

Anyway, if you will go over to the database section on google, you will find all kinds of interesting commentary on this topic. Just search on Aggregate functions and speed. There are thems swearing they are slow and thoses swearing they are not. You do of course know that Dcount cannot take advantage of an index. I'm not sure about the others,well, your guess is certainly as worthwhile as mime. Personally, I'll do almost anything to tweak a bit more speed out of an application. Think PICK my man...what could be fater than a
pick LIST HUH,HUH, HIH?????? I'm spoiled rotten. I'll do a lot of playing to tweak an application to pick up speed.

Berman's rule of development: If the user blinks twice before he has his data, your program needs tuning. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com