I have seen examples (and have them working) of the select * queries with Access to prepopulate a form.
My question is is it possible to use a data connection with an update or INSERT INTO query
I have seen an example on PDF Scripting with hidden fields and connecting to the Database, then update the hidden fields to update the database
I see how this would work, but means I need to have 2x the data fields in my form. To get away from this, I was hoping to have another option.
What I would like to do is have a separate data connection (different from the prepopulate connection that is bound to the Database) set up as a Delayed open Read/Write
Open the database, then execute JS code to update specific fields with
UPDATE TEST SET TEST.WriterName = "New Writer Name Test" WHERE ((TEST.ClientID.value)=1001 AND (TEST.YrRef.value)=2010);
var myNewName = "New Writer Name Test";
UPDATE TEST SET TEST.WriterName.value = myNewName WHERE ((TEST.ClientID.value)=1001 AND (TEST.YrRef.value)=2010);
Is this possible, does anyone have an example that I can be pointed to or provided?
The above would be for the update clause, then if say the year does not exist, I would use the Insert query to first add a new record, then update the fields with the update clause. So if it is possible, looking for the Insert Into option too.
Many Thanks in advance
Yes you can do that. You can configure the data connection to execute a SQL statement so that SQL statement can be anything you want. Stefan Cameran has an example where he is retrieing a specific record (after the user selects an item from a DDList to indicate which record to get). That technique uses a SQL statement so you could use that code and simply update the SQL statement to do what you want.
Here is a link to Stefan's blog entry:
Hope that helps
Unless I am missing something, your link provides a sample on how to use a select query
ie: SELECT id as catId, name AS catName FROM movie_categories GROUP BY name ORDER BY name;
I have the SELECT query working using the WHERE clause to get a spcific record. Works great.
I use this to pre-populate the form. Once I have the forms edited via data input from the users, they send the forms back to an administrator who has the ODBC connection and this admin person will update the database. I would prefer NOT to have invisible fields bound to the database, which is the sample in the PDFScripting website (A great source of info for users by the way).
what I am looking for is a sample on how to (if possible to do) use an Insert into (if need a new record) and the Update queries (to update existing records) with the specific data.
So my question is not how to use a SELECT query, but how to use the other 2 queries.
Right ....the sample shows how to add a field reference (invisible or not it does not matter) into a SQL statement. it is the technique that I was referring to not the exact statement. You woudl have to duplicate that technique when building your Insert statement. So something like:
INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)
where value1, value2 and value3 are replaced by field references.....like Field1.rawValue. The script can get quit complicated as you are building a string that contains the command but it is possible. I suggest building it up in a variable then writing that var to the screen (that way you can check if the SQL sytax is correct). Once you have the syntax right you can remove the writing to the screen.
Hope that helps
I have been trying to build one.
But if I do not know the exact syntax options, it becomes difficult 🙂
If I knew where to look for the documentation for this code, then the building of a sample becomes much easier.
Hope that helps