Increment Number Without Using Autonumber

[Michael]

Hi Folks - I have a db that tracks felony charges against defendants. I have
a main defendant form with a charges subform. On the subform, I have 2
fields: Count and Statute. Count is a number from 1 to 999. Statute is a
drop-down that references the master statute table.

Currently, the user will enter the count manually. However, I would like to
automate this process if possible. One problem with the manual approach is
if Count 3 of 10 Counts is deleted. This forces the user to re-number the
Counts. Not sure how to handle this. Any ideas?

Note: For each charge, there can be multiple victims.

Michael

 

[John W. Vinson]

Hi Folks - I have a db that tracks felony charges against defendants. I have
a main defendant form with a charges subform. On the subform, I have 2
fields: Count and Statute. Count is a number from 1 to 999. Statute is a
drop-down that references the master statute table.

Currently, the user will enter the count manually. However, I would like to
automate this process if possible. One problem with the manual approach is
if Count 3 of 10 Counts is deleted. This forces the user to re-number the
Counts. Not sure how to handle this. Any ideas?

Note: For each charge, there can be multiple victims.

Well... you may want to think about this. Suppose you had a defendant (a real
baddie) with 427 charges against him. They're numbered 1 to 427.

The court decides to drop charge 3. This will renumber all of the charges from
4 through 427... *in the computer*. Will it also renumber them all in the
printouts that have been sent to attorneys, prison officials, parole officers,
etc. etc.? Will it renumber them in the minds of these people (who may be
thinking "charge 71 is just SO bogus, it's bound to get dropped" when it's
actually charge 72)?

What you ask to do can be done - running an Update query to update all the
higher numbers by subtracting 1 - but I have to wonder whether it's wise to do
so!

John W. Vinson [MVP]

 

[Mr. B]

Hi Folks - I have a db that tracks felony charges against defendants. I have
a main defendant form with a charges subform. On the subform, I have 2
fields: Count and Statute. Count is a number from 1 to 999. Statute is a
drop-down that references the master statute table.

Currently, the user will enter the count manually. However, I would like to
automate this process if possible. One problem with the manual approach is
if Count 3 of 10 Counts is deleted. This forces the user to re-number the
Counts. Not sure how to handle this. Any ideas?

Note: For each charge, there can be multiple victims.

Michael

Hi, Michael.

You can use the On Current event of your sub form to get the next
available number for the next charge by first getting the Max number
that is already in your list of charges for that defendent by using
the DMax function to return the current maximum number and then add 1
to it each time and then assign that number to be the value for the
next Charge number. You would most likely lock that field so the user
could not change it. As for the renumbering. Depending on how you
are having the records deleted you can actually have a function that
would do the renumbering for the list no matter which charges were
deleted. You really would need a Delete button to accomplish this.

I know that I did not completely answer your question, however, the
methods you would use to reach your goals will depend on how you have
things working now.

Post back if you have additional questions.

HTH

Mr. B

 

[Guest]

I'm also trying to generate a number without using autonumber. I've created
a file Order test with two fields, Key number and Next seq. I used the
Expression builder on the form and set the default to =DMax("[Next
seq]","Order test")+1. I get nothing back. What am I doing wrong?