[plug] OpenOffice.org data connection - Database for the rest of us

[J Michael Gilks]

On Wed, 9 Jul 2003 02:25, Craig Ringer wrote:
> > For those of you that missed out on the July PLUG Seminar tonight:
> > OpenOffice.org data connection - Database for the rest of us presented
> > by Mike Gilks, see:
> > http://www.westnet.com.au/mike.gilks/plug/index.html
>
> BTW, you don't need the macro in OO.o 1.1beta2, I just tested.
>
Woo hoo. My dreams have been answered

> I haven't figured out how to get a writeable subquery though, at least
> without using updateable views (these qualify as "the hard way" to do
> simple things).
>

If you create a child form that is a simple table view then you can do all the 
normal data manipulation, update, delete add etc.
The problem comes with a more complex view which involves lookups of different 
tables. ie the order details tables looks up an inventory table, but stores 
the ID only. To display this you need a multitable query and a modification 
would be messy since only part of the query is modified.
A solution I have thought of, but not tested yet would be multiple child 
forms.
Taking the classic case
Master form	-	Orders			contains orderID

Child of Orders	-	OrderDetails		contains orderDetailID
								link orderID to Orders
This one in a grid view to display all details for the current order.
Set to non editable.

Child of Orders	-	NewOrderDetails	link orderDetailsID to OrderDetails
Set this one editable with list boxes etc to handle referential integrity.
The NewOrderDetails  subform should now display the record currently displayed 
in the OrderDetails subform and be editable.
After adding or modifying a record click save and if need be refresh the 
document.

As I said, not tested yet, but seems to me it should work.
The subforms are quite flexible and may be linked to multiple forms if needed.

Hope this helps.

Love
Mike.