InfoPath Dev

I have a form connected to an access database. I have drop-down lists in the query portion and in the data entry portion of my form. I setup a secondary connection for receive data for the query portion and when I try to link the drop-down list I get the following error: "You selected the field where the control saves data. Instead, select the field where the control looks up data".

I thought that I could set up a secondary connection to receive data, link it to another access table where the AppsID and Initials is coming from in both the main connection and secondary connection. I then linked the drop-down list to the secondary connection in the query portion of my form and when I try to use the AppsID field from the secondary connection, I get the error. I even have the AppsID field in the main connection unchecked.

I know I am doing something wrong, but I sure don't know what. Please help me understand this error and how to correct it.

Hi --

So you have a drop down list control that is bound to a query field, and you get this error when you set the control to use that secondary data source for your list items? Actually, I can replicate this -- if I choose a data field from the Main data source as the values for my drop down list.... but if I choose my secondary data connection, I don't get any errors.

Open your Drop Down List Box Properties and make sure you've selected 'Look up values from an external data source'. Then be certain you've chosen the secondary data connection in the Data source drop down before you select the data field that should populate your drop down list.

Hillary,

I continue to have the same problem as before. I have checked my secondary connection and it is set to receive data. In the Drop-down list properties, I have selected my secondary connection in the Data Connection box. In the entries box, I have selected the top level of the connection which is referring to another table in the db. Actually I have exported that table from access as an xml file and use that file for the creation of the secondary connection. Hope that makes sense. In the value field, I have the ID field from that table. In the Display Name field, I have the field I want to display in the drop-down box. When I select that field, I get the error: "You selected the field where the control saves data. Instead, select the field where the control looks up the data."

I followed what you have written and everything looks correct. But, I still get this error.

Any Suggestions

Ralph

Can you attach your form template? When you reply, under the Options tab, you can attach a file....

