Subject Re: Lookuprowset
From Mervyn Bick <invalid@invalid.invalid>
Date Fri, 5 May 2023 22:00:42 +0200
Newsgroups dbase.getting-started

On 2023/05/03 22:18, Peter wrote:
> Hi Mervin,
 > I am able to use the form as you have shown below.
 > I am trying to modify the form to do the same thing with
 > Insurance Information, such as Ins_code (2 character field) and Ins_name (40 character field).  User enters 2 letter code for Ins_code and OnKey function does a lookup in Ins_info file to show full length Insurance name and puts it in entryfield2.
 > Something is wrong.  Ins_code is not found. Entryfield2 shows \\\"Invalid Ins Name\\\". In addition when setfocus() goes back to entryfield1, there is only one space.
 > Can you find my error?  I have attached the lookuptest2.wfm, Ins_info.dbf. If no .MDX there, index on Ins_code
 >            Production MDX file: INS_INFO.MDX
 >            Index TAG: INS_CODE  Key: INS_CODE
 >
> I have another question, but I'll wait until this gets fixed.
 > Again, thank you.
 >
> Peter
 >
>

I'm having to cope with power outages so please excuse the delay.  We
only had power for 3 hours from 17:00 to 18:00 and from 22:00 to 00:30
on Wednesday, we were off from 10:00 to 12:30 and from 18:00 to 22:30
yesterday and today we were off from 08:00 to 10:30 and will be off
again from 16:00 to 18:30. :-(

Lee has got your form working but I have a few comments.

You have included a database object in the constructor code but you
don't actually use it.

Before the advent of OODML in dBASE, to open a table in a named database
the database name was used as a prefix, delimited with colons, to the
table name after the USE command.

   USE :MYDATA:ins_info

In your query's constructor code you have use the old XDML approach

sql = "select * from :MYDATA:ins_info where ins_code=upper(:ins_code)"

Although this works your should rather assign the database object to the
database property of your query.

database = form.database1
sql = "select * from ins_info where ins_code=upper(:ins_code)"

As you have used the PICTURE property of entryfield1 to force the input
to upper case you don't need the upper() function in the SELECT statement.

sql = "select * from ins_info where ins_code=:ins_code"

The parameters for the query are held in an ASSOC ARRAY which is case
sensitive.  The case of the parameter in the SELECT statement must match
the parameter name.  When you assign a new value to the parameter you
must use the exact name used in the query's constructor code.

Mervyn.