InfoPath Dev

Please help me.

I have a database in SQL Server 2008 R2, called LU_Employee. It has a primary key column (int), a column for name (varchar) and a column for surname (varchar).

I want to be able to have a combo box in an InfoPath form, where the employee surnames in the LU_Employee table is the selection.

Could someone please, in detail, explain to me how to do this, and my combo box apprears empty.

Is there are step-by-step guide online that could assist me in doing this? I would also appreciate any suggested book I can buy that could help with InfoPath with SQL and Access.

Regards

Christiaan

Hi - welcome to the forum.

I think you are saying you'd like to use a table from your SQL database as a data source for your combo box.

Here's a link to an Office Online article about this: http://office.microsoft.com/en-us/infopath-help/use-values-from-a-sql-server-database-to-populate-a-list-box-drop-down-list-box-or-combo-box-HP010093650.aspx

Now, it refers to InfoPath 2007, but if you are using IP 2010, you add data connections from the Data tab in the ribbon - otherwise, it should be pretty much the same.

However, you mention "my combo box appears empty" which would lead me to think you've already tried this and are having trouble? If that's the case, if you can attach your form (or a sample form where you've tried to set this up) under the Options tab in a reply, I'd be happy to take a look at it, see if I can see what's awry.

I really think the InfoPath help and how page on Office Online is a good place to get started: http://office.microsoft.com/en-us/infopath-help/CL010072926.aspx?CTT=97 

That's for Office 2007 - so again, if you are using 2010 you may have to do some hunting to find the right options, but it should help you get the fundamentals.

Hi Hilary

Thank you so much for your swift reply.

I looked at your link, but because you suggested I must attach my form, I just recreated my small database table in Access. I looked at this article:
http://office.microsoft.com/en-us/infopath-help/use-values-from-an-access-database-to-populate-a-list-box-drop-down-list-box-or-combo-box-HP010096933.aspx?CTT=1

I'm sure my issue is more an InfoPath (I don't know what I'm doing) one, than a database one.

I use InfoPath 2007.

1. I created a new design template, selected database, and followed the article, and set up a main submit connection.
2. I set up a secondary data connection (as instructed in the article).
3. When I drag and drop a combo box onto my form, I get the following error:
   You have chosen to bind a non repeating control to a repeating field or group. This control will be bound to the first item in the repeating field or group. Do you want to continue? I selected Yes.
4. When I double click the combo box, unde the data tab, I select Look up values from an external data source, I select Data source: employeeDataConnection (created in step 2), and under Entries, I click to my EMPLOYEE_NAME column in the "decision tree" under dataFields, LU_EMPLOYEE, and get the error You selected the field where the control saves data. Instead, select the field where the control looks up data. But there is nothing else to select.

I have attached my Access database and my Test form.

 Christiaan

Wow, so this is kind of fascinating.

First as to item 3 - the data fields are always going to be in a repeating group. You'll want to drag the /dfs:myFields/dfs:dataFields/d:LU_EMPLOYEE group from the Main data source onto your form. You can set the repeating group properties so that the user can't insert more instances of the repeating group. This data structure is the IP structures a data base bound form, which is what you are creating. Since the table the form is bound to has more than one row in it, IP makes the data fields group repeating.

However, what is really interesting is item 4. You have a second data connection to the very same table, and it seems that IP is very confused by that!

Here is the XPath to your employee name field in your main data source: /dfs:myFields/dfs:dataFields/d:LU_EMPLOYEE/@EMPLOYEE_NAME

Here is the XPath the your employee name field in your secondary data source: /dfs:myFields/dfs:dataFields/d:LU_EMPLOYEE/@EMPLOYEE_NAME

Notice anything? They are the same.... So, my guess would be that InfoPath is having some kind of a fit trying to validate this. I think if what you really want to do is have your list of employees be from the same table where you are submitting, you may need to create a view (in SQL - not sure what the equivalent is in Access - a Query?) and then use that for your secondary data source. I'd give my view a distinctly different name from LU_EMPLOYEE, and I'd also alias my columns in the view (EMPLOYEE_NAME as EMPNAME or something). That way you can be sure that your XPath will be different.

All that being said, it could be you are using the same table for both just as a simple test, and for your actual form you intend to use different tables. If that is the case, I'd suggest setting up your test to more accurately reflect your intended final architecture.

Hi Hilary

Thanks for your help. I could not have done it without you. I created the view like you suggested, and that seemed to be the determining factor.

I did get an error when I tried to drag the combo box onto my form using the /dfs:myFields/dfs:dataFields/d:LU_EMPLOYEE group, but when I used the -/d:LU_EMPLOYEE/EMPLOYEE_NAME and -_SURNAME respectively, and clicked on "Yes" at the prompt, it seemed to work.

I owe you a drink if you ever come to South Africa.

Now on to getting the form to submit...

My concern with this:

Christiaan:

I did get an error when I tried to drag the combo box onto my form using the /dfs:myFields/dfs:dataFields/d:LU_EMPLOYEE group, but when I used the -/d:LU_EMPLOYEE/EMPLOYEE_NAME and -_SURNAME respectively, and clicked on "Yes" at the prompt, it seemed to work.

Is that the prompt you are getting means that if your users can query the data source and the query returns more than one result, the controls are only bound to the first instance of the repeating LU_Employee group.

What is the error you get when you drag the group onto the form? Were you putting it in another section?

Hi Hilary

The prompt I clicked yes to is as in point no 3 a few posts back.

The error I got from the group, is attached.

I put the submit button on my form, and though it says that the form was submitted, the new records do not show in my SQL database in the LU_EMPLOYEE table. So I am pretty disgruntled with InfoPath at the moment.

I spent the day trying to get hold of an InfoPath consultant or trainer in South Africa, and even drove to Microsoft's offices, without any success.

You do not maybe have an office or somene who knows InfoPath in South Africa? Or can your company offer some Skype help with my issue? I need about 30 minutes of someone's time to just set up the reading and writing to SQL.

Hi Christian,

We do offer InfoPath Support Help via Skype and/or Livemeeting. Go to Qdabra.com and click on the Support link on the home page. Buy one support pack and we'll contact you.

Thanks for your interest!

Thanks Ernesto

I bought a support pack. My code from the site is: [Edit - removed].

My Skype name is: [Edit - removed]

I look forward to hearing from someone in your company. Don't phone me, it will be too expensive. Just add me on Skype.

Christiaan

Thanks James, Ernesto and Hilary for the awesome service I received from your company.

James was helpful and friendly and patient with me, and very experienced.