Sequential AutoNumbering using Database

[johnpinter]

I am relatively new to Infopath and to SQL.

I have hunted high and low throughout this discussion group, tried most of
the suggested solutions and none of them seem to work correctly.

The latest attempt (solution by Scott L. Heim subject:how do I create a
formula to display sequential numbers) by creating a table in SQL with a
field that is the primary key (NOT an identity), creating a stored procedure
to increment that field, adding code to the OnLoad event to test for new
document only produced the following error:

Infopath cannot create a new, blank form.
The form template is not valid.

I followed the instructions to a tee but I must be missing something.

I am trying to create a Purchase order form. The attached database contains
several tables - Creditor address details, Delivery address details,
transaction codes - all using drop down lists. I want to be able to pull the
next sequential number from a table in that database (called PONumbers) and
display it in a field on the form. I also want the form to update the last
number used so the next form that is opened gets the next number.

Can someone out there provide idiot-proof instructions to display a
sequential autonumbering field within an Infopath form using either an Access
or SQL database to control that field? I also want to know how that field is
updated.

Thank you in advance.

John

 

[G0ggy]

It's actually an age old problem. How to produce a number prior to creating a
record, and keep it sequential. The answer you have had it correct and I
personally would steer well clear of doing it. You will spend more time
trying to control the number in code than it will useful for.

Surely as long as the order number is unique then that is all that matters,
HHMMSSDDMMYYYY will create you a unique number that will not duplicated and
can be used to create an order number field.

Save yourself the pain and do not keep it sequential.

 

[johnpinter]

Thanks for that.

I appreciate the answer and, although it is an easier solution to go the
system-date-way, I really require the number to be sequential in order to tie
in with our accounts system for cross checking purposes.

Any other suggestions and fool proof solutions would be highly appreciated.