Subject Re: QUERY ON FORM
From Mervyn Bick <invalid@invalid.invalid>
Date Sun, 21 Jan 2018 09:53:23 +0200
Newsgroups dbase.getting-started

On 2018-01-20 11:53 PM, Mustansir Ghor wrote:
> Dear All
>
> I have put  following statement on form_open() method (xyz.dbf has fields code, name)
>
> mq1 = new query()
> mq1.sql = select * from c:xyz"
> mq1.active = true
>
> when I refer to this query later in the form with following statement it says variable mq1 undefined
>
> form.mq2,rowset,fields["item"].lookuprowset = mq1.rowset
>
> Can anybody guide where is the error.

Firstly, the SELECT statement must be in quotes otherwise you can't
assign it to the query's sql property.

mq1.sql = 'select * from c:xyz'

Unfortunately this still won't work as in SQL a colon signifies a
parameter, in other words a value stored in a variable.  A parameter
can't be used to pass a tablename to a select statement.

localSQL will recognise c: as meaning drive C provided it is in a string
literal.   You must, however also provide a folder so for a file in the
root directory you would need

mq1.sql = 'select * from "c:\xzy"'

There is no need to include the .dbf extension when working with .dbf
files.  The extension is only actually needed with Paradox files.  The
table name only needs to be presented as a literal where a drive letter
is included or where there are spaces in the table name or path.  (dBASE
does not allow spaces in table names but Paradox does.)

One shouldn't store data in the root directory of the c: drive so it
would be better to use

mq1.sql = 'select * from "c:\path\xyz"'

Hard-coding a path in a select statement is not a good idea.  If your
data is not in the same folder as your program you should create a
BDEAlias, a User BDEAlias or a temporary BDEAlias pointing to the data
folder and use a database object to access it.  See BDE Alias in the
help file.

If the table is in the same folder as the program then

mq1.sql = 'select * from xzy'  will create the rowset you need once the
query's active property has been set true.  You can then assign the
rowset as the lookuprowset for mq2.

mq1 = new query()
mq1.sql = 'select * from xyz'
mq1.active= true
form.mq2.rowset.fields["item"].lookuprowset = mq1.rowset

This rowset only exists after an instance of the form has been created
so it can't be assigned as a lookuprowset until then.  As the query is a
"stand-alone" object in the form's onOpen event handler that is the only
place where an assignment operator will be able to find it.   If you
wanted to make the assignment from anywhere else then mq1 needs to
assigned to the from.

form.mq1 = new query()
form.mq1.sql = 'select * from xyz'
form.mq1.active = true

Now the new rowset can be accessed from anywhere in the form but only
after the form has actually been created in memory.  It still can't be
assigned as a lookuprowset for a field in form.mq2 in form.mq2's onOpen
event handler as form.mq2 is opened in memory before the form actually
opens.

The only place you can assign mq1.rowset (whether mq1 is "stand-alone"
or a property of the form) as a lookuprowset is from within the form's
onOpen event handler.  ALL the objects on the form open before the form
opens which then triggers its onOpen event handler.  The onOpen event
handler of one of these objects looking for mq1 won't be able to find it
because it hasn't been created yet.

It would be better to use the item field's lookupSQL property to create
the lookup for mq2 instead of the lookupRowset property.  dBASE will
automatically create the lookup after mq2 has opened.

Mervyn.