Subject Re: Lookuprowset
From Peter <phb2020@hotmail.com>
Date Wed, 03 May 2023 16:33:05 -0400
Newsgroups dbase.getting-started

Peter Wrote:
Ins_info file would not attach.  examples:  MC, Medicare; OX, Oxford, CI, Cigna, AE, Aetna.

> Peter Wrote:
 > Hi, Mervin,
 > I was able to get your example up and running.
 > I then tried to modify it to do the same thing for Ins_info file.  User enters 2 character Ins_code and the onKey function returns ins_name to entryfield2.
 > I get \"Invalid Ins ID\", entryfield1 gets focus with only one space.
 > The INS_INFO file has 2 fields, INS_CODE (2 characters), and INS_NAME (40 characters). I hopefully attached it.  MDX Index is on INS_CODE tag INS_CODE.
 > Can you find my error?
 > With thanks as always,
 > Peter
 >
>
> ** END HEADER -- do not remove this line
 > //
 > // Generated on 05/03/2023
 > //
 > parameter bModal
 > local f
 > f = new lookup_testForm()
 > if (bModal)
 >    f.mdi = false // ensure not MDI
 >    f.readModal()
 > else
 >    f.open()
 > endif
 >
> class lookup_testForm of FORM
 >    with (this)
 >       onOpen = class::FORM_ONOPEN
 >       height = 22.3182
 >       left = 26.5714
 >       top = -0.8182
 >       width = 64.7143
 >       text = \"\"
 >    endwith
 >
>    this.DATABASE1 = new DATABASE(this)
 >    with (this.DATABASE1)
 >       left = 36.0
 >       top = 3.0
 >       width = 7.0
 >       height = 1.0
 >       databaseName = \"MYDATA\"
 >       active = true
 >    endwith
 >
