Page 1 of 1

Modify data source in a form

PostPosted: Thu Dec 07, 2017 6:30 pm
by salrandazzo
Hi folks.
I'm trying to understand how basic works with API and UNO.
Found some documentation about basic, but I really get lost with API and UNO.
My purpose is this one, so simple:
MySQL database with just one table: tbCustomers, with name, phone and address.
Then a OpenOffice base application connected with the MySQL database.
But now my purpose is:
Form connected with the only one table tbCustomers in the MySQL database.
It works, and I can see and modify data.
No need for basic macros, so far.
Now I want to make it harder. I add two text fields, not connected. One named txtPhone, other named txtAddr
When the user starts typing into one txt field, no matter what, the "modified" event should trigger a basic function that will compile a SQL string to filter records of the tbCustomers table in the form

I did it quite easy using msAccess. This was the working code:

Code: Select all   Expand viewCollapse view
' This sub makes a search in the customers table for a phone number
' that contains the digits that the user inserts as a pre-selection
' into the text box TxNumTel

Private Sub TxNumTel_Change()
    Dim TelNumParz As String        ' partial phone number
    Dim SQL_query As String         ' SQL quuery string
    TelNumParz = Me.TxNumTel.Text   ' Partial phone number from the TxNumTel text box
   ' If the partial phone number is not empty
   ' I prepare a SQL query string
   ' and I will assign it to the listbox RpRicercaElencoClienti
   ' then I start a requery

    If Not IsNull(TelNumParz) Then
        SQL_query = " Select ID,Nominativo,Indirizzo,Telefono,Note " & _
        "from Clienti where (Telefono like " & Chr$(34) & "*" & TelNumParz & "*" & Chr$(34) & ");"
        Me.RpRicercaElencoClienti.RowSource = SQL_query
    End If

End Sub

But I can't succeed to understand how to make it with OpenOffice base + MySQL + basic
The main problem is that I can't understand how to get my information from the API/UNO documentation. I get lost.
Any hint?
Thanks a lot

Re: Modify data source in a form

PostPosted: Thu Dec 07, 2017 7:05 pm
by Villeroy
Install the MRI extension. It comes with a small Basic module.
Read [Tutorial] Introduction into object inspection with MRI

Bind your form control event to MyMacros>MriLib>Module1.Mri and see

Re: Modify data source in a form

PostPosted: Thu Dec 07, 2017 7:39 pm
by salrandazzo
Thanks, I'll go on

Re: Modify data source in a form

PostPosted: Thu Dec 07, 2017 8:22 pm
by Villeroy
But first of all you should learn each and every aspect of Base forms. They are very simplistic, nevertheless flexible and powerful. Any form created by the "form wizard" does not even cover 10% of the capabilities.
If you implement some "power filtering" (search this forum) together with a most simple, event driven script which refreshes the filtered subform, then you have a filter-as-you-type form.