Query and update on one form

[Jeffshex]

I don't know if this is possible, but I'm wondering if Infopath can do this.
I'd like to query by 2 drop down lists, Name and Department, and a date range

Since 1 person will have mulitple entries per date range, I'm guessing I'll
need record selectors so I can scroll through the few entries until I get to
the one I want.

Am I asking for too much?
If not, any good tips, hints, etc... would be great!

Thanks

 

[Scott L. Heim [MSFT]]

Hi Jeff,

There is no reason why you should not be able to query my multiple fields
such as name and department. However, a date "range" is a different story.
In order to implement a date range query you will need to write custom
script for this:

826992: How To: Use Wildcard Characters in an InfoPath Form Query When Bind
http://support.microsoft.com/default.aspx?scid=kb;EN-US;826992

Now I am assuming your InfoPath solution is based on a database (Access or
SQL Server) - if so, once you execute the query, the results returned to
the form should be updateable.

I hope this helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jeffshex]

Can I do that all on one view?
Also,
can you point me to an example of a date range query in Infopath?
I'm not a "custom code" person, more of a copy and paste and edit type.

Thanks Scott!
-Jeff

 

[Scott L. Heim [MSFT]]

Hi Jeff,

Assuming you have SP1 for InfoPath (Office 2003) installed - yes.

With regard to a date range query, I am not aware of anything "specific"
already documented on this; however, the article I referenced previously
has everything you need...but it will need to be customized to your
solution.

If you need a small sample against, say, the Northwind sample database
(i.e. the Orders table) I can provide that if you would like.

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Scott L. Heim [MSFT]]

Hi Jeff,

Here are sample steps to get this working using the Orders table from the
SQL Server Northwind sample database:

- Create a new InfoPath solution using the Orders table from the SQL Server
Northwind database (for this sample, you only need the fields: OrderID,
CustomerID, EmployeeID, OrderDate, ShipVia & Freight)
- From the Tools menu choose Form Options, select the Advanced Tab and be
sure the Form Code Language is set to: VBScript
- Display the Data Source Task Pane - you should see both queryFields and
dataFields
- Right-click on "myFields" and choose Add
- In the Name field enter: txtStartDate and click OK
- Right-click on "myFields" and choose Add
- In the Name field enter: txtEndDate and click OK
- Drag the txtStartDate and txtEndDate fields to the query section of your
View
- Expand dataFields in the Data Source Task Pane, drag Orders to the data
section of your View and choose Repeating Section with Controls
- Right-click on the Run Query button and choose Properties
- Change the Action to: Rules and Custom Code
- Click the Edit Form Code button - you should then see the following:

Sub CTRL2_8_OnClick(eventObj)
' Write your code here
End Sub

- Immediately below the "Write Your Code Here" line, add the following:

'Store the original command text of the query adapter
Dim strOriginalCommand
strOriginalCommand = XDocument.QueryAdapter.Command

'Get the query node that you want to modify.
Dim queryOrders
Set queryOrders =
XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:queryFields/q:Orders")

'Get the start and end dates entered by the user
Dim objStart
Set objStart =
XDocument.DOM.selectSingleNode("//dfs:myFields/my:txtStartDate")

Dim objEnd
Set objEnd = XDocument.DOM.selectSingleNode("//dfs:myFields/my:txtEndDate")

'Build the new SQL command
Dim strSQL
strSQL = strOriginalCommand & " WHERE OrderDate >= '" & objStart.text & "'
And OrderDate <= '" & objEnd.text & "'"

'Set the query adapter command to the new SQL text
XDocument.QueryAdapter.Command = strSQL

'Execute the query
XDocument.Query

'Reset the query adapter back to the original command so it is ready for
the next query
XDocument.QueryAdapter.Command = strOriginalCommand

- From the File menu choose Save
- From the File menu choose Exit - you should be back to your InfoPath form
in Design View
- Preview the form
- In the txtStartDate box, enter: 7/1/1996
- In the txtEndDate box, enter: 7/31/1996
- Click the Rules and Custom Code button - result: you should only have
orders returned where the OrderDate is between 7/1/1996 and 7/31/1996!

