What is best way to re-use an account number?

[Dennis]

Hi,

I'm on Access 2003 and will be going to Access 2007 shortly.

Background:
-----------
I have a Charity Fund raising database. When a person joins, they are
assigned an automatically generated account number by Access. (The account
number is not the issue.) However, mgmt want to also assign a re-usable
"Fund Raising" number and that is my issue.

When a new member joins, they are assigned a "Fund Raising" number. That
number will stay will that member until they leave the group. Once that
member leaves the group, we terminate the member on the system, and their
"Fund Raising" number becomes available to be re-assigned to the next new
member.

The fund raising number is a sequential number starting at 1 and going up.


Database:
---------

I will create a table called tblFundNo, which is keyed by the fund raising
number. There will be a special row keyed by the number zero. The zero row
will contain that last used high number.

The table's structure will be:

tblFundNo
key : Name = FundNo, Value = A Fund No
fld 1: Name = Available, Value = Y (# is available) or N (# not available)
fld 2: Name = HighNo, Value = Last number assigned.

Note: Only record 0 will have a value in the HighNo field. It's Available
field will be set to "N".


The Code:
---------

The following code will be in the form's After Update event:

When a new member is entered, I will search the tblFundNo using FindFirst
where Available = "Y". If I find one, I will assign that Fund Raising number
to the member and change that number's Available flag to "N".

If the FindFirst fails, I will read in the Zero row, add 1 to the last used
number, and write backup out the zero row. I will then create a new
tblFundNo row using the new last used number, set it's Available flag to "N",
and write it. I will then assign that number to the member and let Access
update the member record.

Once a number is assigned, I will pop up a msg box informing the user of the
member's charity no.


My Questions:
-------------

1. Is this the right way to do this in Access?
2. Is there a better way to do this in Access?
3. Is the form's After Update event the place where I want to put this code?
4. What is the best way to update my tblcharityNo table - using SQL or
using Access's db.AddNew, db.Update, and db.Close?


Thanks for your assitance.

 

[Mike Painter]

I will create a table called tblFundNo, which is keyed by the fund
raising number. There will be a special row keyed by the number
zero. The zero row will contain that last used high number.

The table's structure will be:

tblFundNo
key : Name = FundNo, Value = A Fund No
fld 1: Name = Available, Value = Y (# is available) or N (# not
available) fld 2: Name = HighNo, Value = Last number assigned.

Note: Only record 0 will have a value in the HighNo field. It's
Available field will be set to "N".


The Code:
---------

The following code will be in the form's After Update event:

When a new member is entered, I will search the tblFundNo using
FindFirst where Available = "Y". If I find one, I will assign that
Fund Raising number to the member and change that number's Available
flag to "N".

If the FindFirst fails, I will read in the Zero row, add 1 to the
last used number, and write backup out the zero row. I will then
create a new tblFundNo row using the new last used number, set it's
Available flag to "N", and write it. I will then assign that number
to the member and let Access update the member record.

Once a number is assigned, I will pop up a msg box informing the user
of the member's charity no.


My Questions:
-------------

1. Is this the right way to do this in Access?
2. Is there a better way to do this in Access?
3. Is the form's After Update event the place where I want to put
this code?
4. What is the best way to update my tblcharityNo table - using SQL
or using Access's db.AddNew, db.Update, and db.Close?

There is no need to store the highest number. After finding no available
number use
NewValue = dMax("YourNumber","YourTable") + 1

I don't see the need for an extra table. You could just mark the table with
the rest of the fundraiser info as available and update it.
What happens if Fred Fundraiser quits and comes back next day, week, month,
year?

SQL is better most of the time and will always be faster. I'd suggest in
this case using the method you are less familar with.

 

[Tony Toews [MVP]]

When a new member joins, they are assigned a "Fund Raising" number. That
number will stay will that member until they leave the group. Once that
member leaves the group, we terminate the member on the system, and their
"Fund Raising" number becomes available to be re-assigned to the next new
member.

Why? To me this is a dumb, dumb business decision. And how do you
know they "leave the group?" Why if they don't donate one year? Or
is this an MLM type of business?

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Daryl S]

