Re: Retrieve data from one table, submit data to another table

Hilary Stoupa — Mon, 28 Mar 2011 21:22:43 GMT

Since you are submitting your data to the "CYLINDER_MEASUREMENTS" table, you need to have that be the table you use for your data connection.

"CYLINDER_DATA" would be a secondary data connection in your form. Your drop down where the user selects the Cylinder Id would use the Cylinder Data data source to provide it's values, most likely. You'd add rules to your Cylinder Id field to popluate other fields in your form based on the selected Cylinder Id. You'll need to apply a filter to your secondary data source when you select the value you want in your main data source field -- where "Cylinder Id" in the secondary data source is equal to Cylinder Id in the main data source.

This Office Online article may help you some, too.

Retrieve data from one table, submit data to another table

lespaul00 — Tue, 22 Mar 2011 13:44:36 GMT

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.

Data Connections

Re: Retrieve data from one table, submit data to another table

Retrieve data from one table, submit data to another table