automatic number advancement

  • Thread starter Thread starter jf
  • Start date Start date
J

jf

I am developing a contract management database. Each new
contract will be assigned the next sequential number. I
want to have a base info entry screen. Is there a way to
pre-load the next number in the sequence; either in an
entry box, or as a number displayed on the form?

thanx

jf
 
I am developing a contract management database. Each new
contract will be assigned the next sequential number. I
want to have a base info entry screen. Is there a way to
pre-load the next number in the sequence; either in an
entry box, or as a number displayed on the form?

thanx

jf
Click to expand...

You'll need a little bit of VBA code. Exactly how the code works
depends on the circumstances: if it's a one-user database, or if it's
very unlikely that you'll have two users trying to assign a number
simultaneously, you can use code in the Form's BeforeInsert event
like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtContractNo = NZ(DMax("[ContractNo]", "Contracts")) + 1
End Sub

This runs a slight risk of two users getting the same Max if one of
them opens a new record while another user has a new record open (but
unsaved). If you want to avoid this, you can use a table holding the
next number - Getz et al. have some robust code for the purpose in
their _Developer's Handbook_ and there are other examples on the web.
Search for "Access Custom Counter".
 
jf said:
I am developing a contract management database. Each new
contract will be assigned the next sequential number. I
want to have a base info entry screen. Is there a way to
pre-load the next number in the sequence; either in an
entry box, or as a number displayed on the form?

thanx

jf
Click to expand...
In the routine that created the new contract record, do a Dmax on the
table t get the
last contract number and add 1 to it.

Me!ContractNo= DMax("[ContractNo]", "tblContract" ) + 1


HTH,
Ron
 
I am developing a contract management database. Each new
contract will be assigned the next sequential number. I
want to have a base info entry screen. Is there a way to
pre-load the next number in the sequence; either in an
entry box, or as a number displayed on the form?

thanx

jf
Click to expand...

You'll need a little bit of VBA code. Exactly how the code works
depends on the circumstances: if it's a one-user database, or if it's
very unlikely that you'll have two users trying to assign a number
simultaneously, you can use code in the Form's BeforeInsert event
like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtContractNo = NZ(DMax("[ContractNo]", "Contracts")) + 1
End Sub

This runs a slight risk of two users getting the same Max if one of
them opens a new record while another user has a new record open (but
unsaved). If you want to avoid this, you can use a table holding the
next number - Getz et al. have some robust code for the purpose in
their _Developer's Handbook_ and there are other examples on the web.
Search for "Access Custom Counter".
Click to expand...

I don't think BeforeInsert is the best event for this. The new
contract number will be created when the user first types into the new
record. A better event is Before Update and check for a new record.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.txtContractNo = Nz(DMax("[ContractNo]", "Contracts")) + 1
End If
End Sub

This will happen at the very last instant before the record is added
and the risk of conflicts is very, very slight.

- Jim
 
I don't think BeforeInsert is the best event for this. The new
contract number will be created when the user first types into the new
record. A better event is Before Update and check for a new record.
Click to expand...

Either way can make sense; if you use BeforeUpdate the user will never
see the new contract number (it will only be created when they are
completely done with the record), whereas with BeforeInsert it will be
assigned at the first keystroke. The BeforeUpdate is certainly safer
in that it will keep the time between the assignment of a number and
storing it in the table to a minimum, but many users are perplexed
when they can't see the contract number as they're entering data.
 
Either way can make sense; if you use BeforeUpdate the user will never
see the new contract number (it will only be created when they are
completely done with the record), whereas with BeforeInsert it will be
assigned at the first keystroke. The BeforeUpdate is certainly safer
in that it will keep the time between the assignment of a number and
storing it in the table to a minimum, but many users are perplexed
when they can't see the contract number as they're entering data.
Click to expand...
I guess my concern is in the human factor aspect. The user starts a
new record with the new number and then gets distracted or called away
or coffee break. During the interval another user uses that *next*
number. When the user gets back to the record and attempts to save it,
they have problems with the number - that_will_confuse them.

If a number is needed then there can be a save when a minimal amount
of data is entered. That gives them a real contract number. Of course
if it is abandoned that would have to be managed.

That's my 0.02

- Jim
 
I guess my concern is in the human factor aspect. The user starts a
new record with the new number and then gets distracted or called away
or coffee break. During the interval another user uses that *next*
number. When the user gets back to the record and attempts to save it,
they have problems with the number - that_will_confuse them.
Click to expand...

That's why I suggested NOT using this technique in a multiuser
application; Getz et al.'s suggestion (using a NextNumber table,
opening it exclusively, incrementing, storing it back in the table,
and returning the incremented value, with code to protect users from
colliding) gets around this problem, at the cost of some rather
complex code.
 
That's why I suggested NOT using this technique in a multiuser
application; Getz et al.'s suggestion (using a NextNumber table,
opening it exclusively, incrementing, storing it back in the table,
and returning the incremented value, with code to protect users from
colliding) gets around this problem, at the cost of some rather
complex code.
Click to expand...
You're right you did mention it was primarily for a single user
environment. And, you did mention the risks.

The point I was trying to make is that it doesn't need to take much
code to deal with the conflicts - just applied at the right time.

Cheers,
- Jim
 
Back
Top