Just to try to make sure I understand what you are saying, you have a secondary data connection (let's call it 2DS so we have a short name).

On your form is a drop down list box bound to a data field in your Main Data Connection.

For data connection for the drop down list entries, you've chosen 2DS. For the entries, you've selected a repeating group. Then you've selected two fields from that repeating group, one for the display and one for the value?

Hillary,

Thank you for responding. I have verything working the way I want it to on my form. I have several xml files that I exported from Access 2003 and am using them as secondary connections to several fields in the query section of my form. I had linked to the xml files but decided it probably would be better to have the information embedded into the form in case someone doesn't have their mapping to our network share done the same as other people in our division.

Anyway, everythign is working ok. I just have one more thing that I have been struggling with. I created a posted earlier in the month and got a response back from Patrick. I haven;t been able to completely understand his response.

http://www.infopathdev.com/forums/t/12022.aspx

Here's the scenario. I have a table in access with 265 records. One of the fields is called AssyNo. What I want to happen is, when a person clicked on the new record button, this field will be populated with the next number available. At that point, I need to do an autosave for the record is created and a second person can't get that same number. Hope that makes sense.

Do I need to setup another secondary connection for the AssyNo field?. If so, how do I get the information I want into that field?. I am about as new to this as anyone can be, so please forgive me if my question comes across as stupid. I am self taught in a lot of areas like Access, Sharepoint, etc.

Any help you can give me will be greatly appreciated. I have tried to search for blogs to help me but haven't found any yet.

Thank You

Ralph

Glad you've got this issue sorted. I've replied to your other post with a bit more information based on the question there. However, reading this post, which has a bit more information.... I have to wonder -- is your form built off the Access DB in question? That table has an ID field, correct? Does it auto increment? With a data base bound form, when you submit a new record, it gets its id. Does the AssyNo have to be populated before the form is first submitted?

Yes, the main connection is to the Access DB. I set that up when I created the form originally. The main connection refers to tblBoard_Information which AssyNo is one of the fields. The AssyID is set for auto-increment in access but the AssyNo is typed in when using Access. Thatis why I want this number to automatically populate this field when the new record button is pressed. When I do a submit to the DB, the AssyID is automatically added to the DB, but unless the AssyNo field is typed in, that entry is not completed. Hope this answers that question.

You wrote: Q: Does the AssyNo have to be populated before the form is first submitted?

I'm not sure how to answer this. My theory was that if the AssyNo populated the field when the new record button is pressed and there is an autosave after that, then another person could not get the same number from the database and have a conflict. Otherwise, if you want until the end of the submit, someone else in the meantime may get that same number. Do that make sense.

Looking forward to hearing from you. FYI, when the form linked to this table is opened from Access (Access form), the AssyNo has to be typed in manually before the record is saved, however, there are fewer people access the form this way, than will be accessing using the InforPath form. Hope this clarifies that matter.

In Access, I have a search query set up that shows all of the existing boards, so it is easy to see what number is next. In InfoPath, this isn't the same situation.

If you have a better idea, I am VERY open to suggestions. This is my first form linked to an Access DB so I am struggling with the different concepts compared to Access 2003.

Thank You

Ralph 

I'm not super familiar with Access, but can you also set AssyNo to auto increment, maybe make it a calculated column?

When I asked if it had to be populated, I guess I'm wondering about the use of the field. You know, sometimes we need to know something before we submit a form, because we are also typing a description that is supposed to have the AssyNo in it, or we have some other process that we need it for.

The 'New Record' button is its own special beast, so you'll not be able to add additional rules to it. It is also not an action available to other rule sets. That is part of what makes this challenging.

You could add a button called 'Get AssyNo', I suppose, and then after they click New Record, they could click 'Get AssyNo' to get the newest number, using the same methods I outlined in your other thread on this -- run a query that gets the max AssyNo, populate the field with that + 1, and submit the form so that the new AssyNo is used.

Hillary,

I liked your suggestion about adding the button to get the next available number from the database. The field in Access uses the data type of Number - Long Integer. So, I created my 'Get AssyNo'' button and added a rule that says "max(@AssyNo)+1". When I click the button, the word "NaN" appears in that field.

I don't know where that is coming from since the AssyNo field is tied directly to the backend of the db. You mentioned something about running a query that gets the max AssyNo to populate the field.

How do I do that? I'm don't understand how that is supposed to happen. Do I set up another secondary connection. If so, how do I link to the connection. On my form, I have a text box to display the information linked to the main connection. Do I need to change to a different type of control?

You have been a great help. I hope that I am not asking too much.

Thank you for any help you can provide.

Ralph

You need to add a secondary data connection to your form. That connection needs to receive data.

Do you know how to create a View in Access? You should create a View that uses the following query to always return the max assy number (this is written for SQL, so you may need to modify syntax for Access):

Select max(AssyNo) from TableName

Your secondary data connection will connect to that view. You do not need to set it to automatically retrieve data on open.

The first rule on the button should have an action to run a query. Query the data connection you just set up to the view. 

The second rule on the button should use the action to set a field's value. The field should be the AssyNo field in the data fields of the main data source of your form.

The value should be the AssyNo field from the brand new data connection we just created plus 1.

The third rule on your button should submit the form, so that the brand new assy no gets submitted to the database, so the numbers stay up to date.

 Hi Hillary,

I have a similar problem but I have not been able to a solve it after reading this post.  I have an Access field (a ID number that users will look for) that I am trying to pull into my form to use as a drop down list.  Once this field has data selected I would like to automatically populate other fields from my database.   I solved the problem with the error coming that you discussed in this post early on.  I was able to get the selection list to show up when I checked "retrieve data when form is open", but this also created multiple entries in a repeating table that gave the date of entry so I turned it off.  Now, I don't see anything in my drop down box although it is apparently connected to my secondary data source.  Your help would be greatly appreciated.  Thanks.

Hey, Dan - any chance you could attach your form under the Options tab so I can get a better idea of what your issue is?

Thanks!

 Hi Hillary,

Attaching my form as you have requested.  Thanks,\.

Dan

Okay - so, first off, you will have to query the data source in order to have options in the drop down. Querying loads the data in the data source.

It looks like you have 2 data connections to the same data source. You probably don't need that, since you can't filter your query prior to running it - that is, every time you query your Access DB, all items will be returned.

So - I'm trying to understand what you are wanting to do in your form. It appears you have the secondary data source fields on the form in a number of places - are you just wanting to display data from your DB? If not, if you actually are wanting to save data the user enters in those controls, you would need to use main data source fields for controls in the form, or you could create your form off the database so it will query from and submit back to the DB.

If you are just trying to display, you can prevent displaying those multiple values by adding conditional formatting to the repeating sections where PreauthorizationID in the 2ds = PreauthorizationID in the main DS.

Thanks Hillary,

I will try to explain what I want my for to do.  The database is comnected to two forms. The first form collects information from Prosthtics Service with information about an upcoming surgery.  This information will include information about a needed implant, a vendor, an estimate of what the implant will cost, and estimated date the item will be needed and a date of input.  That form is a Preauthorization request and the information is sent to an Access database that assigns a random unique PreAuthorizationID.  The second form,  the one I sent you, is for Contracting to fill out and sign to authorize the purchase. In this form, the user will select the PreAuthorizationID from the database and, after section other fields from the first form will autopopulate on the form. 

The form will collect and send back to the database all the fields that the Contracting Officer fills in so the datbase is contantly updated when requests are entered and when the requests are authorized.

The folowing items are enhancements I intend to add, but I wanted to get the drop down list and autopopulate working first:

Not to exceed amount is coming from the database and is input by Prosthetics.  In the final version of the form I will make this field Requested Not to Exceed amount.  Contracting will either be able to accept this amount ( by a checkbox) in which case the final not to exceed amount will be autopopulated below it, or change the amount (another check box) which allows the field to be filled in with an amount the Contracting Officer determines. I will also do this with other fields such as need date.  If this enhancement isn't possible in InfoPath please let me know.

I am attaching my other form.

By the way.  Is the conditional formatting you are referring to something I can do in InfoPath? Where is that control? What does DS refer to?  Sorrry for being such a newbie.

Thanks for your help. Please let me know if you need any additional information. 

Dan