InfoPath Dev

I have read and successfully gone through the steps in the very informative article: How-to guide: Connecting an InfoPath form to an Access database.  Also, in looking through many posts here on the forum I have seen many questions and explanations about querying from and submitting data to Access DBs via InfoPath. 

What I am doing should be even simpler, but I cannot find any reference here to anyone doing this:

I have an InfoPath 2003 form with its own (InfoPath-created) XML data which is all user-supplied.  For one field on this form, I want, through a secondary data connection, to query an Access 2003 DB and populate an InfoPath field. This query is based on an account number field which the user populates directly on the InfoPath form.

Through rules on this InfoPath Account Number field, I am successfully looking up and retrieving these dollar amounts from an Access field called "Loan Amount" to an InfoPath field called "Loan Amount".  This is working very well.

I don't need to send any data TO Access.  The Access DB is being populated/updated daily by a different means. The Loan Amount changes often, but the Account Number stays the same.  It's also a very basic DB, with only one table containing two fields:  Account Number and Loan Amount, resulting in a one-to-one relationship.

Can I work this so that the InfoPath field "Loan Amount" can update automatically whenever the Access DB is updated?  Right now I just have the Access file on my own PC, but I realize it would have to be put somewhere else when actual users start to use the form. 

I would prefer that the users would not even need to open the form for the field to be updated (the form data is currently being submitted to a SharePoint 2007 form library), but I cannot even get the update to work upon re-opening the InfoPath form. 

Has anyone done something like this before?  Incidentally, I am not using any drop-down lists, unlike what most form designers seem to be doing with secondary data connections.  I also used no custom code.

Thanks a lot in advance,

Bob Hansen

Hi Bob Hansen and welcome,

The only way to automatically update the form when Access changes would be to add a polling loop to the form's taskpane that checks every minute for a change and if it finds one changes the value. You can't do it if the form isn't running.

Perhaps just adding a refresh button on your form that re-queries the Access DB is good enough, Just tell people to click it. Also, you can add a check before submit that verifies the values are up-to-date and if not alerts the user that a more recent value is available. These options require no code and are probably good enough for most scenarios.

Thanks a lot, Patrick.  I got some technical assistance from Microsoft, and they got me set up with both a Refresh button (which I had tried and done incorrectly before) and with an “Open and Save” rule which refreshes the data upon just opening the form.  They're both working very well.  They agreed that you have to open the form to have the balances refreshed, and now I see why.   My users will just have to deal with that extra step!  Often they would need to be opening the form to update other fields, anyway. 

Thanks again, and I really like the forum,

Bob