Highlighted

Dynamic Drop Down Lists with MS Access Database

Jstaab

12-09-2011

Hello and thank you for looking at my problem.

I am trying to create dynamic or cascading drop down lists in Livecycle Designer ES2 v.9.  I need a total of three drop down lists.  The third DDL would provide three or four possible dates depending on the selections made in DDL1 and DDL2.  The list items in DDL1 and DDL2 do not change.  DDL1 is to identify the first letter of the person's last name (which is already stated in a previous DDL linked to an Access Database).  DDL2 is the event being requested.  The options in DDL1 and DDL2 do not change so they can be manually entered (unless someone has a better way to identify the first letter of the last name listed in the DDL at the top of the form).  But the list of possible dates in DDL3 is always changing.  Several computers would access the changing dates in DDL3 so if the dates could be updated in one place, this would be optimal.

I have been assuming that the best way to solve this problem is by linking DDL3 to an access database.  (If there is an easier way, please let me know.)  But I cannot get this to work.  I have read, and re-read and re-re-read Stephen Cameron's blog postings, but these examples are now five years and several versions old, and I cannot seem to make it work.  I cannot figure out whether to use the exit event, the change event or the pre-open event, and which DDL the code should be placed.  I have benefited from this forum more than you know, so I do not ask this question lightly.  I have spent several days (weeks?) trying to solve this, but I'm at wits-end, so it is time to ask. 

Any help would be greatly appreciated.

Replies

Highlighted

pguerett

13-09-2011

The technique that is used to communicate with the DB has not changed since the early releases of the product. So the information on Stefan's blog is still accurate. Before we delve into this be aware that you will have to Reader Extend your form to allow database connectivity for users that use Reader. If you are using Acrobat then this is not a requirement. Note that the right I am referring to is only available in the Reader Extensions LiveCycle server.

Let me know if you want to proceed.

Paul

Highlighted

Jstaab

13-09-2011

Thank you! Everyone working on this form has the standard version of Acrobat, so extending the form is not an issue. I have watched your video on connecting to a database several times, and it is what finally helped me accomplish this. But every attempt I make at these dynamic drop downs fails miserably. I would really appreciate your help.

P.S. I should have posted this on the Designer forum not the Forms forum, but I don't know how to change it.

Highlighted

pguerett

13-09-2011

I moved it for you ......

Ok that is good .....so you are able to populate the 1st DDList by binding to the database directly. Every subsequent DDlist that you want to populate will have to use script to connect and run a SQL command to extract the data that you want. Have you read through the Stefan Cameron blog on this topic yet?

Paul

Highlighted

Jstaab

13-09-2011

Yes (many times) I also found a pdf example called "populating a drop down list from database" which was very helpful. In an attempt to simplify (and learn the concept) I have tried setting up just two dropdown lists, with the second one being populated based on the selection in the first DDL. I am figuring out that DDL2 should not be linked in any way to the database when it is created. Then, in the exit event of DDL1, I have the following code (without the brackets):

var oDataConn = xfa.sourceSet.[secondDataConnection].clone(1);

oDataConn.nodes.item(1).query.select.nodes.item(0).value = "SELECT * FROM WHERE = " + this.rawValue;

oDataConn.open();

ddl2.clearItems();

ddl2.rawValue = "";

while(!oDataConn.isEOF()){

DDL2.addItem(xfa.record.[secondDataConnection].NAME.value, xfa.record.[secondDataConnection].ID.value);

oDataConn.next();

}

oDataConn.close();

when setting up the data connection for DDL2, I stayed BOF and EOF.

When I attempt to run this, I get an error message in the Javascript Debugger:

GeneralError: Operation failed.

XFAObject.open:3:XFA:form1[0]:#subform[0]:LastName[0]:exit

open operation failed. [ODBC Microsoft Access Driver] Syntax error in FROM clause.

Am I close?

Highlighted

pguerett

13-09-2011

The error is indicating that there is a problem in your SQL statement. I hope that you are replacing the words "table in second database connection" with the name of your specific table and the words "id column in second table" with the name of the column that you are trying to extract from. Also whenever you see [secondDataConnection] is replaced with the DataConnection name that you gave the 2nd dataConnection.

Paul

Highlighted

Jstaab

13-09-2011

You are correct. The actual SQL statement reads:

oDataConn.nodes.item(1).query.select.nodes.item(0).value = "SELECT * FROM CourtDate WHERE Ctrm_ID = " + this.rawValue;

Highlighted

Jstaab

13-09-2011

I GOT IT! Paul, you are amazing!!! Every time I get to the end of my rope and finally ask for help, I figure it out before I get the answer. Maybe I should start asking sooner.

My problem was in the second data connection. I was still linking the second data connection to a table.

I changed this to a SQL command " SELECT * FROM WHERE 0=1

Then it worked! Oh, I am sooooo excited. The things I can with this. Skippyidy Doo Dah.... Thank you a million times over.