Creating a Query form that will return all records based on two parameters

[Dan]

I want to create a query in InfoPath 2003 that accesses Microsoft SQL.
What I want to happen is when I select a date and a facility that the
query will only return those records that are for that particular date
and for that facility.

So this is what I have:


tblAccounts
ID#
Name


tblAccessErrors
ID#
Date
Time
etc...(just more information about the error)

From that I need a query or something that I can select something from

tblAccounts.Name and from tblAccessErrors.Date then have it return all
of the records from tblAccessErrors that match the date and the name of

the account.

I hope I'm not clear as mud. Thanks for any help you can provide.

 

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

You can create an InfoPath form and bind it to tblAccessErrors using "New
from Data Connection...". tblAccessErrors will then provide the data for your
Main data source. Then you can add a secondary data source that connects to
tblAccounts and another one that connects to tblAccessErrors (again). Drag
the data fields from the Main data source and drop them into the data fields
section on the form. Drag the query fields for the date and name from to the
query fields section onto the form and convert both fields to drop-down list
boxes. Then fill these drop-down list boxes through lookups to the secondary
data sources for the name and date. When you fill out the form, the drop-down
list boxes will be populated with names and dates. You can select from each
drop-down list box and do a query. The query will return matching records
from tblAccessErrors.

 

[Dan]

That worked great only problem I have now is that it returns all of the
files from tblAccessErrors. When i run the query it does not update the
number of records show it just shows them all. But when I run the query
on a date and a name that I know dont have a corrisponding record it
does give me a error but still shows all of the records.

 

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

If it returns all the rows even after selecting a name and date, it means
that you haven't bound the query fields correctly. Make sure that you drag
fields from the queryFields under the Main data source to the form when
setting up the query section for the form. And also make sure that the
"Value" and "Display name" for the secondary data sources have the same
values as those used in the tblAccessErrors table. For example, for the Name
drop-down list box, set both the Value and Display name for the drop-down
list box to the Name field of tblAccounts and not to the ID field. For the
Date drop-down list box, set both Value and Display name to the Date field of
tblAccessErrors.