InfoPath Dev

I am using InfoPath and SharePoint 2003. The main data source for my form is an Access database table, but I added a SharePoint List as a secondary data source (the form automatically retrieves the data from this List). In the List, I have details for several hundred Contacts, including where they are located. I would like users to enter a criteria into the form, for example "NY", and then see all the Contacts located in NY-- similar to the 'Run Query' button that's automatically created when you design a form based on an Access table, but I am trying to query the SP List, not the database table. Because not all states have the same number of contacts, I figure I need to use a repeating section. I added a set of data to the main data source (the form itself) with all the same field names as the SP List, and added "State" and "Last_Name" to the form layout. Then I created a button to set the value of the form's (main data source) "Last_Name" field equal to the SP List's (secondary data source) "Last_Name", filtered for when the two "State" fields are equal. But when I preview the form and enter "NY" into the "State" field, then press the button, only 1 last name displays-- that of the 1st contact in the SP List located in NY. Why don't all the other NY last names appear?? I'd be tremendously grateful for any assistance.

OK - Welcome to the forum.

You must have some fields in your main data source, under data fields that you want this data stored.  The user will choose a contact state and then the contact and this will get placed in the data to be submitted to Access. Correct?

You do not really need to create another data node in your main form.

Typically you would just make the field you want to store state in, from your data fields, a dropdown control in your form.

Then have the source be the list of states from your secondary connection.

Then put a dropdown on contact name and this would use the secondary list but with a filter where state = the state chosen in the drop down (the state field under Datafields)  This would then allow them to pick the contact they want.

Then have a rule which would populate the rest of the fields based on the name they had chosen, such as phone or whatever.

Email me if you would like to see an example.

Sorry, I should have clarified that using the query fields and data fields that are automatically added to the main data source is not an option for the form initiator, who will not have access to our database.  My form works perfectly when it is only retrieving information and submitting to the database (multiple contacts per state appear in the repeating section and I can add new contacts).  The problem is that I want the form to retrieve the contact data from the SharePoint List, not the database-- it only gives me 1 contact per state.

Here are the steps I have taken:

1. Created InfoPath form from the back end Contacts table (this automatically created queryFields and dataFields in the Main Data Source).
2. Exported the Contacts table to a SharePoint List and connected the List to the form as a Secondary Data Source that automatically retrieves data.
3. Added a new node (myNode) to the Main Data Source and added fields with the same names as the columns in the Contacts table and Contacts SharePoint List.
4. Dragged State and Last Name from myNode onto the form's layout.  State is a 'text box' and Last Name is a 'repeating text box' in a repeating section.
5. Created a buttom with a Rule.  The Rule sets Last Name (from myNode) equal to Last Name (from SP), with a filter: where State (from SP) = State (from myNode).
6. Previewed form, typed "NY" into the State text box, and clicked the button.  Only the 1st contact from NY appears in the Last Name text box.

Why don't the rest of the NY contacts appear?

Any suggestions are greatly appreciated!

I would also like to see the example you described.

 Thank you.

Thank you for speaking with me, Clay!

You were a huge help.  Thanks again.