Dynamic Drop Down Menu from an Access Database

[Former Community Member]

Hello Everyone,

I am user Adobe Designer 7 to create a fillable PDF, and I would like to get the options for a drop down menu from my MS Access database. Basically I would like to populate the drop down menu with the names in the Access database. There i Have a table called PhoneNumbers and it contains the names of all the people I want to appear in the drop down menu that I just created.



This is what I did:



I created a drop down menu and then I clicked on Object > Binding > under "Default Binding (Open, Save, Submit)" choose "New Data Connection"

then connect to the database using a fileDSN. I opened the table where with the "names" column (PhoneNumbers)

and under the "Query," option, I wrote:



select * from PhoneNumbers



Then it gave me all the fields from the table and I dragged the "Last name" field over the drop down menu. I will need to find a way to get the "First Name" field to join the Last Name field to create one name that looks like this: Hill, Angie



The problem is that even though it shows the name from the Access database, it does not give me a list of all the names when I open the form in Reader 7...



No matter how much I click that drop down arrow, the name does not change...

It's almost as if it gets to the databse and gets the data from the database, but it cannot loop through it...

It's almost as if it's missing the code to loop through it...



Why is this? Any ideas how to fix it so it gives me a list of ALL the names, when I click on the drop down button?



After that, how can I add the first name to it?



The form would look like this:



Angie H. 202 641 2055



Right now it does look like that, but I cannot change to another name when I click the drop down menu. For the code to be working when I change to another name, the phone number also changes to the phone number of that person:



Barry S. 703 555 1258



The name is in a drop down menu, the phone number is in a textbox.



Can you help me with this?

[Former Community Member]

ANGELA,<br /><br />Well, the good new is that you are not far off...What you need is to insert the following code using Java Script under the Initialize function. Just replace the Connection name, The Hidden Value, and the Display Text with your information. This is a function is 8.0 but will work with 7.0<br /><br />/* This dropdown list object will populate two columns with data from a data connection.<br /><br /> sDataConnectionName - name of the data connection to get the data from. Note the data connection will appear in the Data View.<br /> sColHiddenValue - this is the hidden value column of the dropdown. Specify the table column name used for populating.<br /> sColDisplayText - this is the display text column of the dropdown. Specify the table column name used for populating.<br /><br /> These variables must be assigned for this script to run correctly. Replace <value> with the correct value.<br />*/ <br /><br />var sDataConnectionName = "DataConnection"; // example - var sDataConnectionName = "MyDataConnection";<br />var sColHiddenValue = "CAT_Corp_SeqNo"; // example - var sColHiddenValue = "MyIndexValue";<br />var sColDisplayText = "CAT_Corporate"; // example - var sColDisplayText = "MyDescription"<br /><br />// Search for sourceSet node which matchs the DataConnection name<br />var nIndex = 0;<br />while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName)<br /> {<br /> nIndex++;<br /> }<br /><br />var oDB = xfa.sourceSet.nodes.item(nIndex);<br />oDB.open();<br />oDB.first();<br /><br />// Search node with the class name "command"<br />var nDBIndex = 0;<br />while(oDB.nodes.item(nDBIndex).className != "command")<br /> {<br /> nDBIndex++;<br /> }<br /><br />// Backup the original settings before assigning BOF and EOF to stay<br />var sBOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("bofAction");<br />var sEOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("eofAction");<br /><br />oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayBOF", "bofAction");<br />oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayEOF", "eofAction");<br /><br />// Clear the list<br />this.clearItems();<br /><br />// Search for the record node with the matching Data Connection name<br />nIndex = 0;<br />while(xfa.record.nodes.item(nIndex).name != sDataConnectionName)<br /> {<br /> nIndex++;<br /> }<br />var oRecord = xfa.record.nodes.item(nIndex);<br /><br />// Find the value node<br />var oValueNode = null;<br />var oTextNode = null;<br />for(var nColIndex = 0; nColIndex < oRecord.nodes.length; nColIndex++)<br /> {<br /> if(oRecord.nodes.item(nColIndex).name == sColHiddenValue)<br /> {<br /> oValueNode = oRecord.nodes.item(nColIndex);<br /> }<br /> else if(oRecord.nodes.item(nColIndex).name == sColDisplayText)<br /> {<br /> oTextNode = oRecord.nodes.item(nColIndex);<br /> }<br />}<br /><br />while(!oDB.isEOF())<br />{<br /> this.addItem(oTextNode.value, oValueNode.value);<br /> oDB.next();<br />}<br /><br />// Restore the original settings<br />oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sBOFBackup, "bofAction");<br />oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sEOFBackup, "eofAction");<br /><br />// Close connection<br />oDB.close();

[Former Community Member]

Under the Initialize function of the drop down menu?



