Tomorrow came soon.
Got an idea and had to propose it before I forgot it.
Create a query in Access for each one of the periods you want to filter on.
Let's call them period 1 (due today or tomorrow), period 2 (due in 2 to 6
days), and period 3 (due in 7 to 14 days). So you would have 3 queries
returning issues that are due in a certain period.
In the SELECT of each query, add an extra non-existent field called "period"
and fill it with a number. For example, if the SELECT for the first query says
SELECT *
FROM
you change this to
SELECT *, 1 AS [period]
FROM
For the second query this would be SELECT *, 2 AS [period] FROM
and for the third query this would be SELECT *, 3 AS [period] FROM
Then create an all-encompassing query, let's call it UnionQuery, that is a
union of the 3 queries, so
SELECT * FROM Query1
UNION
SELECT * FROM Query2
UNION
SELECT * FROM Query3
Note: All the queries must have the same amount of fields in the same order.
Then switch to InfoPath and create a new form from data connection and
choose UnionQuery as your Main data source. Drag the dataFields to the form.
Drag the queryFields to the form. Change the "period" field in the
queryFields to a drop-down list box and pre-populate it with static values 1,
2, and 3. Add corresponding display names, e.g. "Due today or tomorrow", "Due
in 2 to 6 days", and "Due in 7 to 14 days".
Test the form. If you do not select anything in the period drop-down and
click on the [Run Query] button, you should get all the records back from the
UnionQuery. If you select a period (1, 2, or 3) and click on the [Run Query]
button, you should get back only the records for the selected period.
Let me know if this works for you.
---
S.Y.M. Wong-A-Ton
Adam Harding said:
Thanks again
Look forward to hearing from you tomorrow
:
The functionality you require should be possible with code, but I might be
able to find a way to do it without.
I'll get back to you tomorrow.
---
S.Y.M. Wong-A-Ton
:
OK
Thanks for your reply
Purpose of the form is to query the database to find out about existing
records logged from another form. It queries outstanding issues by several
criteria such as creator, issue number, email address, last user etc. These
all work fine.
On the SubmissionForm there is a field called TargetResolutionDate and this
is what is causing me problems. The QueryForm needs to be able to pull up
all records that are overdue, due today or tomorrow, due in 2 to 6 days time
and due in 7 to 14 days time in combination with the other criteria.
I thought the only way to solve this issue was to write my queries in the
database and use its result to populate my form. And it is that which is
casuing me problems.
If there is a better way to do this without using queries at all i would
love to find out about it, as long as there is not too much code to write as
i have only ever written macros before and my form is in javascript mode.
Hope this helps and thanks for your patience
Adam
:
Adam,
I think we have to take this one step at a time...
What is the purpose of the fields in the main data source? Is it that you
want to populate the fields in the main data source from the secondary data
source? I'm not sure whether you can do that if the fields in your Main DOM
are tied to the database. Are they tied to the database? That is, did you use
"New from Data Connection" when creating your form?
If you aren't going to submit back to the database, it is best to just
create the fields in your Main data source by hand and not tie them to the
database. Then you should be able to populate them with whatever data you
like.
---
S.Y.M. Wong-A-Ton
:
No submission at all on this form
Still haveing problems
I have set my secondary data source to have the exact replica of the main
data source, where the secondary datasource is a series of 3 queries in an
access database. I am not having any joy in getting any further though.
I did a test where i set the button to display the result of data connection
called
qry_TargetResDatePassed that pulls back 7 rows of cascading data in access.
I got the button to set the value of the URN to match the value of the URN
in the secondary data source not knowing how to pass the data across and it
only pulled back one result. The URN is held in a repeating section called
Tbl_FSSIssues that repeats beneath Tbl_FSSUsers that is the master table.
My question is How do i get the multiple data from the source into the
datafields on the form? Setting it field by field does not work so what is
the best way to compose it?
Cheers Adam
:
Adam,
Just one more question: Are you using the the data source tied to your Main
data source now to submit back to the database? If so, I'd like to make one
remark that you cannot use the secondary data sources to submit back to the
database. The secondary data sources would be purely to retrieve and show
records.
---
S.Y.M. Wong-A-Ton
:
I am not acquainted with code unfortunately so i think the latter solution
would have to work.
With regards to that, why would i need to switch views? My current view has
all the details to display multiple records as it is a repeating section but
I am having problems getting any data to paste into the repeating section
from these queries. Do my datafields on the query need to exactly match the
datafields on the form to get this to work?
If you thought code was a better way to do it then i can stumble my way
through it if you could provide a few pointers.
Cheers Adam
:
Adam,
I'm not sure if I understood your problem correctly, so feel free to correct
me if I'm wrong.
You have tied your form to an Access database, meaning that you have
dataFields and queryFields in your Main data source. Now you would like to
use the queryFields to retrieve records and filter them on date periods. And
what you are trying to do now is pre-populate the queryFields to be able to
search on more than 1 date.
One problem with using the standard queryFields of InfoPath is that you can
only perform exact searches with them, i.e., if you are searching on dates
you can only search on 1 exact date. As soon as you want more flexibility,
you have to resort to code, which is what I would suggest for your scenario,
but am not sure if this is feasible.
Another option is to create the queries in Access (1 for each date period),
create a secondary data connection for each Access query in InfoPath, and
then create a view to show the results for each secondary data source
separately. You could set up a mechanism to be able to switch between the
views for each query result set. In this solution, all the filtering is done
in Access and InfoPath is only used to show the results. It's a very static
solution, but could get the job done.
Perhaps I might come up with more ideas; if I do I'll let you know...
---
S.Y.M. Wong-A-Ton
:
S.Y.M Wong-A-Ton
Completely agreed my problem is with the Infopath side of things. I am
trying to publish the URN of each case to a query field so that they can
click search to pull back to whole record. I can't get a repeating query
field so it cannot display more than one.
I have also tried replicating the queries I have setup successfully from
Infopath to Access and back. Here i have queryfields that the user fills in,
clicks search and returns results into a repeating section allowing the
display of more than one record.
My problem comes in that when trying to use the query to do this it does not
work at all. Currently the fields in the qry do not exactly match the fields
in the repeating section so I am presuming this presents a problem.
In conclusion I have to make a choice, to allow my button to set the value
of a repeating queryfield, which may not be possbile although if it is it
seems more simple. Or to publish the results of the Access Qry directly to
the datafields on my query form which are already repeating but is something
i am unable to faciltate at the moment.
Ms. Wong-A-Ton could you please offer a suggestion as to the best way to
proceed. FYI No rules or filters are being used on these queries.
Many thanks for your advice so far. I am also sorry in that I did not make
my initial question specific enough as it was my belief that the problem lay
in the form not the database.
Adam Harding
(e-mail address removed)
:
Although I set up my query another way using a BETWEEN... AND... syntax in
the WHERE-clause, it did not matter. I still cannot make the Access -
InfoPath connection fail.
Since you said that your query returned the correct amount of records in
Access, I suspect that the problem is somewhere within the setup of controls
on your form (and perhaps rules or filters) and not in Access or the data
connection between InfoPath and Access.
---
S.Y.M. Wong-A-Ton
:
Mrs S.Y.M. Wong-A-Ton
My access query says
DateAdd("d",Now(),2) Or DateAdd("d",Now(),3) Or DateAdd("d",Now(),4) Or
DateAdd("d",Now(),5) Or DateAdd("d",Now(),6) that is the criteria
on the field TargetResolutionDate that works brilliantly in Access.
I have created a query form which users can enter detail to search on from
the main data connection which can pull back multiple records as my
datafields are all in a repeating section which has a repeating table in it.
This query is attached as a secondary data connection that is opened when
the form is openend. I have a button that forces the results of the query
into the datafields below, but it only pulls back one record, depsite there
being two or more.
:
Perhaps you should describe some more how you set up the form and what your
query looks like in Access. I can successfully create a query in Access and
use it in InfoPath as a View from a secondary data source, so am unable to
reproduce your problem.
---
S.Y.M. Wong-A-Ton
"Adam Harding" wrote:
