Next Number in the Series, WITHOUT using "autoid"

"Kelly" (khoch@urinow.com) wrote in message news:VDoz6.1040$4e.263683@newshog.newsread.com...

How do I make the next number in the series appear automatically without using the auto number in a table?.......

........Assuming we're talking about field "MyField" in table "MyTable", set the [default] field [value] equal to
=DMax("MyField", "MyTable") + 1

Dirk Goldgar

From Me (Larry): You would also want to insert the following code into the "Before Update" Event Procedure of the form in question. This helps prevent a common problem in networking situations: number sequencing problems with multi-user situations:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me![packlist] = DMax("packlist","main_ship_table")+1
End If
DoCmd.RunCommand acCmdSaveRecord
End Sub

In the above example, the table's name is "main_ship_table," the field's name (both on the form & in the table) is "packlist"

Special Scenario

6/28/01 1:32:00 PM

I have Access 97, Windows NT Service Pack 6. I am trying to generate the next number in a series (numerical type of field), without using "autoid." I have the following syntax (entered in the DEFAULT VALUE of the field on the form) to do this:

=DMax("MyField", "MyTable") + 1

This is a special case, though, in that in the table there are "mold numbers" as high as 9020 or so. However, there exists a big "gap" in the series. All the numbers between 2000 and 9000 are vacant. Therefore, I want the form--for now at least--to max out at the highest value that is LOWER than 9000. Is this possible, or too far-fetched?

Possible, even easy. Just pass DMax() its optional third parameter, a WHERE clause without the WHERE specifying criteria:

=DMax("MyField", "MyTable", "[MyField] < 9000") + 1

John W. Vinson[MVP] http://go.compuserve.com/msdevapps?loc=us&access=public

The Syntax I Used in the Above Scenario

The field I was doing this with was an alphanumeric field, as opposed to a numeric field, so I added the ' characters around the 9000 portion. My table name was "mold_table," the field was named "mold_number" (though, again, it was an alphanumeric field). The syntax was as follows:

=DMax("mold_number","mold_table","[mold_number] < '9000'")+1

Again, this text was entered in the "default value" section of the field on the form.