>    this.INS_LOOKUP1 = new QUERY(this)
 >    with (this.INS_LOOKUP1)
 >       left = 36.0
 >       width = 9.0
 >       height = 1.0
 >       sql = \"select * from :MYDATA:ins_info where ins_code=upper(:ins_code)\"
 >       params[\"ins_code\"] = \"\"
 >       active = true
 >    endwith
 >
>    this.ENTRYFIELD2 = new ENTRYFIELD(this)
 >    with (this.ENTRYFIELD2)
 >       when = {||false}
 >       height = 1.0
 >       left = 18.0
 >       top = 6.3636
 >       width = 40.0
 >       fontSize = 12.0
 >       value = \"\"
 >    endwith
 >
>    this.TEXT1 = new TEXT(this)
 >    with (this.TEXT1)
 >       height = 0.7727
 >       left = 3.1429
 >       top = 4.9545
 >       width = 10.8571
 >       text = \"Enter Ins ID \"
 >    endwith
 >
>    this.ENTRYFIELD1 = new ENTRYFIELD(this)
 >    with (this.ENTRYFIELD1)
 >       onKey = class::ENTRYFIELD1_ONKEY
 >       height = 1.0
 >       left = 4.4286
 >       top = 6.4545
 >       width = 8.0
 >       picture = \"!!\"
 >                 maxlength = 2
 >       fontSize = 12.0
 >       value = \"  \"
 >    endwith
 >
>    this.ENTRYFIELD3 = new ENTRYFIELD(this)
 >    with (this.ENTRYFIELD3)
 >       height = 1.0
 >       left = 4.4286
 >       top = 9.0455
 >       width = 25.7143
 >       value = \"dummy to accept focus\"
 >    endwith
 >
>    this.TEXT2 = new TEXT(this)
 >    with (this.TEXT2)
 >       height = 1.7273
 >       left = 5.0
 >       top = 11.1818
 >       width = 48.4286
 >       text = \"Entering a valid two character stateID will look up the State name and dispay it in entryfield2.\"
 >    endwith
 >
>    this.TEXT3 = new TEXT(this)
 >    with (this.TEXT3)
 >       height = 1.5
 >       left = 5.0
 >       top = 13.2727
 >       width = 48.1429
 >       text = \"Entryfield2 can never receive focus as its WHEN event handler has been set {||false}\"
 >    endwith
 >
>    this.TEXT4 = new TEXT(this)
 >    with (this.TEXT4)
 >       height = 2.2727
 >       left = 5.0
 >       top = 16.1818
 >       width = 48.1429
 >       text = \"A valid stateId will lookup the State name and display it in entryfield2.  Focus will move to the next object in the z_order.\"
 >    endwith
 >
>    this.TEXT5 = new TEXT(this)
 >    with (this.TEXT5)
 >       height = 1.5
 >       left = 5.0
 >       top = 19.2273
 >       width = 48.1429
 >       text = \"An invalid stateID will blank the entry in entryfield1 and wait for a new value.\"
 >    endwith
 >
>    this.TEXT6 = new TEXT(this)
 >    with (this.TEXT6)
 >       height = 1.5
 >       left = 4.2857
 >       top = 0.6818
 >       width = 30.7143
 >       text = \"Mimic the DML LOOKUP() function in OODML\"
 >    endwith
 >
>
>    function ENTRYFIELD1_OnKey(nChar, nPosition,bShift,bControl)
 >       if  len(TRIM(this.value)) >1  // Two characters entered
 >          form.INS_LOOKUP1.params[\'INS_CODE\'] = this .value //pass value in entryfield to query parameter
 >          form.INS_LOOKUP1.requery()  // fetch state name for the given stateID
 >          //Test to see if a valid stateID was entered
 >          if form.INS_lookup1.rowset.count() = 1 //found the state
 >             form.entryfield2.value = form.Ins_lookup1.rowset.fields[\'ins_name\'].value
 >             this.before.setfocus()  //Move focus to next object in z-order
 >          else // stateId not found  
 >             form.entryfield2.value = \'Invalid INS Code\'
 >             form.entryfield1.value = \'\' //Empty entryfield
 >             form.entryfield1.setfocus() //Back to entryfield to try again
 >          endif  
 >       endif  
 >       return
 >
>    function form_onOpen()
 >       form.entryfield1.setfocus()
 >       return
 >
> endclass
 >
>
>
> > Mervyn Bick Wrote:
 >
> > >
> > >
> > > ** END HEADER -- do not remove this line
 > > > //
 > > > // Generated on 2023-05-01
 > > > //
 > > > parameter bModal
 > > > local f
 > > > f = new lookup_testForm()
 > > > if (bModal)
 > > >    f.mdi = false // ensure not MDI
 > > >    f.readModal()
 > > > else
 > > >    f.open()
 > > > endif
 > > >
> > > class lookup_testForm of FORM
 > > >    with (this)
 > > >       onOpen = class::FORM_ONOPEN
 > > >       height = 21.5455
 > > >       left = 27.1429
 > > >       top = 0.0455
 > > >       width = 60.2857
 > > >       text = \"\"
 > > >    endwith
 > > >
> > >    this.STATES_LOOKUP1 = new QUERY(this)
 > > >    with (this.STATES_LOOKUP1)
 > > >       left = 36.0
 > > >       width = 11.0
 > > >       height = 1.0
 > > >       sql = \"select * from states_lookup.DBF where stateID = upper( :stateID)\"
 > > >       params[\"stateID\"] = \"\"
 > > >       active = true
 > > >    endwith
 > > >
> > >    this.ENTRYFIELD2 = new ENTRYFIELD(this)
 > > >    with (this.ENTRYFIELD2)
 > > >       when = {||false}
 > > >       height = 1.0
 > > >       left = 24.0
 > > >       top = 6.3636
 > > >       width = 29.0
 > > >       value = \"\"
 > > >    endwith
 > > >
> > >    this.TEXT1 = new TEXT(this)
 > > >    with (this.TEXT1)
 > > >       height = 0.7727
 > > >       left = 3.1429
 > > >       top = 4.9545
 > > >       width = 14.5714
 > > >       text = \"Enter State ID \"
 > > >    endwith
 > > >
> > >    this.ENTRYFIELD1 = new ENTRYFIELD(this)
 > > >    with (this.ENTRYFIELD1)
 > > >       onKey = class::ENTRYFIELD1_ONKEY
 > > >       height = 1.0
 > > >       left = 4.4286
 > > >       top = 6.4545
 > > >       width = 8.0
 > > >       value = \"\"
 > > >    endwith
 > > >
> > >    this.ENTRYFIELD3 = new ENTRYFIELD(this)
 > > >    with (this.ENTRYFIELD3)
 > > >       height = 1.0
 > > >       left = 4.4286
 > > >       top = 9.0455
 > > >       width = 25.7143
 > > >       value = \"dummy to accept focus\"
 > > >    endwith
 > > >
> > >    this.TEXT2 = new TEXT(this)
 > > >    with (this.TEXT2)
 > > >       height = 1.7273
 > > >       left = 5.0
 > > >       top = 11.1818
 > > >       width = 48.4286
 > > >       text = \"Entering a valid two character stateID will look up the State name and dispay it in entryfield2.\"
 > > >    endwith
 > > >
> > >    this.TEXT3 = new TEXT(this)
 > > >    with (this.TEXT3)
 > > >       height = 1.5
 > > >       left = 5.0
 > > >       top = 13.2727
 > > >       width = 48.1429
 > > >       text = \"Entryfield2 can never receive focus as its WHEN event handler has been set {||false}\"
 > > >    endwith
 > > >
> > >    this.TEXT4 = new TEXT(this)
 > > >    with (this.TEXT4)
 > > >       height = 2.2727
 > > >       left = 5.0
 > > >       top = 16.1818
 > > >       width = 48.1429
 > > >       text = \"A valid stateId will lookup the State name and display it in entryfield2.  Focus will move to the next object in the z_order.\"
 > > >    endwith
 > > >
> > >    this.TEXT5 = new TEXT(this)
 > > >    with (this.TEXT5)
 > > >       height = 1.5
 > > >       left = 5.0
 > > >       top = 19.2273
 > > >       width = 48.1429
 > > >       text = \"An invalid stateID will blank the entry in entryfield1 and wat for a new value.\"
 > > >    endwith
 > > >
> > >    this.TEXT6 = new TEXT(this)
 > > >    with (this.TEXT6)
 > > >       height = 1.5
 > > >       left = 4.2857
 > > >       top = 0.6818
 > > >       width = 30.7143
 > > >       text = \"Mimic the DML LOOKUP() function in OODML\"
 > > >    endwith
 > > >
> > >
> > >    function ENTRYFIELD1_OnKey(nChar, nPosition,bShift,bControl)
 > > >       if  len(this.value) >1  // Two characters entered
> > >          form.states_lookup1.params[\'stateID\'] = this .value //pass value in entryfield to query parameter
 > > >          form.states_lookup1.requery()  // fetch state name for the given stateID
 > > >          //Test to see if a valid stateID was entered
 > > >          if form.states_lookup1.rowset.count() = 1 //found the state
 > > >             form.entryfield2.value = form.states_lookup1.rowset.fields[\'state\'].value
 > > >             this.before.setfocus()  //Move focus to next object in z-order
 > > >          else // stateId not found  
> > >             form.entryfield2.value = \'Invalid StateID\'
 > > >             form.entryfield1.value = \'\' //Empty entryfield
 > > >             form.entryfield1.setfocus() //Back to entryfield to try again
 > > >          endif  
 > > >       endif  
> > >       return
 > > >
> > >    function form_onOpen()
 > > >       form.entryfield1.setfocus()
 > > >       return
 > > >
> > > endclass
 > > >
> >
> > I haven\'t forgotten about you, Mervyn.
 > > I am trying to change your form to use the actual DBF names that I have.
 > > If I get stumped I will paste what I have changed and resend to you.
 > > Peter
 >
>