I would like to have a function within my database where the next available
number in a series is automatically found and inserted. For example, I want
to generate a file number, but I can't use Auto Number since I will sometime
insert a batch of info and I think that would mess auto number up. I want
the function to look at 1, 3, 76, 982, and know that the next number it
should pick is 983. Help!!
--
Andrea Sargent
President
A Virtual Wonder LLC
www.avirtualwonder.com

SargentAP ha scritto:

> I would like to have a function within my database where the next available
> number in a series is automatically found and inserted. For example, I want
> to generate a file number, but I can't use Auto Number since I will sometime
> insert a batch of info and I think that would mess auto number up. I want
> the function to look at 1, 3, 76, 982, and know that the next number it
> should pick is 983. Help!!
> --
> Andrea Sargent
> President
> A Virtual Wonder LLC
> www.avirtualwonder.com

May be like this...?

On DefaultValue property
=Nz(Dmax([Fieldname];"TableName"),0)+1

@Alex

AutoNumber should work fine if you insert a batch of records. I do it
all the time.

Jerry

Think I had the same problem you are having and someone help me out with that
problem.

Heres the code they show me...so Im pasting on to you....This is what I
believe you are looking for....

This code goes in the form that you are using...

'Assign the new member 1 membership ID before added to database.
Me![MembershipID] = NewMembershipID()


This code rite here...goes into a module and also you will have to change
something to see fix your needs...

'Got code from:
'Name: NewMembershipID
'Purpose: To find the last MembershipID then + 1 for the new member's ID
'Edit by:
'Create on: 7/6/2006

Public Function NewMembershipID() As Long

On Error GoTo NextID_Err

Dim NextMembershipID As Long

'Find highest Membership ID in the tblMembership table and add 1
NextMembershipID = DMax("[MembershipID]", "tblMembership2") + 1

'Assign function the value of the Next ID
NewMembershipID = NextMembershipID

'Exit function now after successful incrementing or after error message
Exit_NewMembershipID:
Exit Function

'If an error occurred, display a message, then go to Exit statement
NextID_Err:
MsgBox "Error " & Err & ": " & Error$

Resume Exit_NewMembershipID

End Function


I hope this help..........