Subject Re: lookupsql and Combobox ; where is the missing link
From Mervyn Bick <invalid@invalid.invalid>
Date Sat, 30 May 2020 14:45:28 +0200
Newsgroups dbase.getting-started

On 30/05/2020 11:33, Mervyn Bick wrote:

> I've had a quick look at the code you posted in the binaries newsgroup.
> I'll dig a bit deeper later if the message I posted earlier doesn't help
> you solve the problem yourself but please don't use the combobox's
> dataLink property.  Rather use the dataSource property.  In fact,
> probably for nine out of ten cases you shouldn't need to use a
> combobox's dataLink property.
I've made the following changes to your code.

Uncommented

    this.VRAAGBAK_LAND = new QUERY(this)
    with (this.VRAAGBAK_LAND)
       left = 252.0
       top = 396.0
       width = 80.0
       height = 37.0
       database = form.databank_cust
       sql = "select * from COUNTRY"
       requestLive = false
       active = true
    endwith

    with (this.VRAAGBAK_LAND.rowset)
       autoEdit = false
    endwith

Changed

    this.KEUZEDOOS_LAND = new COMBOBOX(this)
    with (this.KEUZEDOOS_LAND)
       onChange = class::KEUZEDOOS_LAND_ONCHANGE
       height = 24.0
       left = 70.0
       top = 105.0
       width = 170.0
       fontBold = true
       colorNormal = "WindowText/0xbfbfbf"
       dataSource = form.vraagbak_land.rowset.fields["Land"]
       style = 2        // DropDownList
       dropDownHeight = 132.0
    endwith

Replaced

   function KEUZEDOOS_LAND_onChange()
       lur = form.vraagbak_stammdat.rowset.fields['country'].lookuprowset
       lur.applyLocate( "land = '" + this.value + "'" )
       form.vraagbak_stammdat.rowset.filter = "country =
'"+trim(lur.fields[1].value)+"'"
    RETURN

Added

     form.keuzedoos_land.value := "(selecteren)"

after

     form.keuzedoos_sorteer.value := "(selecteren)"

in  function form_onOen

Something in your code is overwriting this as the combobox opens blank.
A quick scan of the code doesn't immediately show any code that could be
doing this.  Unfortunately I don't have the time right now to dig
deeper.  Selecting a value does, however, set the filter.

There are 62 records in country.dbf and they will all display in the
combobox dropdown list.  It would be far more "user friendly" to limit
the list to only those countries represented in stammdat2.dbf.

  this.VRAAGBAK_LAND = new QUERY(this)
    with (this.VRAAGBAK_LAND)
       left = 252.0
       top = 396.0
       width = 80.0
       height = 37.0
       database = form.databank_cust
       sql = "select * from COUNTRY where landcode in (select distinct
country from stammdat2)"
       requestLive = false
       active = true
    endwith

    with (this.VRAAGBAK_LAND.rowset)
       autoEdit = false
    endwith

This does, however, mean that you need to do a bit more work if you add
new counties (or change an existing country value to something else) to
stammdata2.dbf.  If you don't, th new country will not show in the
dropdown list until you restart the program.

After you save the new or altered record you will need nto requery both
the vraagbak_stammdat and the vraagbak_land queries and you will also
need to restate the combobox dataSource property.


       form.vraagbak_stammdat.rowset.save()
       form.vraagbak_stammdat.requery()
       form.vraagbak_land.requery()
       form.keuzedoos_land.dataSource =
form.vraagbak_land.rowset.fields["Land"]

Mervyn.