InfoPath Dev

Hi everybody, I'm super new to Infopath, so please bare with me. 

Ive created a form which I've connected to my Access 2007 DB to receive data.  I've successfully created a drop down where the user can choose which customer's info to populate the form with.  I can get a date of birth field to update every time I select a different customer in the drop down list.  However, I want to have the form include a list of allergies the customer may have.  So I've been lead to trying to use a repeating text box.  The problem is, I want the values of these repeating text boxes (and number of text boxes on the form) to update each time I select a new customer with my drop down.  It won't let me enter a default value like I did with the date of birth and I can't seem to find a way to tell it when to run this query.  Please help!

Thanks,

Eric

Depending on how you've set up your datasource/tables in the database your description sounds like the workflow is like this:

1. Open form

2. Select customer name

3. Data from database will auto-populate

4. Add to list of allergies

5. Submit changes

6. Select another customer name

7. Data from database will auto-populate

8. Add to list of allergies.

repeat

In your database I would expect to find a table which represents a one-to-many relationship for each customer unique id relating to a set of records containing their allergies.
e.g.

Table 1 - Customers
custId (autonumber)     Name
1                                   Bob
2                                   Jane

Table 2 - Allergies
recordId (autonumber)  custId    Allergy
1                                   1           Bees
2                                   1           Dust
3                                   2           Pink
4                                   2           Peanuts

Your form main data source should include both tables with a relationship to link the tables together (the wizard usually lets you continue to 'add table' to the datasource and defined which fields in each table are linked)

When you Query your main datasource, the allergy table records will also be retrieved and the number of repeating text boxes (or any other repeating control) should alter to match the number of records and display them.
If you allow adding to the repeating text boxes/controls then after doing so the user must Submit the changes - otherwise the database won't be told to add the new allergy records.

Then you can allow a new customer name selection, requery the database - and repeat as needed.
You could put the rule to requery the database on the drop down list of names.

A key point is that in database design it is often necessary to have a 'record Id' (primary unique key) field so that applications such as Infopath can easily identify which record in a table they need to update.
I generally find a plain Autonumber field is fine.

Without a sufficient unique field (or set of fields) the program may produce an error/warning, or simply not allow you to make any submit or record changes.

Thanks for the reply!  

I've learned a couple of things from your reply so far, the main one being that I can add multiple tables to my recordsource.  So I am making progress, but not there yet.

In your example above, say I choose Bob (custID == 1) from my drop down list.  I want the allergies section only to display the records where custID == 1, so in this case Bees and Dust.  So far, I have only been able to get the allergies section to display the entire allergies table (every instance of every customer's allergies).  I would like this allergies section to be blank when the form is loaded, and populate once a customer name is selected from the dropdown list. 

Also, this form will only receive data, not being used to submit.

No submit? So purely a display of records in the database. This means no new information will be expected to be added to the form and stored anywhere?

Just checking because you need to be clear on which paths to take before proceeding, or you might end up having a messier solution down the track.

The form *should* be blank when you open it and only perform a retrieval of records when the a 'Query' action is used. If you are getting records immediately I am thinking you may be working with a secondary data connection instead of the main one.
Secondary data connections to databases will only retrieve all records unless you programattically alter the SQL for the data connection before running a query.

When you add the allergies table to your datasource Infopath should ask you which fields in both tables should be 'related'.
In the example I gave, the fields to select would be Table 1 custId and Table 2 custId.

Infopath may list a bunch of other fields automatically as related if the tables have other fields with the same names. You should remove these and make sure only the actual fields which should be related are listed.

The data would only be retrieved when a customer id/name is entered into the query fields of the data connection which should only return the related customer record and allergy records.
Anyway - to help out I'll attach a database and form template with a basic demo. BRB :-D

Yeah, the few forms I want to create here are just going to be printed off and taken to meetings.  Any data that needs to be added to the database will be added another way (in the database itself). 

Anyways, I think I am following your explanation, but there is something missing...I think it lies in either the concept of the main data source vs secondary data source OR I am simply using these controls incorrectly. 

I created two queries in Access which I am using as the tables in Infopath.  One is the customer info (customerID, first+last, DOB) the other is a query of all instances of allergies and their names (customerID, allergyID, allergyName).  In Infopath, I added the allergy query table to my customer info table and I have the customerID as the relationship.  This all makes sense to me up to this point.

When I click on manage data connections I have this listed as a secondary connection...should I not have any secondary data connections at this point?  Also, how do I specify what field is going to be my 'query' field?

Are there many records in the database? If not, then you can probably continue using secondary data connections and employ some conditional formatting to get the results you are after.

Sorry for taking so long, I got caught with the over-zealous (but probably necessary) security restrictions which come with IE8 and above. Prevented me from querying my local .mdb database successfully. I'm assuming you have things working.

Anyway, unzip the attachment somewhere, you'll need to go to design mode and reconnect the main datasource to the database provided.
You'll also probably need to republish the form.

I've included how it can be done with the main data connection (reveals the Query fields I've referred to previously) or the secondary data connection using conditional formatting.

 Hey, I am a little stuck trying to get the main connection to work (I think I reconnected it correctly, but when I click requery it gives me an error saying that the main data connection is unavailable) but I am going to try more tomorrow and hopefully should figure it out.  I just wanted to say thanks for sending this to me and thanks for all your help!

 Eric

Yeah, I've been having annoying issues with my form security and databases recently. To get this to work for me today I had to go into Control Panel > Internet Options > Security Tab > Select 'Local Intranet' > drag allowed level slider to the bottom..

I don't really recommend or think that you would have to do this in a normal workplace/Domain level environment (which is what I used to work in).
Windows 7 Home seems to require the setting be manually set. Give it a go if you like, then you can at least see the demo form, but don't forget to set the option back again.

 I figured out how to get your samples to work...I just had to go to the Infopath preview settings > security and trust: uncheck 'automatically determine security level' and check 'full trust'.  Now I can start understanding this thing, thanks again.

 Skarn, I have another question for you...

What is the trick to adding a main data connection to a form that I have made from importing a word document?  I see how to add a secondary data source, and I see that the main data source fields are all automatically detected/created...but to get those fields to link up to a main data source is what I am trying to figure out.  For example, in the sample you sent me, I can click on tools > data connections and view the main data source which you named "main connection."  Is it a different process when you import from a word/excel document?

I believe you must select what kind of a main dataconnection you have at the beginning of creating a template. If you start a template based on an XML file (or word document? never done that before) then the main data source/connection will be email/file based for submit rather than database.

I don't think you can 'convert' it to a database based form.

You may have to start a new template based on your database instead of the word document.

You may get some luck with the 'Tools > Convert Main Datasource' option but I don't know whether it will let you reconnect the form to a database.

What version of Infopath are you using, and can you use 'code' of any sort. It may be better option to use code manipulated Secondary data connections seeing as you have no intention for the form to be submitted anywhere.