Data Connections

Re: Secondary Connection for Drop-Down list

gnatzd — Sat, 29 Mar 2014 00:15:54 GMT

Ok, I got the drop down list to appear nicely, but I am stumped again. You said to add conditional formatting to the section to keep it from repeating with different values. I wasn't sure what to set in formating so I set do not allow users to insert or delete fields. This didn't work, obviously. Also. i have played with putting differnt query fields into that spot, but everything I have tried has grabed one value for the field when the form loads and this value never changes no matter what value you pull up in the drop down. I am attaching my latest effort. Thanks.

Dan

Re: Secondary Connection for Drop-Down list

gnatzd — Fri, 28 Mar 2014 18:42:50 GMT

Ok, sorry I misunderstood. I will rebuild the second form and add you other suggested changes and then I will let you know where I am. Thanks.

Dan

Re: Secondary Connection for Drop-Down list

Hilary Stoupa — Fri, 28 Mar 2014 18:26:48 GMT

I am still not being clear.

What I am trying to tell you is you cannot "add" a submit connection to your Access database. You need to use the database template when you create your form.

Re: Secondary Connection for Drop-Down list

gnatzd — Fri, 28 Mar 2014 18:06:26 GMT

Sorry, this is a work in progress and intially these two forms were two views of the same form each with its own submit botton. I put them into seperate forms when I started looking into getting each view to submit only its own fields to the database and realized that was a difficult problem becuase you can only have one submit connection to Access. I just haven't added the submit connection for the second form. There are still some fields I will add to the database to collect the information form the second form but some are there. So yes, it is intended to submit to the database. Thanks.

Dan

Re: Secondary Connection for Drop-Down list

Hilary Stoupa — Fri, 28 Mar 2014 17:51:55 GMT

So - my question is - how do you expect the 2nd form to submit to the DB? It is not based off the DB, so there is no option to submit it....Your first form is based off the database. So it does submit to the database. I mean, you can add code to submit to a DB.... but I'm guessing that isn't your intent.

Conditional formatting is part of InfoPath. Click on the repeating section in the form and add a rule that is a formatting rule.

DS is just shorthand for data source.

Re: Secondary Connection for Drop-Down list

gnatzd — Fri, 28 Mar 2014 17:17:13 GMT

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

Re: Secondary Connection for Drop-Down list

Hilary Stoupa — Fri, 28 Mar 2014 16:34:05 GMT

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.

Re: Secondary Connection for Drop-Down list

gnatzd — Fri, 28 Mar 2014 15:13:29 GMT

Hi Hillary,

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

Dan

Re: Secondary Connection for Drop-Down list

Hilary Stoupa — Fri, 28 Mar 2014 15:00:44 GMT

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!

Re: Secondary Connection for Drop-Down list

gnatzd — Thu, 27 Mar 2014 23:18:48 GMT

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.

Dan

Re: Secondary Connection for Drop-Down list

Hilary Stoupa — Thu, 18 Jun 2009 18:08:43 GMT

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.

Re: Secondary Connection for Drop-Down list

rdodson4 — Thu, 18 Jun 2009 17:00:05 GMT

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

Re: Secondary Connection for Drop-Down list

Hilary Stoupa — Wed, 17 Jun 2009 22:11:03 GMT

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.

Re: Secondary Connection for Drop-Down list

rdodson4 — Wed, 17 Jun 2009 16:08:44 GMT

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

Re: Secondary Connection for Drop-Down list

Hilary Stoupa — Wed, 17 Jun 2009 01:10:57 GMT

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?