| Subject | Re: AutoFill_entryfield error |
| From | Ken Mayer <dbase@nospam.goldenstag.net> |
| Date | Wed, 20 May 2020 10:58:51 -0700 |
| Newsgroups | dbase.getting-started |
| Attachment(s) | autofill_entryfield.cc |
On 5/17/2020 11:28 AM, Tom wrote: > > I understand you cannot run the code since you do not have the same > tables but, by inspection, do you see what is causing the error? Tom -- found the problem (sorry it took so long, I was in the middle of a lot of different things). Code in the autoFill_entryfield2 object's startup code was overwriting the databaseName property, which in cases where the data is all in one database is fine, but when in different databases, obviously, is a problem. Anyway, it was a relatively simple fix once I found it ... the attached should solve it. Ken -- *Ken Mayer* Ken's dBASE Page: http://www.goldenstag.net/dbase The dUFLP: http://www.goldenstag.net/dbase/index.htm#duflp dBASE Books: http://www.goldenstag.net/dbase/Books/dBASEBooks.htm dBASE Tutorial: http://www.goldenstag.net/dbase/Tutorial/00_Preface.htm | |
/* ----------------------------------------------------------------------------- autofill_entryfield.cc Authors..: Omar Mohammed, Ken Mayer Credit...: Mervyn Bick, Ronnie MacGregor, Akshat Kapoor Credit for various bits of help in places on this, it's a group effort, really. Date.....: April, 2020 May, 2020 (fix in autofill_entryfield2) Version..: 1.5 Notes....: This file contains two auto fill entryfield classes. The first is based on work by Omar Mohammed, and is designed to act very much like the way Excel does when typing a value in a column and fills in possible choices as you type. You can then accept them and move on or continue typing -- this uses just a dataLink to the field in question. The second is designed to work a little differently, in that it requires the dataLink, but it can uses a different table as a sort of dictionary. However, it is not designed to add to the "dictionary" table. Therefore it has what would be called if using a combobox or listbox, a dataSource as well as a dataLink. Rather than using the values in the "dataLinked" column, it uses the values from another table and field ... This one is subclassed from the first one ... Both versions should work with an ADO database as well as a BDE-based database, local tables, or otherwise. See the usage explanations in the documentation below. NOTE: ADO Code tested with MySQL by Ken Mayer. ----------------------------------------------------------------------------- */ /* ----------------------------------------------------------------------------- Class....: autofill_entryfield Author...: Omar Mohammed Date.....: March 18, 2020 -- modified from an example posted by Omar in the dBASE newsgroups, by Ken Mayer, attempting to turn this into a "generic" custom class. Version..: 1.1 April 20, 2020 -- updated to deal with issues having to deal with data modules, minor corrections in code, but necessary, and a few issues with ADO databases. Notes....: This is an autofill entryfield like the way Excel (or Google, sort of) uses to fill the rest of the word for you by searching the current contents of the table in the dataLinked field. The one thing this doesn't do is provide, if there are more than one match, a list of options to select from. That would require a combobox control and adding calls to load the dataSource for the combobox, etc. Usage....: set procedure to :dUFLP:autofill_entryfield.cc ** on a form in the form designer, place this control, ** and set the dataLink like you would any other entryfield. ** that's it. When run, adding a new record or editing an ** existing one will load information and as the user types ** attempt to find a matching value ... If this is an ADO database, you need to set the following in the form's onOpen event handler (ensures we use dBASE's ADO objects as needed): function form_onOpen() // any other code you might need form.AUTOFILL_ENTRYFIELD1.ado := true return ----------------------------------------------------------------------------- */ class autofill_entryfield( oParent ) of entryfield( oParent ) custom with (this) key = class::autofill_KEY onKey = class::autofill_ONKEY onOpen = class::autofill_OnOpen value = "" endwith // --------------------- // custom properties // --------------------- // this.ado should be set true in form's onOpen, canOpen, or // other appropriate method/event handler this.ado = false // is this an ADO database? // this information is found in the onOpen event handler: this.fieldName = "" this.tableName = "" this.dataBase = "" function onDesignOpen( lFromPalette ) // only execute this code when dropped onto a form from // the palette ... Set default height/width properties: if not lFromPalette return endif if this.form.metric # 0 and form.metric # 6 msgbox( "Please use either form metric of 0 (Chars) or "+; "6 (Pixels) for this control." ) return endif if this.form.metric == 0 // chars this.height = 1 this.width = 28 else this.form.metric == 6 // pixel this.height = 22 this.width = 196 endif return function autofill_onOpen() // check to see if dataLink is set, if not, // return error and close form if this.dataLink == "" msgbox( "Entryfield must be dataLinked to a field in the table!",; "autoFill error", 16 ) form.close() endif // if we do have a dataLink, let's work with it ... this.fieldName = this.dataLink.fieldName // parent = fieldArray // parent = rowset this.tableName = this.dataLink.parent.parent.tableName // ----------------------------------------------------------------------- // do we have a database? // parent = fieldArray // parent = rowset // parent = query this.databaseName = this.dataLink.parent.parent.parent.database.databaseName this.database = null // the default database is _app.databases[1] which // points to the current folder, it's really not // needed for this. It doesn't have a value for // the databaseName property if not empty( this.databaseName ) this.database = this.dataLink.parent.parent.parent.database endif // ----------------------------------------------------------------------- // if we have a backslash in the table path, the odds are that the // table is a local table, and for our purposes we want to strip // out the path to the table (returned by the tableName property // of the rowset object above) ... if "\" $ this.tableName this.tableName = right( this.tableName, ; len( this.tableName ) - rat("\", this.tableName ) ) endif // put the lookup query code in its own method class::setupQuery() // set up the ListBox control: class::setupListBox() return // eof: autofill_onOpen() function setupQuery // define the database and the query here, so it is in one place. // query to search for field uses current table and field that we're // dataLinked to in a separate query. if not this.ado this.q = new query() else this.q = new adoquery() endif // this.ado // if we have a databaseName we should set the reference to // the database object: if not empty( this.databaseName ) this.q.database = this.database // set above endif // ----------------------------------------------------------------------- // Tricky trying to insert the properties into the select statement, // basically we're limiting the query to a single field, the one we're // dataLinked to, but in order to set the sequence we're using a // calculated field (upper(fieldname)) and using that in the order // by clause. Weirdly, this does come together and appears to work // properly. // * There are quotes around the field name in case it has spaces in it, // or it's a reserved word, or ... // * DISTINCT is used in case of duplicate values in the field, this will // shorten the listing to a single record for each variation ... (SQL // version of a UNIQUE index option with dBASE tables) // (Thanks to Mervyn Bick for helping with this monster) // modified to use parameter for the field ... // Ronnie MacGregor suggested trying without quotes, as it was causing // issues with MySQL and ADO, and it seems to work fine ... oy! (the // original version had quotes around field and table names) this.q.sql := [SELECT DISTINCT t.] + this.fieldName + [ as lookup_field, upper( t.] +; this.fieldName + [ ) as upper_field] +; [ FROM ] + this.tableName + [ t]+ ; [ where upper( t.] + this.fieldName +[ ) LIKE :partial]+; [ order by upper_field] // parameters are the best way to handle this, so we can just do // a requery when we need it, rather than redefining the SQL statement // each time the user types something. If this is an ADO query, // then we need to do this a little different: if not this.ado this.q.params["partial"] = "%" else // modified to use the .value property, because ADO requires it // Akshat Kapoor reminded me of this: this.q.parameters["partial"].value = "%" endif this.q.active := true return // eof: setupQuery() function setupListbox // use a listbox and display options if there's more than 1 ... // the listbox will display under the entryfield, but to // keep everything encapsulated we are assigning the object reference // as a custom property of this control (allows for multiple instances // of this control on a form). oThis = this // reference the entryfield control this.list = new listbox( oThis.parent ) this.list.visible := false // only display as needed // top, left and width properties: this.list.top := oThis.top + oThis.height // directly under entryfield this.list.left := oThis.left // left of entryfield this.list.width := oThis.width // width of entryfield // suggested by Akshat Kapoor: this.list.height := oThis.height*3 // 3 times height of entryfield // height is complicated (get position of the bottom of the listbox, // if it is > than the bottom of the form, we need to bring it up ...): if this.list.top+this.list.height > oThis.parent.height // simplified, based on suggestion by Akshat Kapoor: this.list.height = oThis.parent.height - this.list.top /* if oThis.parent.metric == 6 this.list.height := oThis.parent.height - this.list.height - 30 // pixels else this.list.height := oThis.parent.height - this.list.height - 1 // char endif */ endif this.list.af_field = oThis // Can't use "parent" property (read-only) this.list.onSelChange := {; this.af_field.value = this.value; this.visible=false } return // eof: setupListbox function autofill_key( nChar, nPosition, bShift, bControl) // the key event fires for every keystroke (well, almost, there are // some that don't seem to fire it). // this routine is checking for backspace/delete and/or spaces // being typed as part of the value. In the case of backspace/delete // we want to ignore it in the autofill_onKey() event handler below. // In the case of a space, we want to make sure it's part of the // text ... this.bspa = false // 8 = backspace // 127 = delete if (nchar == 127 or nchar == 8) this.bspa = true endif this.space = false // 32 = space if( nChar == 32 ) this.space = true endif return true // eof: autofill_key() function autofill_onKey(nChar, nPosition, bShift, bControl) // the onKey event fires after the key event handler // if the user pressed Backspace or Delete, return if this.bspa = true return endif // Omar's code forced upper case, but in a lot of cases // upper case is not always desirable for the entryfield value partial_field = this.value.rightTrim().leftTrim() // if the user typed a space (using the example data, "NEW " // for example), we want to add it into the text so we're not // having issues with ... well, everything. if this.space partial_field += " " endif // by setting the query in the onOpen event handler, we need to // use a parameter and a requery: if not this.ado this.q.params["partial"] = upper( partial_field ) + "%" else this.q.parameters["partial"].value = upper( partial_field ) + "%" endif // restate the sql property of the query with new values // for parameter "partial": this.q.requery() // if there is a value/match found there will be at least one // record in the rowset if this.q.rowset.count() > 0 // get field but trim it ... full_field = this.q.rowset.fields["lookup_field"].value.rightTrim().leftTrim() // if the count is greater than 1 if this.q.rowset.count() > 1 // build an array for the listbox of all "matches" this.aData = new array() for i = 1 to this.q.rowset.count() this.aData.add( this.q.rowset.fields["lookup_field"].value.rightTrim().leftTrim() ) this.q.rowset.next() next // assign listbox dataSource this.list.dataSource := "array this.aData" // make it visible: this.list.visible := true else // if it is 1 or less then don't display the list anymore this.list.visible := false endif // this.q.rowset.count() > 1 else // we have one or zero matches // Altered code a bit, April 19, 2020 // If rowset.count is 0 or 1, turn off the list, because // it might still be there from an earlier match // AND set the "full_field" to the value of the partial (what the // user typed) this.list.visible := false full_field = partial_field endif // this.q.rowset.count() > 0 // for this we want to highlight (the selected value in the entryfield) // everything to the right of where the cursor was when the user // was typing, so that if they type more characters the selected text // disappears or updates appropriately. if len(full_field) > 0 // put text into entryfield this.value = full_field // how far back from the right side do we need to highlight // the text? backstr = len(full_field) - len(partial_field) // go to end of value displayed this.keyboard("{end}") // loop backward to highlight text to the right of where // the user had been typing for x = 1 to backstr this.keyboard("{shift+leftarrow}") endfor // x = 1 ... endif // len(full_field) > 0 return // eof: autofill_onKey() endclass /* ----------------------------------------------------------------------------- Class....: autofill_entryfield2 Author...: Ken Mayer Date.....: March 19, 2020 Version..: 1.1 April 19, 2020 -- updated to deal with issues having to deal with data modules, minor corrections in code, but necessary 1.2 May 20, 2020 -- fixed an issue with two different databases being used for the data, the code in autofill_onOpen was overwriting the object's databaseName property and then we couldn't find the correct table ... Notes....: This is a variation on autofill_entryfield (above), in that it is designed to perform an autoFill field, but rather than using data in the same field of the table as the source, it uses data from a different table, a sort of dictionary and is very much like working with a combobox. Usage....: set procedure to :dUFLP:autofill_entryfield.cc ** on a form in the designer, set an instance of ** autofill_entryfield2 on the form. Using the Inspector, ** open the form's onOpen event handler in the Source Code ** Editor (click "Events" in the inspector, find onOpen, ** click the tool button). Add the following code ** where appropriate in the function that is created ** (the following assumes the standard naming on a form ** in the form designer, adding a numeric value to the ** end of the name of the control): ** form.autofill_entryfield21.dataBaseName := "Name_of_Database" ** form.autofill_entryfield21.tableName := "MyLookupTable" ** form.autofill_entryfield21.fieldName := "MyLookupField" ** If there is no database (the tables are in the same folder ** as the application) you can leave it out. Otherwise, ** please define the database name, even if it is the same ** as the field the entryfield is dataLinked to. Example -- in the same folder: function form_onOpen() form.AUTOFILL_ENTRYFIELD21.tableName := "Autofill_Cities" form.AUTOFILL_ENTRYFIELD21.fieldName := "CityNames" return Example 2 -- in a database: function form_onOpen() form.AUTOFILL_ENTRYFIELD21.databaseName := "MyApp_Data" form.AUTOFILL_ENTRYFIELD21.tableName := "Autofill_Cities" form.AUTOFILL_ENTRYFIELD21.fieldName := "CityNames" return If you are using an ADO database for this, then you need to also set the following in the code above (either example), as this ensures we are using the correct ADO Data objects: form.AUTOFILL_ENTRYFIELD21.ado := true ----------------------------------------------------------------------------- */ // Subclass from the first control, meaning we don't have to repeat all // the code that is duplicate (most of it). class autofill_entryfield2( oParent ) of autofill_entryfield( oParent ) custom with (this) value = "" onOpen = class::autoFill_onOpen endwith // custom properties for dataSource this.ado = false // see instructions in "USAGE" above in the header, // for this version these should be set in the form's onOpen // event handler: this.databaseName = "" this.tableName = "" this.fieldName = "" function autofill_onOpen() // check to see if dataLink is set, if not, return error and close form if this.dataLink == "" msgbox( "Entryfield must be dataLinked to a field in the table!",; "autoFill error", 16 ) form.close() endif // we need to grab the value from the form's onOpen // event handler and put it somewhere else. // problem is that the code below, in this method, // will overwrite that value otherwise: if not empty( this.databaseName ) this.lookupDatabase = this.databaseName endif if this.tableName == "" msgbox( "You must define a lookup table!", "autoFill error", 16 ) form.close() endif if this.fieldName == "" msgbox( "You must define a lookup field!", "autoFill error", 16 ) form.close() endif // ------------------------------------------------------------ // do we have a database? // parent = fieldArray // parent = rowset // parent = query if not this.ado // For ADO this will pull the name of the database from the server, // which is not what we want for our purposes, as the User BDE // Database name may not match the name of the database as it is // stored on the server this.databaseName = this.dataLink.parent.parent.parent.database.databaseName endif this.database = null // the default database is _app.databases[1] which // points to the current folder, it's really not // needed for this. It doesn't have a value for // the databaseName property if not empty( this.databaseName ) this.database = this.dataLink.parent.parent.parent.database endif // if here, call setupQuery() method to actually do the query: class::setupQuery() // set up the listbox control class::setupListBox() return // eof: autofill_onOpen() function setupQuery() // query to search for field uses database, table and field that is // passed to the control in form's onOpen method. This is a little // different from the first autoFill control ... if not this.ado this.q = new query() else this.q = new adoQuery() endif // if we have a databaseName we should set the database object: if not empty( this.lookupDatabase ) // at this point we need to create the // database object: if not this.ado this.d = new database() this.d.databaseName := this.lookupDatabase else // ado: this.d = new adoDatabase() this.d.databaseName := this.lookupDatabase endif // not this.ado // activate the database object this.d.active = true // assign database to query this.q.database := this.d endif // not empty( this.lookupDatabase ) ? this.d.databaseName // Tricky trying to insert the properties into the select statement, // basically we're limiting the query to a single field, the one we're // dataLinked to, but in order to set the sequence we're using a // calculated field (upper(fieldname)) and using that in the order // by clause. Weirdly, this does come together and appears to work // properly. // * There are quotes around the field name in case it has spaces in it, // or it's a reserved word, or ... // * DISTINCT is used in case of duplicate values in the field, this will // shorten the listing to a single record for each variation ... (SQL // version of a UNIQUE index option with dBASE tables) // (Thanks to Mervyn Bick for helping with this monster) // Ronnie MacGregor suggested trying without quotes, as it was causing // issues with MySQL and ADO, and it seems to work fine ... oy! (the // original version had quotes around field and table names) this.q.sql := [SELECT DISTINCT t.] + this.fieldName + [ as lookup_field, upper( t.] +; this.fieldName + [ ) as upper_field] +; [ FROM ] + this.tableName + [ t]+ ; [ where upper( t.] + this.fieldName +[ ) LIKE :partial ]+; [ order by upper_field] if not this.ado this.q.params["partial"] = "%" // all rows to start else this.q.parameters["partial"].value = "%" // all rows to start endif // not this.ado this.q.active := true return // eof: setupQuery() endclass | |