Auto increment new field value based on yes/no field value

K

Ken Eisman

My user issues a permit that has 2 categories - exempt and non-exempt.
Everything else is identical.

I'm using a yes/no field for exempt/non-exempt and a long field for permit
number.

Permit number has to be unique in each category (exempt/non-exempt) but can
be duplicated between categories.
(i.e. There can be a permit #1 for exempt and another permit #1 for
non-exempt but there cannot be 2 permit #1 that are exempt)

Obviously Autonumber won't work. I think I need to do some sort of SQL
lookup to find the highest existing number in the chosen category and then
add 1
to get the new value for the permit number. (i.e. If category = exempt then
get highest of permit number where category = exempt) I've tried a couple
of things that I thought would work but I've failed miserably.

If anyone has any suggestions, I'd be very grateful. I may be going about
this the wrong way. I wouldn't get my feelings hurt if you told me
that I was way off base.

Thanks
Ken
 
G

Guest

Here is the basic concept

You can find the highest current permit number with a DMAX

lngNextPermitNumber = DMAX("[PermitNumber]","PermitTableNameHere", _
"[Category] = " & True) + 1

Now the problem part. If you are in a multi user environment, then it is
possible that two users could be creating a new permit at the same time.
Assuming the highest current number is 2, then both of you would have 3
returned by the DMAX function. One solution is to immediately create the new
record to reduce the chances of two users getting the same number. The other
is to check to be sure it doesn't exist before you save it. For example, in
the Before Update event of your form, you could check to see if the number is
still available and if it is not then increment to the next number:

Do While True
If IsNull(DLookup("[PermitNumber]","PermitTableNameHere", _
"[PermitNumber] = " & Me.txtPermitNumber) Then
Exit Do
Else
Me.txtPermitNumber = Me.txtPermitNumber + 1
End If
End Do
 
K

Ken Eisman

Great! Thanks! DMax is just the command I was looking for. I knew about
Dlookup but somehow missed DMax.

Ken


Klatuu said:
Here is the basic concept

You can find the highest current permit number with a DMAX

lngNextPermitNumber = DMAX("[PermitNumber]","PermitTableNameHere", _
"[Category] = " & True) + 1

Now the problem part. If you are in a multi user environment, then it is
possible that two users could be creating a new permit at the same time.
Assuming the highest current number is 2, then both of you would have 3
returned by the DMAX function. One solution is to immediately create the
new
record to reduce the chances of two users getting the same number. The
other
is to check to be sure it doesn't exist before you save it. For example,
in
the Before Update event of your form, you could check to see if the number
is
still available and if it is not then increment to the next number:

Do While True
If IsNull(DLookup("[PermitNumber]","PermitTableNameHere", _
"[PermitNumber] = " & Me.txtPermitNumber) Then
Exit Do
Else
Me.txtPermitNumber = Me.txtPermitNumber + 1
End If
End Do

Ken Eisman said:
My user issues a permit that has 2 categories - exempt and non-exempt.
Everything else is identical.

I'm using a yes/no field for exempt/non-exempt and a long field for
permit
number.

Permit number has to be unique in each category (exempt/non-exempt) but
can
be duplicated between categories.
(i.e. There can be a permit #1 for exempt and another permit #1 for
non-exempt but there cannot be 2 permit #1 that are exempt)

Obviously Autonumber won't work. I think I need to do some sort of SQL
lookup to find the highest existing number in the chosen category and
then
add 1
to get the new value for the permit number. (i.e. If category = exempt
then
get highest of permit number where category = exempt) I've tried a couple
of things that I thought would work but I've failed miserably.

If anyone has any suggestions, I'd be very grateful. I may be going about
this the wrong way. I wouldn't get my feelings hurt if you told me
that I was way off base.

Thanks
Ken
 
M

Mike Painter

Ken said:
My user issues a permit that has 2 categories - exempt and non-exempt.
Everything else is identical.

I'm using a yes/no field for exempt/non-exempt and a long field for
permit number.

Permit number has to be unique in each category (exempt/non-exempt)
but can be duplicated between categories.
(i.e. There can be a permit #1 for exempt and another permit #1 for
non-exempt but there cannot be 2 permit #1 that are exempt)

Obviously Autonumber won't work. I think I need to do some sort of SQL
lookup to find the highest existing number in the chosen category and
then add 1
to get the new value for the permit number. (i.e. If category =
exempt then get highest of permit number where category = exempt)
I've tried a couple of things that I thought would work but I've
failed miserably.
If anyone has any suggestions, I'd be very grateful. I may be going
about this the wrong way. I wouldn't get my feelings hurt if you told
me that I was way off base.

Thanks
Ken

You say "can be duplicated " but does it have to be?
Use an autonumber which will guarentee unique numbers. That with the exempt
flag requires no code.
 
K

Ken Eisman

Mike Painter said:
You say "can be duplicated " but does it have to be?
Use an autonumber which will guarentee unique numbers. That with the
exempt flag requires no code.

User currently has a manual system that has duplicate numbers in it and
wants to maintain that numbering scheme.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top