My drop down menu is called "Person"

And from the Person drop down menu, I want the deparment to appear in the textbox next to it:



So should this code be:

var sColHiddenValue = "Person";

var sColDisplayText = "Department"



If not, where do you find these values?

Thank you for you help!

Angie H.

[Former Community Member]

Angie,



You are almost there, But I think you are trying to do two things at once. Lets get the drop down going first then the text box will be a breeze. I believe that you are using your dropdown as the driver for the rest of the data you will see. You do not need to identify the field of the drop down because it is a event with, so the code will launch when you initialize it. The first thing is to identify you dataconnection in the sDataConnectionName, as for your second value I always auto number any table that has important information, (like a sequence number in access), use this sequence number for your hidden value. then use the Person to populate the list box. This should give you the drop down you are looking for.



Then use your dropdown list name and rawValue to drive data into your other fields.

like person.rawValue, this should equal the sequence number from your database.

[Former Community Member]

Hi,



Did you ever get a solution to this? I want to do exactly the same thing using Designer 7 (I am not able to install 8).



Cheers

[Former Community Member]

This situation can be solved in multiple ways, What is your particular situation? do you have a code sample

[Former Community Member]

I figured it out, but thought I would post an example of what I'm doing in case it helps someone else.

This goes into a script object and is called elsewhere in the form.



userID = userid;

passwd = password;



dBName = databasenamehere;

SQLStatement = something like Select * from TableName where Field = 'value';



function connectDB(dBName, SQLStatement)

{

try

{

var dbConn = ADBC.newConnection(dBName, userID, passwd);

var stmtObj = dbConn.newStatement();

var rowObj = null;

if(dbConn == null)

throw "Error connection to " + dBName;

// Execute the SQL statement

stmtObj.execute(SQLStatement);

if (stmtObj == null)

throw "No records retrieved from " + dBName;

else

{

//begin loop populated drop-down list

// while there is still a value

while(stmtObj != null){

//start with a next for because the first one is always blank

stmtObj.nextRow();

// Get current row object

rowObj = stmtObj.getRow();

form1.Page1.dropdownnamehere.addItem(rowObj.FieldName.value);

}

}

}

catch(exc)

{

console.println(exc);

return null;

}

}

[Former Community Member]

I am trying to incporate that same type of drop down menu on a form. I added the code above (adjusting some to reflect my DB) but I keep getting errors. The first error is a syntax error about ; - I am not familiar with Javascript but do understand the ;



I checked the line # and there is ; so I am not sure where to go from here.



I have been able to create the link and see the first option but can't get it to loop like in the code above. Help!



Also, is there any way to create the link to a public database or a public folder on the network?

[Former Community Member]

Hi. I would need to have a look at your script. Also it may be easier for you to use FormCalc if you are not used to Javascript as I have found some awesome articles on all of this, but it uses FormCalc. I don't know who the guy is that writes them, but he's a total life saver.



Start here and it should get you where you need to go.



http://blogs.adobe.com/formbuilder/2006/09/connecting_a_form_to_a_database.html

[Former Community Member]

Thanks! When I try the suggestions from the link you provided, I still only see the first item in the drop down menu. I checked to see if the code was placed under initalize and nothing was there. I checked my binding then the code and still nothing. I even switched my binding to reflect normal and checked the code and still nothing.



My only reason to connect my form to a database is for one field. I have a drop down menu on my form which I would like to link with all of our contacts. Our contacts are in a public folder in Outlook and in an Access Database. I do not need the entire record from the database just the contact. Is it possible to link the field on the form to Outlook instead of the Access Database?

[Former Community Member]

Hi. I would really need to see any script you're using and your version of Acrobat.



If you post it here I'm happy to have a look.

[Former Community Member]

Hi,

I have used the above Javascript to populate my drop down list.

How do I use my dropdown list name and rawValue to drive data into other fields. Like person.rawValue?

[Former Community Member]

Asda,



Are you looking to populate text fields based on the data you would select from the Drop-down list?



For example, if I select John Doe from the Drop-down...the text field "Phone Number" would autofill with the corresponding data from a database.



What version of Designer are you using?

[Former Community Member]

Jeff,



Here is what I am trying to achieve. My drop down list is dynamically populated with CompanyName from an access database. This bit is fine and works. But when I select a specific company from the drop down I need it to fill in the address and zip from the same access database table for that company into a text box. I am struggling on this bit. I am using LiveCycle Designer 8.0.



Thanks for your help

[KaarthikRam]

Hi,

Has anyone got an answer to this situation? Im in the same position and trying to auto-populate couple of textfields once a value is selected from a dropdown. All these values are in an Access 2003 database. Curently using Livecycle Designer v8.

Cant seem to find a post in these forums which gives out the solution..

Please help !

Regards,