Developer Forum

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

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

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

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.

SNIP

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