General

Re: Repeating table and query fields problem

Patrick Halstead — Wed, 22 Dec 2004 09:42:56 GMT

Thanks for sharing! :)

Re: Repeating table and query fields problem

crashoverride — Tue, 21 Dec 2004 19:35:18 GMT

I created a group in the main data source with all the fields that my user would need for their input values. (The group looks much like the ones generated by the main data source because it has a repeating group under it with all the relevant fields) I then created a repeating table and binded it to the repeating group. I created a button that executes code when it is clicked in order to query and submit each line to the database. Here is the code behind the button (VBScript):

Sub CTRL12_7_OnClick(eventObj)

Dim queryString
Dim MoneyBalance
Dim objNodeList
Dim objNodeList2
Dim i

'Below is a command that gets all the data from the repeating table in the form

set objNodeList = XDocument.DOM.selectNodes("/dfs:myFields/my:group/my:repeatingGroup")

'Below is a loop which goes through each line in the repeating table and builds a query string based on the inputs, then queries it against the database to find the correct record to change, then changes the MoneyBal data field of that record, and then submits it back to the database

for i=0 to (objNodeList.length - 1)
MoneyBalance = objNodeList.Item(i).getAttribute("my:MoneyBal")

queryString = "Select * from PortfolioTable where PortfolioID="
queryString = queryString + objNodeList.Item(i).getAttribute("my:PID") + " and RptYear='"
queryString = queryString + objNodeList.Item(i).getAttribute("my:rptY") + "' and RptMonth='"
queryString = queryString + objNodeList.Item(i).getAttribute("my:rptM") + "' and FundID="
queryString = queryString + objNodeList.Item(i).getAttribute("my:fund")

XDocument.QueryAdapter.Command = queryString
XDocument.Query()

'After the query is executed, there should only be one record in the data source, and this is the one that is changed and submitted back to the database

set objNodeList2 = XDocument.DOM.selectNodes("/dfs:myFields/dfs:dataFields/d:PortfolioTable")
objNodeList2.item(0).setAttribute "MoneyBal", MoneyBalance
XDocument.Submit()

next

msgbox "The form was submitted"
Application.XDocuments.Close(0)

End Sub

Re: Repeating table and query fields problem

Greg Collins — Tue, 21 Dec 2004 19:01:38 GMT

Glad to hear it! Do you mind sharing your final solution?

Greg Collins [InfoPath MVP]

Re: Repeating table and query fields problem

crashoverride — Tue, 21 Dec 2004 18:43:57 GMT

Problem solved.

Re: Repeating table and query fields problem

crashoverride — Mon, 20 Dec 2004 19:04:53 GMT

I created a seperate repeating table in the main data source (for user inputs) that is independent of the data fields. Can someone provide me with some help with the code on how to take the user inputs and submit them to the database?

My best guess involves using a loop to capture each line in the repeating table. For each line, I will create a query string based on the inputs and then query and submit to the database using the lines

XDocument.QueryAdapter.Command = myInputString
XDocument.Query()
XDocument.Submit()

Does this seem reasonable or is there a better way?

Re: Repeating table and query fields problem

Greg Collins — Thu, 18 Nov 2004 18:48:21 GMT

When you can't do what you want with what InfoPath provides, its time to start looking at building your own stuff.

Here's what I suggest:

1. Create a secondary data source that will be your repeating table of {PortfolioID} {FundID} {RptMonth} {RptYear}.
2. Use the repeating table bound to the secondary data source for the user to enter their query values.
3. Use code on a "Query" button which will generate the SQL query statement by combining all of the table rows together into a valid SQL statement.
4. Drop this built query string into the main data source query.
5. Force the query to happen using code.

Greg Collins [InfoPath MVP]

Re: Repeating table and query fields problem

crashoverride — Mon, 15 Nov 2004 22:17:28 GMT

Here is a clarification of what I am trying to do:

I want a user to be able to enter data for any number of funds at once (i.e., in a tabular format so they dont have to reopen the form over and over to do the same thing for different funds):

PortfolioID FundID RptMonth RptYear MoneyBalance
1 1 Jan 2004 <edit this>
1 2 Jan 2004 <edit this>
1 6 Dec 2003 <edit this>
etc

In order to edit the MoneyBalance fields however, I need to first query the portfolioID, FundID, RptMonth, and ReportYear fields of each row.

Re: Repeating table and query fields problem

Greg Collins — Mon, 15 Nov 2004 22:04:16 GMT

Have you tried using a wildcard for the fund? If you want the user to be able to search across funds, then why not just build that into your query, either by the user typing a wildcard, or by using custom code to build your query before submitting it.

Greg Collins [InfoPath MVP]

Re: Repeating table and query fields problem

crashoverride — Mon, 15 Nov 2004 21:47:49 GMT

Here is an example of what I am trying to do:

I have a table in an Access database that has information about investments. The table lists the returns for different mutual funds in different portfolios at different times. The fields are FundID, PortfolioID, ReportMonth, ReportYear, ReturnPCT, and MoneyBalance.

A user creates a record by entering the ReturnPCT for a given month and year for a given fund and portfolio. A user is able to create multiple records for multiple funds at one time through the use of a repeating table. This is done through a seperate form which I have working properly.

My problem is that because of reporting timing, a user must enter the MoneyBalance for a fund a few weeks after the record has been created for a particular fund in a particular month and year. (i.e., when the user creates fund records via the other form, the MoneyBalance field is blank for a few weeks). Thus, a user must be able to query the database on FundID, PortfolioID, ReportMonth, and ReportYear to get the appropriate MoneyBalance field and then enter the correct figure. I want the user to be able to do this for multiple funds at once, just as they created multiple records at once. I want them to be able to make several queries at once, in essence a table of queries:

PortfolioID(Q1) FundID(Q1) RptMonth(Q1) RptYear(Q1) MoneyBalance
PortfolioID(Q2) FundID(Q2) RptMonth(Q2) RptYear(Q2) MoneyBalance
PortfolioID(Q3) FundID(Q3) RptMonth(Q3) RptYear(Q3) MoneyBalance
etc

Any help would be much appreciated!!!

Re: Repeating table and query fields problem

Greg Collins — Mon, 15 Nov 2004 18:42:06 GMT

Maybe I'm not understanding what exactly you want to do.

When you query, you query in a single set of fields. The query itself RETURNS multiple rows. Those returned rows are populated into the datafields. That is where you do your editing and submitting. The query is only to get the multiple rows into the datafields... not to do the editing itself.

Greg Collins [InfoPath MVP]

Re: Repeating table and query fields problem

crashoverride — Mon, 15 Nov 2004 16:40:18 GMT

But I also need to query multiple records at once in order to make the changes to those multiple records...any idea how to do this?

Re: Repeating table and query fields problem

Greg Collins — Thu, 11 Nov 2004 18:40:47 GMT

What you are experiencing is the correct behavior. The query fields are not for data editing and submission, that's what the data fields are for. The query fields are only to "query" the database and populate the data fields.

You need to bind your repeating table to the data fields for data entry and submission.

Greg Collins [InfoPath MVP]

Repeating table and query fields problem

crashoverride — Wed, 03 Nov 2004 21:05:11 GMT

I am having a problem because Infopath will not allow me to bind a repeating table to query fields.

What I need to do is be able to edit and submit several rows of records in an Access database at one time from my infopath form.