InfoPath Dev

I have an infopath 2003 form that I created and have linked to an access 2003 database. I have two fields (AssyID and AssyNo from the database. I want to have the AssyID field display that last ID number used and increment it by 1. I want the AssyNo field display that last AssyNo used and increment by 1. I have googled and search this forum and have not been able to find an answer to this problem.

Can anyone help me.

Thank You

Ralph Dodson

Hi Ralph,

Well, both SQL and Access provide functionality to automatically increment IDs. You just need to load that ID into your form after submitting the first time and then submit again. The first submit will set the row in the DB. Then, you'll want to query the DB (create another data connection for that), get the value, and set that in the form before submitting again.

This is one of the most common forum queries. I personally have seen no less than 20 threads devoted to this issue. Most people are trying to use SQL though. Please try to search using our Google search tool: http://www.google.com/coop/cse?cx=003654972516704994317:4j0mji2l1rq 

Cheers!

Patrick,

I am still having trouble with this. I have search the forum but wasn't able to find what i was looking for. I have an existing access 2003 db and have my infopath 2003 form linked to the backend of my db. I also have secondary connections for receiving data from 2 other tables in the same db. From the main connection, I am trying to populate a field (AssyNo) to the last value in the db and have it displayed. I am VERY new to infopath so am not able to completely follow what you are recommending. I don't understand your statement to load that ID into my form after submitting the first time and then submit again. I don't understand what that means. Can you explain in more detail please. I have a beginning infopath 2003 book and am not able to find any information about this in that book.

 Can you help me?

Thanks for any help you may provide.

Ralph Dodson

Hi, Ralph:

If I may expand on Patrick's answer....

You can add another data connection to your form that receives data, and you can select your Access database as the source. Perhaps it would be easiest if you created a View in your Access database that returns the max AssyNo and the max of the other field you want incremented. Here is a blog post with more information on the max function in Access.

Then, use rules for your Submit -- you can do that if you go to Tools / Submit Options. Select to Submit via rules. Your first rule would be to query using your data connection to the view that returns the max AssyNo. Your second rule could be to set the form field value that you want populated with the AssyNo from your secondary data connection + 1. Then your third rule could be to actually submit the form.

Hope this helps!