Subject Re: QUERY ON FORM From Mustansir Ghor <mustan31@hotmail.com> Date Sun, 21 Jan 2018 04:08:46 -0500 Newsgroups dbase.getting-started Dear Mervyn Thanks for the information. 1. Sorry to put c:xyz. But this resulted in the getting more information on access path to .dbf file. If I use userBDEAlias then we need to mention database name in the query and also take into consideration inifile setting and its location. On other hand  putting simply filename means look for the file in current folder and then search for it if the set path to with path option given. I prefer second option. 2. Regarding qurey object  will the below satement can be considered valid mq2.rowset.fields["item"].lookupSQL = mq1 or we need to put mq2.rowset.fields["item"].lookupSQL = "select * from xyz" 3. I was avoiding lookupSQL considering that once query is active data is fecthed from network and rowset created is in local memory. Therefore by assigning lookuprowset it will speed up and be more stable. Is my this perception correct ? Pls do enlighten us such important issues Regards Mustnasir Mervyn Bick Wrote: > 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. > > > > > > > > >