Drop-down list to pull in Access record

[Former Community Member]

Hi -

I am a newby at this so please bear with me.  I know this have been addressed a hundred times but I can't seem to find anyone else who is having this specific issue.

I have v8.2.

Since my scripting wasn't working, I downloaded the "Adobe LiveCycle Designer 7.0, providing interactive database lookup from forms" document to try and see if I can figure out what is going on.

I built exactly as in document with exception of adding "clone(1)" as needed based on what I found in other postings about v8.2 and premission errors.

Dropdown box:

/* This listbox object will populate two columns with data from a data connection.

sDataConnectionName

- name of the data connection to get the data from.

- Note the data connection will appear in the Data View.

sColHiddenValue

- this is the hidden value column of the listbox.

- Specify the table column name used for populating.

sColDisplayText

- this is the display text column of the listbox.

- Specify the table column name used for populating.

These variables must be assigned for this script to run correctly.

*/

var sDataConnectionName = “DataConnection”;

var sColHiddenValue = “ID”;

var sColDisplayText = “PART_NO”;

// Search for sourceSet node which matchs the DataConnection name

var nIndex = 0;

while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName)

{

nIndex++;

}

var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1);

oDB.open();

oDB.first();

// Search node with the class name “command”

nIndex = 0;

while(oDB.nodes.item(nIndex).className != “command”)

{

nIndex++;

}

// Need to set BOF and EOF to stay

oDB.nodes.item(nIndex).query.recordSet.setAttribute(“stayBOF”, “bofAction”);

oDB.nodes.item(nIndex).query.recordSet.setAttribute(“stayEOF”, “eofAction”);

// Search for the record node with the matching Data Connection name

nIndex = 0;

while(xfa.record.nodes.item(nIndex).name != sDataConnectionName)

{

nIndex++;

}

var oRecord = xfa.record.nodes.item(nIndex);

// Find the value node

var oValueNode = null;

var oTextNode = null;

for(var nColIndex = 0; nColIndex < oRecord.nodes.length; nColIndex++)

{

if(oRecord.nodes.item(nColIndex).name == sColHiddenValue)

{

o

ValueNode = oRecord.nodes.item(nColIndex);

}

else if(oRecord.nodes.item(nColIndex).name == sColDisplayText)

{

o

TextNode = oRecord.nodes.item(nColIndex);

}

}

while(!oDB.isEOF())

{

this.addItem(oValueNode.value, oValueNode.value);

//IDList.addItem(oValueNode.value, oTextNode.value);

oDB.next();

}

// Close connection

oDB.close();

Button:

if (Len(Ltrim(Rtrim(SelectField.rawValue))) > 0) then

$sourceSet.DataConnection.clone(1).#command.query.commandType = “text”

$sourceSet.DataConnection.clone(1).#command.query.select.nodes.item(0).value = Concat(“Select * from OfficeSupplies Where ID = “, Ltrim(Rtrim(SelectField.rawValue)) ,””)

//Reopen the Dataconnection

$sourceSet.DataConnection.clone(1).open()

endif

Preview PDF and it connects correct, drop down box populates correctly, but when I click the button, it ALWAYS populates data from first record (no matter what is selected in drop down box).  This is the same issue I had with my own build.

Anyone have any ideas what I have wrong?

Thanks!

Misty

[Former Community Member]

To do this you will need two DataConnections .....I see that you are only using one. The 1st one is used to connect to and bind the Dropdown list. Whenit is configured it is setup to connect to the DB when the form is opened (delayed open checkbox is off by default). The second connection is the one you reference in the button and it shoudl be setup with Delayed open on  (delayed open is on the last panel of the setup wizard for the connection). This mens that the connection to the db will not happen until you tell it :

$sourceSet.DataConnection.clone(1).open()

Hope that helps

Paul

[Former Community Member]

Hi Paul,

Wow, I am so not getting this.

Updated script on button to following:

if (HasValue(SelectField)) then

$sourceSet.DataConnection2.clone(1).#command.query.commandType = "text"

var sqlString = Concat("Select * FROM OfficeSupplies Where ID = '", Ltrim(Rtrim(SelectField.rawValue)) ,"'")

$sourceSet.DataConnection2.clone(1).#command.query.select = sqlString

$sourceSet.DataConnection2.clone(1).open()

endif

Built second connection with "delayed open" checked and still not getting anything but first record.  If not value in drop down, it does not return record as expected so it is looking for result, just not grabbing correct one.

Suggestions or correction very welcome.

Misty

[Former Community Member]

Are you getting any errors on the java console?

Put a message box out that shows the sqlString and ensure that your sql command is correct.

Add a line after the var sqlString line like this:

xfa.host.messageBox(sqlString)

Paul

[Former Community Member]

I have switched back to using this in my personal file and not test file so button script is as follows with your edit:

if (HasValue(SelectID)) then

$sourceSet.DataConnection2.clone(1).#command.query.commandType = "text"

var sqlString = Concat("SELECT * FROM Medication WHERE MRN = '", Ltrim(Rtrim(SelectID.rawValue)) ,"'")

xfa.host.messageBox(sqlString)

$sourceSet.DataConnection2.clone(1).#command.query.select = sqlString

$sourceSet.DataConnection2.clone(1).open()

endif

And got this...

Warning: JavaScript Window -

SELECT * FROM Medication WHERE MRN = '9999'

Misty

[Former Community Member]

So I assume that there is a corresponding record with an MRN of 9999 right?

Paul

[Former Community Member]

Correct.

[Former Community Member · Accepted solution]

The other thing I just noticed is that I generally clone the whole connection and not individual nodes in the connection like you have. Try changing your code to this:

var nIndex = 0;
while(xfa.sourceSet.nodes.item(nIndex).name != "DataConnection2") do

nIndex = nIndex + 1
endwhile

var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1); // the node pertaining to the data connection specified
//set up sql call to DB to get specifics about MRN

if (HasValue(SelectID)) then

oDB.nodes.item(1).query.setAttribute("text", "commandType");

var sqlString = Concat("SELECT * FROM Medication WHERE MRN = '", Ltrim(Rtrim(SelectID.rawValue)) ,"'")

oDB.nodes.item(1).query.select.nodes.item(0).value = sqlString;

//now connect to DB and get a record
oDB.open()
oDB.close();

endif

Also if you copy and paste the code the quotes always get messed up ...you will have to type those in.

Paul

[Former Community Member]

Paul,

Thanks for being so patient.  I know you guys hate us newbies. 

This is in FormCalc, not JavaScript, correct?  I had to change != to <> or got syntax error.

After that, got the followign error message:

Accessor oDB.nodes.item("1").query.setAttribute("text","commandType") is unknown

Button know looks like this:

var nIndex = 0

while(xfa.sourceSet.nodes.item(nIndex).name <> "DataConnection2") do

nIndex = nIndex + 1

endwhile

var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1)

if (HasValue(SelectID)) then

oDB.nodes.item(1).query.setAttribute("text","commandType")

var sqlString = Concat("SELECT * FROM Medication WHERE MRN = ' ", Ltrim(Rtrim(SelectID.rawValue)) ," ' ")

oDB.nodes.item(1).query.select.nodes.item(0).value = sqlString

oDB.open()

oDB.close()

endif

Misty

[Former Community Member]

Can you send me the form and the access DB and I will try it here . You will have to rename the mdb file to another extension to make it through the mail filters. Send the files to LiveCycle8@gmail.com<mailto:LiveCycle8@gmail.com>

Paul

[Former Community Member]

Paul,

Files have been sent. Happy Hunting.

Misty