Look-up to DB after entering text in field

[PaintHorseLover]

I've been browsing the forum for quite a while now and cannot find the answer to my problem.

We've been using LC for months now but we now need to figure out how to have a look-up to a dabatase after the user has entered text in a field.  Based on the text entered, a list will appear which matches the text entered by the user so that the user can choose the appropriate record which will then populate numerous other fields on the form.

Usually, the field the user needs to enter text in is the 'Last Name' field and let's say I enter 'Hill' the look-up would return all last names in the database that contain 'Hill'.  The list that appears should also contain numerous other columns of data in addition to the last name (such as first name, middle initial, etc.) in order to ensure the user is choosing the correct person.  Once the user chooses the correct person, numerous other fields on the form that are 'read only' will then populate automatically.

In the software we've been using for years we achieve this using an SQL statement, however, have tried to use it in LC and it does not work.  I've been able to get the connection made and to have various fields populate with either the first record in the database or a specific record based on my SQL statement, however, I need to know how to not have the database connection execute immediately, but rather wait for the user to enter text in the field and then proceed to filter the records in the database and display all the matching options.  If I check any of the 'delay' options it just leaves the fields blank.

Is there a way in LC to make it function the way I need it to?  I'm new to scripting and SQL, so please be very specific.

Thanks for any help !

Lena

[Former Community Member]

This can only be done through coding .....please see this blog entry for a description and a sample of the code.

http://forms.stefcameron.com/2006/09/29/selecting-specific-database-records/

Paul

[PaintHorseLover]

Thanks Paul, but we've already looked at that and have tried to download the sample and get it working without any success....it's as if there's some information missing that's just not evident to us.

Thanks anyways...

Lena

[Former Community Member]

Are you getting errors when the script runs? You can hit Ctrl-J in Acrobat and the Javascript console will pop up ...that is where scripting errors will appear.

Paul

[PaintHorseLover]

Hi Paul,

Under the sample zip file it gives the following instruction "Use the FormBuilderDB20060929.sql file to build the database, create an ODBC Connection named “FormBuilderDB” and load the form".  I've been able to go in under Administration Tools to create a database named FormBuilderDB but which driver should be used?  When I'm in LC under the database connection, it doesn't seem to like the driver I've chosen (Microsoft ODBC for Oracle)...should it be another one???  It also wants a User Name and Server.....what should go in these???  Where should the FormBuilderDB20060929.sql file go???

Also, this sample uses a drop-down field initially which is not what I need....the user should be typing their last name in a text field and then when they tab out that's when the link to the DB should be activated and all records that match what has been typed should appear.....is that functionality not possible or is it just different coding required?  Perhaps I'm in way over my head on this.

Thanks for any help.

Lena

[Former Community Member]

the sql file contains commands that will build the DB for your particular databse. It is like a bat file for windows. So if you are using Access then it will build an Access table. Once you decide which database vendor to use that will indicate which ODBC driver to use.

The concept of entering in information into a field (whether it is a field or a dropdown) results in the same script being executed.

Paul

[PaintHorseLover]

Hi again,

We're working with Access 2000, so if I create a new DB in Access and name it FormBuilderDB should I then be able to just load the .sql file and it will be built from that?   If that's the case, how do I load it? I've looked at the import options and it doesn't allow you to import a .sql type file.

More step-by-step instructions are obviously needed as I'm not getting it.

Thanks,

Lena

[Former Community Member]

I am not an Access expert so I do not know how to execute those SQL commands in that file (this is an Access thing not a Designer issue). Most other DBs have a console where I can point to the sql file and have ot execute the commands. Perhaps another forum user has experience in this area.

Paul

[PaintHorseLover]

Hi everyone,

If anyone can help me figure this out, it would be much appreciated.

Thanks,

Lena