Now as you can see, the code above is needed to make this work. This sample
only uses one table - when you begin to introduce additional tables (i.e.
Customers so you can show customer information) this gets significantly
more difficult because of the way the SQL command is built by InfoPath. You
get into needing to "parse" that command text so you insert the "where"
condition at the appropriate location.

So, if you only have one table you are using it's not too difficult to
query on a date range; however, if that is not the case you may want to
think about whether this condition is truly necessary.

I hope this helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jeffshex]

I will try that out and see how it works. I only have one table, so
hopefully this will be easier than multiple tables.
One question for you though.
When it displays the results of the query, does it show them all at once
like repeating, or one record at a time, or either or?

Thanks Scott.
-Jeff

 

[Jeffshex]

Scott,

Any idea as to this error?
The following error occurred:

The query method on the Document object failed.
InfoPath cannot run the specified query.
[0x80040E07][Microsoft JET Database Engine] Data type mismatch in criteria
expression.
???????? It points to the line XDocument.Query on the debugger.

 

[Scott L. Heim [MSFT]]

Hi Jeff,

Basically, the error is indicating the issue: there is a "data type
mismatch" in the query criteria. So as an example, when you execute a query
against SQL Server for a date, the date must be enclosed in apostrophe's
(i.e. '7/25/2005'); however, if you use that same criteria against an
Access database, it will generate the error you describe because Access
expects its dates to be enclosed in "pound" signs (i.e. #7/25/2005#).

I hope this helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jeffshex]

Yeah, that did the trick.
So now that I have all those entries showing from the query. If I throw on
a submit button, it would save any changes to those line items?
Could I also add on a drop down list box to narrow the results to that
CustomerID name?

 

[Scott L. Heim [MSFT]]

Hi Jeff,

I will answer you with...."theoretically yes!" <G>

Assuming you do not have any "long" data types in your database (i.e. Memo,
OLE Object, etc. for Access, Image, Text, etc. for SQL Server) then yes -
adding a Submit button should submit any changes to the data back to the
database.

With regard to adding a drop-down list box so the user can select a
customer - this can certainly be added, you would just need to modify the
code to include this additional criterion.

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jeffshex]

I don't think I'm doing this right.
**I am trying this out on my test database..not the Northwind.**
It pulls up ok with the date parameter. I'm trying to add in the Name and
am havin a tough time. I created another field...just like we did with the
dates, but this is the drop down that gathers the names from the database. I
think I'm screwing up in the query part of it.
What do you think?
------------------------------------------------------
Dim objName
Set objName = XDocument.DOM.selectSingleNode("//dfs:myFields/my:txtName")

'Build the new SQL command
Dim strSQL
strSQL = strOriginalCommand & " WHERE Date >= #" & objStart.text & "# And
Date <= #" & objEnd.text & "# & Name = objName.text"

 

[Scott L. Heim [MSFT]]

Hi Jeff,

If you are adding criterion that is taking a "string" variable then I don't
believe your strSQL is correct. I would modify it as follows and test:

'Build the new SQL command
Dim strSQL
strSQL = strOriginalCommand & " WHERE Date >= #" & objStart.text & "# And
Date <= #" & objEnd.text & "# And Name = '" & objName.text & "'"

** NOTE: Just to be clear on the punctuation: just after "Name = " is an
apostrophe (single quote) and then a quote (double quote) and after the
last objName.text is a quote (double quote), an apostrophe (single quote)
and another quote (double quote.)

I hope this helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jeffshex]

Beautiful!!!!
I knew what had to be done...just didn't know how to write it.
I'm catching on!!!

You are a man of many talents Scott.

-Your friend Jeff

 

[Scott L. Heim [MSFT]]

Hi Jeff,

Excellent! Glad to hear it's working now.

Take care,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jeffshex]

Scott,

