Sequential numbering for forms

[XP]

Using Office 2003;

I'm sure this issue has been posted before; I tried
searching for it but I'm still looking for the "ideal"
solution. Here's the business case:

-Forms are used by a wide variety of users over a
network at multiple locations;
-Form numbers must be six digits in sequential order
and unique;
-Due to auditors, need to capture who uses each form;
-Forms are built in MS-Excel per boss requirement;

Right now, when the forms are opened a query to
Ms-Access returns a form number and records the
user login ID at the same time in Access. The number
is then written into the MS-Excel form.

This works well 99% of the time. Every once in a while,
just enough to make you crazy, a form is generated
without a number, even though my code does an edit
check specifically for this. This issue is sporadic and not
limited to any particular user, time of day, day of week,
etc. My program locks users out if macros are not
enabled by displaying a splash screen on open/exit so
user cannot do anything if macros are disabled.

Does anyone have any suggestions on how to make this
more full proof? Ideas for improved numbering systems?
I've seen something out there about a Numbering Add-In?

Due to budgeting constraints I need to build a solution
using MS Office products we alreay have (XL, Access, Word).

Any help appreciated, regards,

East-coast hopeless.

 

[Dick Kusleika]

http://www.dailydoseofexcel.com/archives/2004/10/25/incrementing-invoice-numbers-with-a-text-file/

There's a few additiona techniques there (be sure to read the comments), but
there's nothing wrong with the way you're doing it. I wonder why it's not
working some of the time. Can you post the code that gets the number? Does
your error checking tell you when it doesn't work or do you just notice
later?

 

[XP]

Hi,

I coded this about a year ago and the problems just started in the last
couple months. I ignored it for awhile attributing it to one-off network
issues or something but just today it happened twice. Could it be because
someone opens the DB exclusively? [noone should be doing this, but....]

To tell you the truth, I can't remember how I coded my edit checks, but I
normally would do that...I'm afraid I don't have time today to post the code,
but I will try to tweak what I have; maybe I just reacted too quickly in
thinking about dumping my current code; I did put a lot of time in developing
it.

I'll be working on this in the next week and I will post again if I can't
resolve it. Also, I will carefully check the link you kindly posted to see
whether it might be feasible to change over...thanks for posting!