how do I create a formula to display sequential numbers

[LemonTea]

I'm working on a purchase order system and can't figure out a reference
number. does anyone know how to do this?

 

[Scott L. Heim [MSFT]]

Hi,

Is your InfoPath solution bound to a database or web service? If so, the
easiest option would be to use the "auto increment" functionality of the
database system to create the number for you.

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[LemonTea]

Thank you, I'll try that

 

[LemonTea]

Hi,

I have tried this but get either a scrolling list of numbers of a drop down
list of numbers. Is it possible to create a link or formula to generate the
next sequential number each time the form is opened?

 

[Scott L. Heim [MSFT]]

Hi,

Here are some sample steps I created to get a number from a SQL Server
table:

- Create the following table:
Name: tblMyID
Field: MyID, int, Primary Key (NOT an Identity Field!)

- Create the following Stored Procedure:
CREATE PROCEDURE dbo.mysp_NewID
AS UPDATE dbo.tblMyID
SET MyID = MyID + 1

- Create the InfoPath form:

- Add a field on the form named: txtNew with Conditional Formatting to hide
the field
- Add another field named: field1
- Add a Secondary Data Connection to the table noted above named: NewID
- On the OnLoad event, use the following code:

Dim CN
If Xdocument.IsNew Then
Set CN = CreateObject("ADODB.Connection")

With CN
.Provider = "SQLOLEDB"
.ConnectionString = "Data Source=<your SQL Server>;Initial
Catalog=<Your Database>;Integrated Security=SSPI"
.Open
.Execute "mysp_NewID"
End With
XDocument.DataAdapters("NewID").Query
End If

This simply checks to see if the document is "New" and if so, creates a
connection to the database and executes the stored procedure to update the
number.

- Lastly, add "Rule" on the Open of the document (Tools|Form Options|Open
and Save tab|Rules) that checks to see if the hidden field (txtNew) is
blank. If it is, there is an action to set "field1" to the value returned
by the secondary data connection and another action to set some generic
text in the txtNew field so it is no longer blank.

So basically there are 2 pieces to this solution: if the document is New,
the code kicks in to execute the stored procedure. Because the document is
New, the hidden field "txtNew" is blank so the Rule kicks in to set the
value.

Obviously you could create the entire process in code but I wanted to keep
the custom code to a minimum.

I hope this helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Lisa Spencer]

I am trying to use this code to create an auto number field on an Infopath
form connected to an Access database. I am getting an "SYNTAX ERROR IN
CREATE TABLE STATEMENT" in Access when I make my update query. Here's the
code:
CREATE PROCEDURE dbo.mysp_NewID
AS UPDATE dbo.tblMyID
SET MyID = MyID + 1
Can anyone help me?
Thanks,

 

[pating]

Lisa, i dont think you can create a procedure in Access database. that code
you are trying to run is for an SQL Server database.
i have a question about Scott's procedure. what if i didn't submit the form
(and just cancelled the adding of new record), how can i set the value of
dbo.tblMyID.MyID to its previous value?
I cant use the auto increment in my case coz the ID i'm trying to generate
has formatting (like YYYYMMxxxx where YYYY is current year, MM is current
month and xxxx is the counter, which starts back to 0001 if its for a new
month and year). what i'm thinking is just to create a trigger in my table
that will fire in Insert. the trigger will create the ID for me
automatically. but i would also like to try other solutions to minimize my
SQL scripts.