Data Connections

Re: creating an Access query based on main data source

Matt Faus — Tue, 19 Jun 2007 17:56:24 GMT

Sorry for the misunderstanding and bravo on figuring it all out! Thanks for posting your solution for everyone else!

Re: creating an Access query based on main data source

michayos — Tue, 19 Jun 2007 15:50:48 GMT

Matt,

Thanks for the advice. That might work if I were using a secondary data souce but I'm working with the primary data source.

For all those like me who searched through posting after posting throughout the many sources here's what worked...

After getting some pointers from our web developer on how to debug my code I came up with the following...

function modQuery::OnClick(eventObj)
{
// Retrieve the field in the InfoPath form whose value will be used in the
//WHERE clause
var node = XDocument.DOM.selectSingleNode("/dfs:myFields/my:trueText").text;

// Retrieve and store the SQL statement of the main data source
var strSQL = XDocument.QueryAdapter.Command;

// Generate the new SQL statement with WHERE clause
// Populate the command with the new SQL statement
XDocument.QueryAdapter.Command = strSQL + " where [praxaResponseOpen] = " + node;

//Run the query
XDocument.Query();

//Reset SQL Query statement to origonal string of the main data source
XDocument.QueryAdapter.Command = strSQL
}

I've fully tested this and it works perfectly. I later realized this could also be done with a set of rules....

1) Set a fields value: @praxaResponseOpen (from the query fields) = trueText (trueText="True" which is its default value)

2) Query using a data connection: (select your main data source connection)

This returned all records with my selected field set to the value I was looking for. What can I say...I'm an old school programmer who likes to see whats going on so I'm not used to thinking in a gui environment. This was just too easy to even occur to me.

Since I'm using this as a check to clear any flags (boolean variables) that may still be set in the database I went on with the following rules...

3) Set a fields value: @praxaResponseOpen (from the data fields) = falseText

(falseText="False" by default so this field is set to false in all records returned by the query)

4) Submit using a data connection: (select your main data source)

5) Set a fields valus: @praxaResponseOpen (from the query fields) = "" (clear the query field set earlier)

Maybe this will prevent the next person from spending a week tracking down a resolution for a similar problem...

Re: creating an Access query based on main data source

Matt Faus — Tue, 19 Jun 2007 08:57:40 GMT

Hi Michael and welcome to the forums!

You are very close! The line where you are getting the error should look more like this:

var strSQL = XDocument.DataObjects["NameOfSecondaryDataConnection"].Command;

You might also want to check out this blog post which discusses doing the same thing in a slightly easier fashion: http://www.infopathdev.com/blogs/matt/archive/2006/01/09/Create-a-Multi_2D00_View-Master_2F00_Detail-using-a-Query-Filter.aspx

creating an Access query based on main data source

michayos — Mon, 18 Jun 2007 17:32:31 GMT

Hi guys!

Ok, well I've had some java/VB programming experience a number of years ago, but I'm totally lost on what I'm missing here. Can someone please help!?

I've got an Access 2003 DB on a shared network drive and an Infopath 2003 form published to a document library in windows Sharepoint Services 2.0.

The main form has 3 buttons that will open related forms that use the same Access DB as the primary data source so that I can populate fields in the 3 additional forms based on entries in the primary form I'm referencing in this post. My goal is to use yes/no fields in the database to flag the record I want to grab data from to populate the other forms as the user clicks the button in the main form that opens them.

I've created the form using the Access DB as the primary datasource but also have additional fields in the Infopath form that are not part of the Access DB. The connection name in Infopath is "New_User_Request_DB"

I'm trying to create a connection/query statement in the form of a "select...from...where...." statement. The default data connection to the Access DB works fine because I can use the default "query", "submit", etc...buttons generated by the creation of the form from the DB. My goal here is to do a check on the yes/no field to verify all existing records have the flag cleared before I set the flag on the record of interest. But I can't get the initial query to work! (ARGH!!!) I'm sure if someone can show me where I've gone wrong, I can figure the rest out on my own (I hope!)

I get the following error for line " var strSQL = XDocument.DataObjects.QueryAdapter.Command;"

' XDocument.DataObjects.QueryAdapter.Command' is null or not an object

Here's my code so far...

function modQuery::OnClick(eventObj)
{
// Retrieve the field in the InfoPath form whose value will be used in the
//WHERE clause
var node = XDocument.DOM.selectSingleNode("/dfs:myFields/my:trueText").text;

// Retrieve the SQL statement of the data source
var strSQL = XDocument.DataObjects.QueryAdapter.Command;

// Generate the new SQL statement with WHERE clause
strSQL += " where [praxaResponseOpen] = '" + node + "'";

// Populate the command with the new SQL statement
XDocument.DataObjects.QueryAdapter.Command = strSQL;

// Run the query
XDocument.DataObjects.Query();

}

I'm not a programmer but I think I'm close....what did I miss? Anyone please????

Michael