Dennis -

I assume you have a valid reason for re-using the numbers, and cannot just
create new ones.

Do you need a different set of numbers for each fund? In your Background
section, it sounds like one number per person. I am not sure of the 'group'
that a member leaves, causing them to be deleted. If you just need one
number per person, then this might work for you:

Questions 1 and 2:
Create a tblFundRaisingNo that has an auto-number field (e.g. FundRaisingNo)
and a field that would match the key value to your member table (e.g.
MemberNo). Run an initial query to populate this table with all current
members. The auto-number field will populate itself. After that, when a
member is removed, you can set the MemberNo field to NULL in the
tblFundRaisingNo, but leave the record in place. When you add a new member,
look for any null MemberNo fields, and if there are any, put the new member's
MemberNo in the first record with a NULL MemberNo. If there are no NULL
MemberNo fields, add a new record with that MemberNo.


Question 3: I would expect that adding a new member record would be
successful way more often than it would fail. For that reason, I would
suggest putting this code in the BeforeUpdate event. This is because you can
get the FundRaisingNo for the new member before adding their member record,
so you won't need to code for an update to add it in later. You should code
for the rare failure to add the new member - this means setting the MemberNo
field to NULL for the record in the tblFundRaisingNo table.

Question 4: I don't know what is in your tblCharityNo table - is this the
member table? If you are doing this on a form, then you won't need to code
it separately. If it is another field, and you are adding one record (one
member) at a time, then either code or SQL will work.

 

[Dennis]

All,

Thank you for your response. To answer some questions:

- Do you need a different set of numbers for each fund?
- No. There is only one fund. I need one number per person.

- It sounds like one number per person.
- This is correct.

- I don't know what is in your tblCharityNo table - is this the member table?
- My tblCharityNo is your tblFundRaisingNo.

- Why does a member leave?
- 99% of the events are local events. We live in a highly transitionally
town. When someone moves out of the area or dies, they are removed from the
charity events membership.

- What happens if Fred Fundraiser quits and comes back next day, week,
month,
year?
- This is not a issue as member are only deleted when they move out of the
area or die.

- How do you know they "leave the group?"
- Mail is returned marked undeliverable, no longer at this address, or
deceased.

- To me this is a dumb, dumb business decision.
- The numbers are important because dice are rolled at these fund raising
events and the winer is the member's whoes number matches the roll of the
dice. This is their version of door prices and they do it a couple of times
a night. It does seem to keep attendance up.

- Why reuse the numbers?
- I don't fully understand, but I have tried to get mgmt to change their
mind. Part of the it is because "this is the way it has always been done". I
finally got tired of beating my head against the wall.

- Or is this an MLM type of business?
- What is an MLM type of business?


I have a couple of questions:
1. With respect to "After finding no available number use NewValue =
dMax("YourNumber","YourTable") + 1 ". How long does this take to run versus
reading in a record, adding 1 to the count, and writing it back out. I see
the other way is simpler, but is the dMax function very time consuming?

2. I would suggest putting this code in the BeforeUpdate event.
I would need to assign the Charity Number to the member’s record in the
Before Update event. As I understand it, I can not update the current row in
the Before Update event. So I don’t quite understand how to implement that
piece of the code.


Thanks,

 

[Dennis]

All,

Thank you for your response. To answer some questions:

- Do you need a different set of numbers for each fund?
- No. There is only one fund. I need one number per person.

- It sounds like one number per person.
- This is correct.

- I don't know what is in your tblCharityNo table - is this the member table?
- My tblCharityNo is your tblFundRaisingNo.

- Why does a member leave?
- 99% of the events are local events. We live in a highly transitionally
town. When someone moves out of the area or dies, they are removed from the
charity events membership.

- What happens if Fred Fundraiser quits and comes back next day, week,
month,
year?
- This is not a issue as member are only deleted when they move out of the
area or die.

