InfoPath Dev

I want to set the value of my Customer ID on submit  of the form. I would like to use auto ID generated by Access + leading zeros. In excel I could do it like this =RIGHT("0000000000"&B1,6). I am not sure how it would look in InfoPath.  So on submit it should submit 000001 to the database once to 10 it would be 000010 and so on.

 Thanks

Or could this be set  with the defualt field value of Customer ID  

You can certainly set it to a value in the form (latter approach) or submit to Access and requery to get the ID, set the field and submit again.

Hi, Hope you have got the solution from previous post. But I would prefer following simple solution which is a kind of same thing:

 Access AutoNumber field Format to "0000000000". It keeps the length of the field 10 digits long from right keeping the leading zeros.

 You do not need to create an InfoPath form field/node or you can create a read only field (I prefer to make it visible to the users).

 Thanks,

Ash

I changes the formating in the access Db of the ID feild. to get the value correct but it does not transfer to the form. how do you get the formatting to transfer to the form when queried. 

Hi There - You are right. Access db formatting gets the right number of digits on submission but it doesn't appear on the InfoPath query form. The form takes actual number with no leading zero if you try to view it in any way either by text box or expression box. Interestingly, the Access report view takes the formatting with the leading zeros which was my prediction that it would work for InfoPath query form as well. You can run a query using another text field that will take values from this field with added sets of rules.

Say for example, you've created ID field in the Access db AutoNumber and getting number such as ...., 7, 8, 9, 10, 11. It doesn't matter whether you've formatted in db or not. It also doesn't matter whether you show it on your input form or not. Because this ID field as AutoNumber will get its value in db automatically. To view the ID field in the desired format, you can create another field. Say this is ID_F. Note that as a requirement that this field is to be in the Access db.

In text box properties dialogue box of the ID_F: Data tab: Value fx box will be @ID. Click the Rules button and set the rules as follows:

Rule 1: Condition: ID < 10 and Action: =fx Set a field's value, Field: . , Value: concat("000000000", @ID) or whatever the number of zeros required.

Rule 2: Condition: ID >= 10 and Action: =fx Set a field's value, Field: . , Value: concat("00000000", @ID). There is one zero less as the ID digit is increasing by one digit.

And so on.

You can add comma if you want.

I would prefer to make the field read-only on Display tab of the text box properties dialogue box.