We help IT Professionals succeed at work.

Use InfoPath to Write to an Access DB or Excel and Generate a New Number per Record/ Row

Rex
Rex used Ask the Experts™
on
I want to use an InfoPath form to write to one of multiple tables in access, or worksheets in Excel (I don't care which)

One sticking point is, I want the Infopath form to have something like a "Submit" button that writes to the Access or Excel files and creates a new record/ row with a unique number for each.

Of course we want significance in that number.... YY-XXXX, where YY is the two digit year the infomation was submitted, and 0001 would be the first submission. We would want XXXX to start over at 0001 the next year.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Director of Business Integration
Commented:
I would recommend SQL over Access but you can make a database type form which would submit the data to Access.

The unique ID will be calculated in the form or you can create an identity field in the database which would generate on insert. I actually recommend both probably. InfoPath must have a Identity field to allow submit.

The issue with a unique, sequential ID is keeping from having duplicates.  If you have more than one simultaneous user this is critical.

I will cover the infopath side, create a field in the form to store it each time. Create a secondary data connection to just the ID field and have it sort by the ID number, could do a top 1 as well.

On your submit button, place a rule before submit and after you validate the other data.  Do a query of your secondary to get the latest data.
Set the id field to concat(subtring(today(),3,2), "-", max(substring-after(SQLID,"-"))
Then submit
RexQuality Leader

Author

Commented:
Sorry for the delay. I am working on this. I just had to create the database to go with it first!
RexQuality Leader

Author

Commented:
Thanks. Sorry for long delay.