Newbie Questions

Re: Set the value of a feild on submit.

ashoke — Tue, 15 Mar 2011 14:12:37 GMT

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.

Thanks,Ash

Re: Set the value of a feild on submit.

xjoutsider — Sun, 13 Mar 2011 23:27:13 GMT

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.

Re: Set the value of a feild on submit.

ashoke — Mon, 07 Mar 2011 15:43:14 GMT

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

Re: Set the value of a feild on submit.

Patrick Halstead — Mon, 07 Mar 2011 09:34:48 GMT

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.

Re: Set the value of a feild on submit.

xjoutsider — Sat, 05 Mar 2011 20:04:50 GMT

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

Set the value of a feild on submit.

xjoutsider — Sat, 05 Mar 2011 13:55:22 GMT

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