I liked how that query repeated the results in the repeating section. Is
this possible to do using a secondary data source using a different view? I
have another data source (not the one we were prevoiusly working with) that
has project numbers, titles, and managers. I'm trying to get all open
projects listed in the same manner as we did the query with the date range.
Since this Project List isn't the main connection, is this possible?
I'm a little unsure
Thanks,
Jeff

 

[Scott L. Heim [MSFT]]

Hi Jeff,

You can do basically the same thing but accessing the data from the
secondary data source will be different. Here again are some sample steps
for you to test. I would suggest first creating a new, blank InfoPath form
to use for this sample:

- Create a new, blank InfoPath form
- Add 2 text boxes (txtStartDate and txtEndDate) and a button to the form
- Add a secondary data source to the Orders table from the Access Northwind
database but modify the SQL to the following (this is just to limit the
records

select [OrderID],[CustomerID],[EmployeeID],[OrderDate],[ShipVia],[Freight]
from [Orders] WHERE [CustomerID] Like "A%"

- Display the Data Source Task Pane and drag the Orders table from the data
source to the InfoPath form as a Repeating Table
- Right-click on the button, choose Properties and click the Edit Form Code
button
- Add the following code:

Dim objOrderAdapter
Dim objStartDate
Dim objEndDate
Dim strOrigSQLCommand
Dim strMySQLCommand
Dim strSQLQuery

'Get a reference to the secondary data source
Set objOrderAdapter = XDocument.DataObjects("Orders")

'Store the original SQL Command
strOrigSQLCommand = objOrderAdapter.queryAdapter.Command

'Get a reference to the StartDate and EndDate fields on the form
Set objStartDate =
XDocument.DOM.selectSingleNode("//my:myFields/my:txtStartDate")
Set objEndDate =
XDocument.DOM.selectSingleNode("//my:myFields/my:txtEndDate")

'Create the WHERE clause to be used to filter the records
strMySQLCommand = "OrderDate >= #" & objStartDate.text & "# And OrderDate
<= #" & objEndDate.text & "#"

'Create the new SQL command
strSQLQuery = strOrigSQLCommand
strSQLQuery = strSQLQuery & " AND " & strMySQLCommand

'Set the command text of the query adapter to the new command
objOrderAdapter.queryAdapter.Command = strSQLQuery

'Execute the query
objOrderAdapter.Query

'Set the queryAdapter back to the original command
objOrderAdapter.queryAdapter.Command = strOrigSQLCommand

- Save and Preview the form - you should see only orders for customers
where the CustomerID begins with the letter "A"
- Test by entering 4/1/1998 in the txtStartDate field and 4/30/1998 in the
txtEndDate field - result: your records should now be filtered further!

I realize this is not *exactly* what you were trying to accomplish but
because the Orders table is a secondary data source you will need to access
it differently than before - the code demonstrates what you will need to do.

I hope this is helpful!

Take care Jeff,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[ridawg]

Scott,

Do you have an example of how to do this with 2 tables. I know you mention
it is more difficult. Basically, I have two related tables and I'm trying to
create a view that brings them together.
Brief overview:

1st view essentially allows the user to search against the database. I
basically return a couple of fields with a Details button.

The idea is to let the user click on the Details button. This would execute
a query returning all the fields from the two tables for that record and
switch to the Details View.

Any help would be much appreciated!

 

[S.Y.M. Wong-A-Ton]

Not too long ago ssaral had a similar issue using 2 related tables and
wanting to query these. Try searching this newsgroup on "Secondary data
source query" to see how the shape command must be put together to be able to
filter data returned by a query using a join between 2 tables and
WHERE-clause. It should help you get started.

 

[ridawg]

Thanks! I was able to find the examples you mentioned and was able to get
what I needed to work.

Do you have or know of any examples of the New Record, Delete, and Submit
button default functionality in VBScript? I'm looking to essentially do the
exact same thing as the New Record button for example but I would like to add
some additional code to it to switch to a different view when it is clicked.
Thanks!