- How do you know they "leave the group?"
- Mail is returned marked undeliverable, no longer at this address, or
deceased.

- To me this is a dumb, dumb business decision.
- The numbers are important because dice are rolled at these fund raising
events and the winer is the member's whoes number matches the roll of the
dice. This is their version of door prices and they do it a couple of times
a night. It does seem to keep attendance up.

- Why reuse the numbers?
- I don't fully understand, but I have tried to get mgmt to change their
mind. Part of the it is because "this is the way it has always been done". I
finally got tired of beating my head against the wall.

- Or is this an MLM type of business?
- What is an MLM type of business?


I have a couple of questions:
1. With respect to "After finding no available number use NewValue =
dMax("YourNumber","YourTable") + 1 ". How long does this take to run versus
reading in a record, adding 1 to the count, and writing it back out. I see
the other way is simpler, but is the dMax function very time consuming?

2. I would suggest putting this code in the BeforeUpdate event.
I would need to assign the Charity Number to the member’s record in the
Before Update event. As I understand it, I can not update the current row in
the Before Update event. So I don’t quite understand how to implement that
piece of the code.


Thanks,

 

[Dennis]

Access Vandal,

We are not reusing the "account number". Each member is assign a account
number that NEVER changes. And I should have been more clear. We do not
"delete" a member. We flag the the member as "deleted".

The number we are re-using is a "door prize" number. This number is used to
reward prizes to members at different fund raising events. They are trying
to minimize "gap" in the numbers.

 

[Tony Toews [MVP]]

- To me this is a dumb, dumb business decision.
- The numbers are important because dice are rolled at these fund raising
events and the winer is the member's whoes number matches the roll of the
dice. This is their version of door prices and they do it a couple of times
a night. It does seem to keep attendance up.

Ahhh, ok, now I understand. This makes a lot more sense then. I
apologize for stating "dumb, dumb". I should've asked why before
making such a blunt statement.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Tony Toews [MVP]]

- Or is this an MLM type of business?
- What is an MLM type of business?

Multi level marketing.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Dennis]

Tony,

Thanks for your assitance and comments. No, I kind of agree about it being
dumb, but people are strange with it comes to "lucky number". I just learned
to accept it and figure out how to do it rather than trying to change
people's mind about their lucky numbers.


One question though, how do I update a field on the record / row in the
Form's Before Update event? Doesn't changing a field in the Before Update
event re-trigger the Before Update event. I know I can't change a control
value in the contol values' Before Update event. Is it the same way on the
Form's Before Update event?




Dennis

 

[Dennis]

AccessVandal,

Actually, I was thinking about doing something like you suggested, I just
not had figured out quite how to do it. However, once I saw you code, it
finally clicked.

The only thing I need to do is once I re-use the FundNo from the FlagStatus
= "delete" member, I'm going to have to null it out on the deleted member and
assign it the new member. If I don't remove it from the deleted member, I
could re-use it again later. So I have to figure that out.

Thanks for all of your help.

 

[Dennis]

Mike,

Thanks for you suggestions, after thinking about it, you are absolutely
correct. I can do it within my existing file. Thanks!

 

[Gina Whipp]

Dennis,

How about...

SELECT T.FundNo+1 AS Missing
FROM tblFundNo AS T LEFT JOIN tblFundNo AS T1 ON T1.FundNo=T.FundNo+1
WHERE (((T1.FundNo) Is Null));

Just copy/Paste in the Query SQL window and then you can use this qry in
Access Vandal's code. The above will give you a list of unused FundNo's. I
didn't use a flag, I had to find these numbers without editing the table.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John W. Vinson]

One question though, how do I update a field on the record / row in the
Form's Before Update event? '

Just update it:

Doesn't changing a field in the Before Update
event re-trigger the Before Update event.
No.

I know I can't change a control
value in the contol values' Before Update event.

Correct; you should use the control's AfterUpdate event instead.

Is it the same way on the
Form's Before Update event?

Nope.