10 Replies Latest reply on Oct 26, 2011 10:33 AM by irishfish72

    Drop-down list to pull in Access record

    irishfish72

      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

        • 1. Re: Drop-down list to pull in Access record
          pguerett Level 6

          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

          • 2. Re: Drop-down list to pull in Access record
            irishfish72 Level 1

            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

            • 3. Re: Drop-down list to pull in Access record
              pguerett Level 6

              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

              • 4. Re: Drop-down list to pull in Access record
                irishfish72 Level 1

                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

                • 5. Re: Drop-down list to pull in Access record
                  pguerett Level 6

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

                   

                  Paul

                  • 7. Re: Drop-down list to pull in Access record
                    pguerett Level 6

                    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

                    • 8. Re: Drop-down list to pull in Access record
                      irishfish72 Level 1

                      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

                      • 9. Re: Drop-down list to pull in Access record
                        pguerett Level 6

                        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

                        • 10. Re: Drop-down list to pull in Access record
                          irishfish72 Level 1

                          Paul,

                          Files have been sent. Happy Hunting.

                          Misty