Subject Re: Lookuprowset
From Peter <phb2020@hotmail.com>
Date Wed, 03 May 2023 16:30:11 -0400
Newsgroups dbase.getting-started
Attachment(s) ins_info.DBF

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