Fill in the blanks

[Grimmers]

I have a form [membership] that has a primary key of [membershipNo]. Over the years, people have resigned and I wish to re use their old membership numbers when new members join. I may have nos 1,2,3,4,7 and I need to identify and re-use 5 and 6. Can i do this automatically rather than the current manual method?

Regards,


David

 

[Lister]

Is the [membershipNo] an Auto number?
If it is I think you can't get them back, well not easily anyway.
But also as long as each member has a unique ID number why would you want to reuse old member ship numbers?
If they have been out of use in some time, just archive them and keep on creating new ones.

Hope this is some help.


............................................................................

 

[Grimmers]

Lister,

Membership No is not an auto number, the only reason I want to do it is for aesthetics.

Cheers,


David

 

[dcx693]

If the manual method consists of scanning for unused numbers, then you can certainly use a function to find the lowest "missing" number. It sounds like you need the function for more than aesthetic reasons since you could use it each time the MembershipNo is assigned anyway.

There is no built-in Access function to do this. You'll have to write one of your own, but it's not too horrible an ordeal. There are a bunch of clever dudes here on the boards who can help bang it out for you. No really easy method for finding the lowest "missing" number comes to mind, you'd probably need a function that goes through the numbers starting from 1 and just plods through each integer until it finds the one that's either missing or > the largest currently assigned MembershipNo.

 

[namliam]

You can do it automaticly using 1 of 2 methods

1) VBA
dim rs as dao.recordset
dim I as integer
set rs = currentdb.recordset("QuerygettingallMembershipNos")
I = 1
rs.movenext
do until rs.eof
if I > rs!Membership then
rs.movenext 'should not heppen
elseif I = rs!membership then
debug.print I & " exists"
rs.movenext
elseif I < rs!membership then
debug.print I & " missing"
I = I + 1
endif
loop
2) Query (faster, more flexibele, but takes up (much) more space in you DB)
Involves a dummy table with all numbers posible and creating an outer join...

Regards

 

[dcx693]

namliam, I knew someone clever would write the VBA code!

I'm not sure about solution #2 being "automatic" though. The table of possible numbers would have to be maintained, probably by using DMax and finding the largest membership number, setting that as the upper limit, then filling in the dummy table with all possible values.

 

[Pat Hartman]

I strongly advise AGAINST reusing old member numbers. You don't have any idea when any particular record was deleted. It might have been yesterday! You also don't know what paper records might exist that reference the member number.

 

[Grimmers]

Thanks for the replies guys, as this really doesn't do anything apart from please me I feel that Pat is probably right. The database that I am fixing up has had membership nos deleted when there is provision for a resignation date so all i am trying to do is fill in the holes.

Thanks


David

 

[namliam]

namliam, I knew someone clever would write the VBA code!

I'm not sure about solution #2 being "automatic" though. The table of possible numbers would have to be maintained, probably by using DMax and finding the largest membership number, setting that as the upper limit, then filling in the dummy table with all possible values.

If you have an indivinate number of records and have numbers in you table up to
1,000,000,000 that will last you a while before you run into trouble and need to update your table.

Then running a query selecting from that table where not in membership and <= max(membershipno) will -automaticaly- do the trick at once... Tho you will have to have have 1,000,000,000 "bogus" records in the DB (taking up space)

Regards

The Mailman