Building a user-friendly Excel Oracle front-end Quote:I have some familiarity with Oracle and SQL, but only little familiarity with Excel and none with VB/VBA. I have downloaded your software to trial with a view to building a user-friendly Excel front-end to a bulk update/insert in Oracle.
What I am looking for is the ability to return a result set into a combo box, allow a selection from that list to be used as input parameter in the next SQL to populate another combo box, select data from that result set to be used in validation (via more SQL to populate work tables) of a separate list of items entered on the form by the user. Finally, display the results of the validation in a scrollable list and allow the user to invoke a PL/SQL script to perform the bulk process (using the work tables).
Is this the kind of thing that SQL*XL can be used for? The application will be built from scratch - there is no existing Excel spreadsheet.
I would appreciate your advice.
You can show data in dropdown lists. Go to View-Toolbars and enable the Forms toolbar. You can draw a dropdown list onto your spreadsheet. Right click the control and select "Format control". You can specify a range of cells where the input should be taken. E.g. set that to X2:X100. You now need to put the reference values there. You can run a query to populate the range: select CategoryId from categories to X1; You can automate this using the sqlqueryvalue function (use the Array version to be able to display more than one row). You can also automate it by running a macro running the query.
A combobox can be programmed to assigning a macro that fires when a value in the combo changes. You can execute your detail query from there.
Further reading: Excel DB front-end with listboxes and comboboxes
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1232962909
Pages: 1