Hello,
I've been struggling with getting a simple InfoPath 2007 form to work with a test MS Access 2007 database. I feel the best way to explain what I am doing is by giving you an example.
Background of scenario:
Lets say I am a distributor of gas cylinders. I have 100 different gas cylinders that I fill and send to customers. When they are done with the gas, they return the cylinders and I fill them back up. Each cylinder has specific data related to each: manufacturer, serial #, specific cylinder ID, and material of construction (carbon steel, stainless steel, etc). I have this data in my MS Access database, in an individual table called "CYLINDER_DATA".
Every time I refill a cylinder, I need to record the date and measure the pressure and temperature (among other things) of the gas in the cylinder. This data (along with the specific "CYLINDER_DATA" at this date of measurement) must be added as a new record in a different table called "CYLINDER_MEASUREMENTS" in the same database.
What I would like to do:
So, I want to create an InfoPath form for operators to use whenever they need to perform measurements on a newly filled cylinder. So, I created an InfoPath form, that starts with a drop-down list box control that is populated with the 100 different cylinder IDs. A user would choose one of the "cylinder IDs" from the drop down list and then click "retrieve data". This would then populate the fields directly below: manufacturer, serial #, material of construction, etc from the "CYLINDER_DATA" table.
Below this section would be a section to "fill in" measurements to the "CYLINDER_MEASUREMENTS" table. It would have blank fields for "pressure", "temperature", "date", and other measurement fields. Once filled in, the operator would hit "submit" and a new record in the "CYLINDER_MEASUREMENTS" table would be created. This new record would included the everything populated on the InfoPath form (the cylinder specific data AND the measurements on the given date).
What I've tried doing:
I have my database set up in the two tables outlined above. I created an InfoPath form with the "CYLINDER_DATA" as the Main Connection. I am easily able to retrieve cylinder specific data from the CYLINDER_DATA table into my form by use of the queryfields and datafields. However, I cannot seem to get the "submit" to go to the "CYLINDER_MEASUREMENTS" table. The form seems to only want to submit to the "CYLINDER_DATA" table.
I also tried making the "CYLINDER_MEASUREMENTS" table the Main Connection, so I could submit to it. But then, it seems that I cannot create a database query to "retrieve" data from the "CYLINDER_DATA" table.
Where to go from here:
I am looking to get any insight from you to see how I can configure this appropriately to get what I need. I also do not want the user to be able to change the data in the "CYLINDER_DATA" table, so I would likely have those fields "read-only". Any information/support is appreciated.
Thank you.