zintech
asked on
SQL Join statement not working in InfoPath
I am using InfoPath to query two different tables. The SQL syntax itself is fine. The SQL syntax is:
SELECT PurchOrd.PONbr,PurOrdDet.I
When I click the button, all of the fields that are in the PurchOrd table show textboxes, even if I did not select the field data to be shown. The fields for the PurOrdDet table do not show up, even if I selected them to be shown. Both tables are in the same datasource. I have even tried using separate datasources and using a join statement in the SQL but it still does not work.
SELECT PurchOrd.PONbr,PurOrdDet.I
When I click the button, all of the fields that are in the PurchOrd table show textboxes, even if I did not select the field data to be shown. The fields for the PurOrdDet table do not show up, even if I selected them to be shown. Both tables are in the same datasource. I have even tried using separate datasources and using a join statement in the SQL but it still does not work.
ASKER
No I have not. Which view would I use?
Go into SQL and create a view with your select statement.
Then when adding the datasource in IP, select that view as the table.
Then when adding the datasource in IP, select that view as the table.
ASKER
I created the view like you suggested, clayfox. I cannot see if it works because every time I create a new data source, and select the view as the datasource, InfoPath crashes and restarts. It does this anytime I create a new datasource. I ran Office Diagnostics but that did not find aynthing wrong
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have encountered this issue in other forms as well, in various forms. On some forms, it would only lock up all the time when I edited the SQL statement for pulling the data, but work fine if I just selected the fields to pull. Yeah, I do use the data connection wizard in InfoPath.
ASKER
I uninstalled and reinstalled Office to see if that was the problem, but it still happened. I am going to create a whole new form, as the form I am using may be corrupted.
ASKER
I created the new form, and it allows me to add tables and other views as my datasource, just not the one I need to use. I created the view I was using again from scratch, but InfoPath still gives me the same error. It locks up and restarts. I don't know what to do
and using that as the datasource?