The MemoText Control
Storing long-length text in table rows.
by Ronnie MacGregor — Date: March 2001

| Introduction History Functionality The MemoText Table Using the control Reports with MemoText Converting to a Custom DataModule Quick Start Converting to a Custom Form Conclusion

To download the files for this article for your own use, click here |

Introduction

One of the common needs of an application is the ability to store text of unknown length. The dBASE table format can provide a field of up to 254 characters, but this limit is quickly reached when you want to store free form notes or dialogue.

Some table formats provide for variable-length fields, where the space used on the hard drive varies depending on the field contents. This type of table may provide greater scope for storing long-length text within the table itself, but because of the unpredictable position of field boundaries on the hard drive, if you are unfortunate enough to suffer file corruption, then you may find that your data is very much more difficult to recover. dBASE tables on the other hand have historically always been relatively easy to deal with in terms of salvaging data, because the fields for each row are marked out on the hard drive whether they contain data or not.

The most common way for fixed-length field tables to provide variable-length storage, is to provide a field which links to a “blob” file. The latter is a separate file, and in addition to storing text, can store binary data, thus allowing the storage of image or sound files and the like. The Memo field provided in the dBASE table format is the link-field which points to variable-length text stored in a`.dbt` file.

Back in the days of dBASE IV our own experience was that while the main dBASE table was fairly robust, the`.dbt` file was a bit more fragile. A lot of our problems may have been attributable to the hardware cache cards we were using at the time. Nevertheless it has meant that we have been wary of relying on`.dbt` files for long-length text storage ever since. It seems from occasional messages in the dBASE newsgroups that this concern is shared by others.

We do use`.dbt` files quite happily for image data, mainly in catalogue-type applications where there is little or no change to the data once it is compiled. It seems that some developers avoid using a`.dbt` file for this purpose by simply storing a link to a discreet image file. Similarly, long-length text for use in a standard editor control can be stored in a discrete “txt” file, and a link to this stored in a normal table. This does mean that you could end up with a directory containing a huge number of txt files, and to me this seems somewhat to defeat the purpose of using a database. With good data backup procedures you could argue that there is nothing to worry about, and using a`.dbt` file should be fine. You could also argue that the most basic storage requirement of a table is text. Why should you have to use a complex file structure capable of storing binary data when all you want to do is store simple text? It would also be nice to know that you are using the most robust storage method that you can.

The MemoText control allows you to store long-length text in the rows of a standard dBASE table.

back to index

#### History

Before the days of variable-length fields and blob files, developers had the same problem of how best to store long-length text. The technique that was used then was very simple: chop the text up into uniformly sized chunks, and store the pieces in standard table fields. When required, simply pull out all the pieces and stitch them back together again. In those days hard drives were often interleaved so that data might be read off every third sector, for example, and often consideration was given to sector and cluster size in choosing the length of the chunk of “MemoText” to store in order to maximise performance.

There is no reason why this same basic technique of chopping up and re-stitching cannot be put to good use for dBASE tables. This is what the MemoText control does, in conjunction with a simple table in place of a`` `.dbt` file.

back to index

#### Functionality

The control is based on an editor control, and is designed to behave just as any standard editor control would. The only difference is that the control transparently saves and retrieves its data to and from a separate “MemoText” table.

Just as multiple memo fields in a table are stored in a single`.dbt` file, the MemoText control will allow multiple instances of the MemoText control to share a single table.

In theory, there is no limit to the amount of text that can be stored in this way, other than the normal limits of the dBASE table format itself.

Back to index

#### The MemoText Table

The MemoText control requires a simple table with the following structure in which to store its data:

The name of this table is up to the developer, but it would seem to make sense to match it to the name of its “parent” table, and you could use a`.mtf` extension to indicate that the table contains MemoText. One way to do this is to create the MemoText table with a different name and a standard`.dbf` extension. Once created, simply rename the file. Once renamed with the non-standard extension, the file will not be readily visible in the dB2K development environment, and in this way perhaps more closely mimics the`.dbt` file. It also reduces the risk of someone opening the table and making changes directly, which is obviously not desirable. If you would rather be able to easily see and work with this table directly, why not use the name of the “parent” table and put the `mtf` suffix before the standard`.dbf` file extension. An alternative way to create the MemoText table is simply to set up the control without the table being present, and in this case the control will provide a prompt offering automatic creation of the table on first use.

The `Memo_ID` field contains a unique ten-character string, and this same string is stored to a ten-character field in the “parent” table. This is used to link to the constituent parts of the MemoText. Normally you would think of using autoinc fields to provide a unique value to use for linking, but we cannot do that here, because we will have several rows in the MemoText table which must have the same link value. We therefore need to provide our own unique value, and we have to consider that there may be several MemoText controls on a form which may all almost simultaneously be wanting to create new entries in our MemoText table. There may also be several instances of the same form open at one time, perhaps in a multi-user environment. In addition, there may be a requirement to merge tables from different machines, and we need to be able to do this without the risk of the donor tables using the same ID.

I have created a function to do this in just two lines of dBL code. The function takes the year, month, day, and the number of hundredths of a second passed midnight at the time of calling, forming a decimal string which is far in excess of the ten-character field width. This decimal string is then converted to an eight-character hex string, and a two character pseudo-random number is suffixed, as follows:

| `Function Generate_ID()` `return itoh(val(year(date()) ;` `+ str(month(date()),2,0,"0") ;` `+ str(day(date()),2,0,"0") ;` `+ str(int((seconds() * 100)),7,0,"0")),8) ;` `+ str((random()*100),2,2,"0")`

So far, this has never failed to provide a unique result. If you can demonstrate otherwise, please let me know!!

The `Memo_Sequence` field is probably not required. It will sequentially number up to 9999 parts of a single MemoText entry. In theory, this means that the maximum storage for a single entry is 999,900 characters, but in practice this could easily be exceeded. Why? Well, the MemoText control, although it populates this field, doesn’t actually use it. The table is used in natural order, and there is no way that I can see that the rows for a single MemoText entry could become out of sequence. I chose to leave this field in the table, however, so that in the unlikely event of table corruption, it would be easier for the developer to “see” the required sequence. Perhaps it is old memories of stitching damaged dBASE IV files back together at low level with a Hex editor that makes me happier to do this. I hope I never need it!!

The `Memo_Text` field is the field that holds the data. In choosing the field width for this purpose we should consider economy of disk space. There is no reason why this control could not have used the maximum field width available, but for an entry length which rolls just a few characters over a multiple of the field length, I thought this was a bit wasteful. Also 100 is a nice round number!!

back to index

Using the control

My preferred method of making a custom control available to a form has in the past been to put a copy of it in the same directory as the form, and use the source editor to insert the following line of code into the Constructor code for that form, as the second line of code for the class:

| `Class MemoTextCCForm of FORM` `set procedure to MemoText.cc additive`

Having done that, open the form in the designer, and you will find that the MemoText control is available on the custom tab of the Component Palette. It is now a simple matter to drag and drop an instance of the MemoText control onto your form. The control can then be named to suit, sized and positioned.

With the first release of dB2K we now have the option of using Source Code Aliasing, which allows you, if you wish, to reference each custom control you use in its own development directory, ensuring that you are always using the most up-to-date version. For more information on this, see Ken Mayer’s article in the special dB2K edition of the dBulletin.

Now we have to tell the control what it has to interact with, and this is done by calling the `mtSetup()` method of the control from the `onOpen()` event for the form

| `Function MyForm_onOpen` `form.MemoText1.mtSetup("MemoTextTable.mtf", this.MyRowset, "MemoText1_ID")`

There are three parameters required by the control. The first is the name of the table the control is going to use to store the MemoText in. The second is a reference to the rowset for the table which contains the linkfield for the MemoText control. The control needs to know this so that it can alter the rowset `modified` property, if required, when a change is made to the text in the MemoText control. This is to ensure that the rowset `save()` fires, which in turn will ensure that the changed contents of the MemoText control are saved. The third parameter is the name of the link field in the “parent” table, and it is the content of this field which the control will use to find the appropriate MemoText entries for the control.

With custom controls, I have always thought that ideally they should be completely self-contained with no outside dependencies other than perhaps the initial passing of parameters to the control on opening. This is not always possible. We are trying to create a control which is intended to behave exactly like a datalinked control, but in this instance, because we are not using the normal datalink of the editor control, and because the control needs to respond to rowset events, a little more setting-up is required.

We have to ensure that the MemoText control repopulates itself when its “parent” rowset navigates. We similarly have to ensure that the control responds correctly to a save, abandon, append, etc.. In order to do this we have to ensure that the rowset events for the “parent” rowset fire the corresponding event handlers for the control itself. I have chosen to set up my data access in a datamodule which can be used for reports as well, but however you choose to do this, the basic principle remains the same. If we want the datamodule to be able to fire the methods of our MemoText control, we need to give it a reference to the form which is using it. This is probably best done in the `onOpen()` event code for the form, and we are attaching a custom property `ParentForm` which will contain an object reference for the form, like so:

| `Function form_onOpen` `this.DATAMODREF1.ref.ParentForm = this`

Rather than have separate functions containing the code for the event handlers, I think it is neater and tidier to use codeblocks, keeping everything together. If we have more than one MemoText control, however, this will take more than one line of code in the codeblock. It can still be done, and the syntax needed to do it is given below. The source editor doesn’t take too kindly to this type of construction, so you’ll have to ignore its somewhat distracting colour interpretation of this block. Despite this, it does work!!

| `with (this.MyQuery.rowset)` `onNavigate = {;` `;try;` `;this.parent.parent.ParentForm.MemoText1.mtNavigate();` `;this.parent.parent.ParentForm.MemoText2.mtNavigate();` `;catch ( Exception e );` `;endtry;` `;}` `onEdit = {;` `;this.parent.parent.ParentForm.MemoText1.mtEdit();` `;this.parent.parent.ParentForm.MemoText2.mtEdit();` `;}` `onSave = {;` `;this.parent.parent.ParentForm.MemoText1.mtSave();` `;this.parent.parent.ParentForm.MemoText2.mtSave();` `;}` `onAbandon = {;` `;this.parent.parent.ParentForm.MemoText1.mtAbandon();` `;this.parent.parent.ParentForm.MemoText2.mtAbandon();` `;}` `onAppend = {;` `;this.parent.parent.ParentForm.MemoText1.mtAppend();` `;this.parent.parent.ParentForm.MemoText2.mtAppend();` `;}` `onDelete = {;` `;this.parent.parent.ParentForm.MemoText1.mtDelete();` `;this.parent.parent.ParentForm.MemoText2.mtDelete();` `;}` `endwith`

You may have noticed that the `onNavigate()` code is wrapped up in a `try/endtry`, and we will cover this when we come to reports with MemoText.

The control is now ready to use.

If you are interested in the code which is being fired in the control, open it up in the source editor, and take a look. It should be pretty much self-explanatory. The biggest problem has been to try and make sure that every possible user action is properly handled. If you find a sequence of user actions which is not properly handled, please let me know.

A sample test /demo form `MemoTextCC.wfm` is provided with this article, along with it’s datamodule `MemoTextCC.dmd` and tables `mtCustomer.dbf/.mdx`,`mtOrders.dbf/.mdx`, and `mtOrders.mtf`. Run the form and have a play with it. Add records, change existing records, delete records. Do whatever you can think of, and see what you think. I am sure that you will agree that although a little setting-up is required, it is pretty easy to do, and in use you would be unaware that anything different was happening behind the scenes.

Back to index

Reports with MemoText

You might think that it is all very well having a nice control which will chop up text, store the bits in table rows, and reconstruct them to repopulate the control, but how on earth are you supposed to produce a report containing MemoText?

The answer is remarkably simple. It is very easy using dBL to add a calculated field to the rowset containing the link field. We can use this to produce a field in the rowset which will be populated with the reconstructed MemoText. Here is a sample of the code that is required to create the field:

| `Function MyQuery_onOpen` `f = new Field()` `f.fieldName = "MemoText1"` `f.mtLinkField = "MemoText1_ID"` `f.mtTableName = "mtOrders.mtf"` `f.beforeGetValue = class::mtBuildMemo` `this.rowset.fields.add( f )`

The `beforeGetValue()` method of the field is set up to call `mtBuildMemo()` which is a generic function to reconstruct MemoText. How do we use a generic function for multiple fields, where the link field is obviously going to be different, and the MemoText may not even be stored in the same table? Normally when calling a function, we would simply pass the parameters required, but in this instance we have an example of how to pass parameters without passing parameters.

One of the great things about dB2K and the dBL language is the strong implementation of object hierarchy. Having come from using dBASE IV, you tend to think of a field as a field in a table, and you probably copied the field’s contents into a memory variable to work with it. dB2K effectively does this for you, by copying the field contents into the rowset’s field array, the elements of which are field objects. This means that just as you might attach a custom property to a visual object on a form, it is possible to attach custom properties to the field object. We have given our calculated field two custom properties,`mtLinkField`, which is assigned the name of our link field (which is in the same rowset), and `mtTableName` containing the name of the table containing the MemoText. This means that our generic MemoText rebuilding function can get what you would normally view as parameters, simply by examining these two custom properties. The power and flexibility of dBL is evident in how little code is required to do this.

| `Function mtBuildMemo()` `local sConcant, tpp` `sConcant = ""` `tpp = this.parent.parent` `if tpp.fields[this.mtLinkField].value = null` `return sConcant` `endif` `if not tpp.endofset` `// Set up Query` `sQueryString = "'select * from " + '"' ;` `+ this.mtTableName + '"';` `+ " where Memo_ID = " + '"' ;` `+ tpp.fields[this.mtLinkField]Value ;` `+ '"' + "'"` `//` `this.q = new Query()` `this.q.parent = this` `with (this.q)` `sql = &sQueryString.` `active = true` `endwith` `//` `// Build memo contents` `this.q.rowset.first()` `do while not this.q.rowset.endofset` `sConcant = sConcant + ;` `this.q.Rowset.Fields["Memo_Text"]Value` `this.q.Rowset.next()` `enddo` `// Close query` `this.q.active = false` `this.q = null` `//` `endif` `//` `return sConcant`

Because I have set up the data access in a datamodule, this function needs to be present in that datamodule, but it can equally be made available wherever you are setting up your data access. This could be within a form’s class code, or a report’s class code. Once this is set up, it is simplicity itself to add MemoText to a report. In the Report designer you will find the calculated field in the field palette, from whence it can be dragged and dropped onto the Report designer surface. A sample report with two MemoText fields can be run from a button at the bottom of the supplied demo form, or it can be run directly.

We are using the same datamodule for our report and the form. When the report runs, the rowset in the report instance will navigate, and this will try to fire the `onNavigate()` event code which we set up for our MemoText control. However we don’t really want our MemoText controls to have their contents changed unless the form itself is the source of the navigation. The `ParentForm` reference that we set up to allow the datamodule to reference the MemoText controls will be out of scope as far as the report is concerned, or may not exist, if for example the report was run directly, or if, as we are doing here in the sample code, we use a second instance of the datamodule for the report. This is why we have wrapped the `onNavigate()` code in a `Try/EndTry`. We know that if the `ParentForm` reference is out of scope an error will be generated, and handling this allows us to use the same code to do two jobs.

You have probably worked out by now that if we can produce a MemoText calculated field for use in a report, we can use the same technique to display MemoText on a form. All we need to do is use a standard editor control datalinked to the calculated field we added to the rowset. Obviously this is only of use in “Display Only” situations, and the best way to ensure that the user does not try to change the contents of the editor is to ensure that the editor cannot receive focus.

During testing, Bob Rimmington wanted to establish if he could use MemoText in conjunction with his User Report Generator (dBulletin issue 9), which provides a quick head-start with self-contained listing-type reports, which have their own query code. I think the best approach in doing this is to design your report using the link-field in place of the MemoText it links to. Once the initial report has been generated by the URG, open it in the editor. The calculated fields can be added to the appropriate query, and the `BuildMemo()` function code pasted into the class code. The link-field entries can now be changed to the corresponding calculated field, and the job is done — remarkably quickly and easily. A copy of this report,`URGlist.rep` is included in the zip file available at the top of this article.

Back to index

### Converting to a Custom DataModule

Having created a calculated field, and a `BuildMemo()` function to display our MemoText, and having done this in a datamodule, the next question is: “Can we put all of the MemoText functionality into a custom datamodule?” This would mean that we could just use a standard editor control on our forms, and it would have the plus point that data access functions are where perhaps they should be — behind the scenes — in a datamodule. On the downside, a custom control is relatively easy to perceive as an object with given functionality. The same functionality provided without the visual form object to go with it is a little trickier to perceive.

In fact, using a standard datalinked control has some advantages. Several of the rowset events which we had to deal with for our custom control will be dealt with automatically for us. Navigation will automatically populate the control. Abandoning changes will do the same. By dealing with the link-field ID generation for appended rows as part of the saving process we don’t need any append code, and this has the added advantage of making our link field ID behavior more like that of an autoinc field. In fact, the only rowset events we need to deal with are saving and deleting.

One of the difficulties in doing all of this using a calculated field is that such a field is intended to provide one-way traffic only: from the rowset field array to the datalinked form control, and not the reverse. This means that we have to do the work to ensure that any changes made in our standard editor control are saved correctly. Because we are using datalinked editor controls for our MemoText we have to save the contents of our editor control before the rowset is saved rather than just after, otherwise we will see our MemoText changes disappear as the control is repopulated with it’s original contents. For this reason we are using the rowset’s `canSave()` event rather than the onSave event. The code used for this is a very easy adaptation of the code from the custom control. For similar reasons we are using the rowset’s `canDelete()` method in place of the `onDelete()` event. It is difficult to know what MemoText rows to delete, when the linkfield in the “parent” table has just disappeared!! Again, the code needed for deletion is a very easy adaptation from the custom control.

In the creation of our calculated field, we have added another custom property to it —`mtLinkFieldPointer`.

| `f.mtLinkFieldPointer = this.rowset.fields["MemoText1_ID"]`

The two previous custom field properties are strings, which are used to construct the query SQL string for our `mtBuildMemo()` code. This time we are creating an object reference, or pointer, to the link-field itself. This is so that a generic function can write back to the link-field by changing the value of this custom field property. This ability will be used the first time MemoText is added to an editor control, and also if and when all the MemoText is deleted in an editor control.

When we were using our MemoText custom control, the control’s “rowset” events were fired by the corresponding rowset event. Each MemoText control took care of its own housekeeping once set up. If we want to use generic functions from a datamodule to do the same job, then the datamodule needs to know which fields in the rowset are MemoText fields. We do this by calling the `mtSetup()` function in the custom datamodule from the `onOpen()` event of the query for the rowset containing our calculated fields.

| `this.parent.mtSetup(this.rowset, "mtOrders.mtf")`

The second parameter containing the MemoText file name allows the `mtSetup()` function to check for the existence of the MemoText table, and if not found, offers automatic creation. With the first parameter, we are passing an object reference for the rowset to the function. The function then works its way through all the fields in this rowset looking for the custom properties we have created, thus identifying fields with these properties as MemoText fields, and once found, the field is added as an object reference to an array. This array will then be used when performing the saving and deleting code required for each MemoText field when the event is fired.

So far so good. We have calculated fields containing our MemoText. We have an array of MemoText fields for the custom datamodule functions to use. We need to save any changes made in our “MemoText” standard editor controls. How do we get our data to flow the wrong way up a one-way street? The first problem that we have here is that changing the contents of our standard editor control will not change the rowset’s `modified` property to `true`. A datalinked calculated field is considered to be used in read-only mode, so this would not normally be a requirement. We have to ensure, however, that if we make changes to our MemoText, that the Save events fire, and to do this we have to ensure that the rowset’s `modified` property is changed to `true`. Interestingly, any change made to the MemoText in a standard editor control will fire the `onChange()` event for the datalinked calculated field itself, and we can harness this by including the following line of code into our calculated field Construction code:

| `f.onChange = class::mtOnChange`

This in turn fires the following code in our custom datamodule:

| `this.parent.parent.modified = true`

This could have been done in one step using a codeblock, but if we want to think of our custom datamodule as a pseudo-custom control then it is probably better to have all the required event code visible in that one place. Now we know that the save events for the rowset will fire. It should now be simple to save our changed MemoText. Well, not quite. What are we going to save? The `value` property of our calculated field still contains the original contents of our field before we made our changes. It is the editor’s `value` property which contains our new MemoText. The problem here is that while our editor control knows about our calculated field, and is datalinked to it, neither the field nor the rowset have a clue about the existence of the editor control. How do we access the contents of the editor control from our custom datamodule code? The solution I have used here is to use the `onOpen()` event for the editor control to give the calculated field to which it is datalinked a new custom property `VisualControlRef` which will contain an object reference to itself (the editor control):

| `Function MemoTextEditor1_onOpen` `Form.datamodref1.ref.mtordersquery.rowset.fields["memotext1"] ;` `.VisualControlRef = this` `return`

This is easy to do; just copy the line from the datalink property of the editor control, and paste it into the `onOpen()` event for a quick head-start, and finish the line off manually. We can now get at our changed MemoText by examining our calculated field’s `VisualControlRef.value`.

A working set of samples is provided with this article; the custom datamodule `MemoText.cdm`, the datamodule for the demo,`MemoTextCDM.dmd` (which uses our custom datamodule as its base class, therefore inheriting all it’s methods), the demo form `MemoTextCDM.wfm`, and a demo report `MemoTextCDM.rep`. The tables are shared for both methods. Examine the code contained in these samples to see how this has all been implemented, and play with the form. You may see subtle differences in the sequence of events compared with the custom control approach, but they both do exactly the same job.

Back to index

### Quick Start

Here is a check-list of the steps in setting up MemoText, using the custom datamodule method:

1. Add the link-field to the “parent” table structure.

2. Ensure that your datamodule is derived from the MemoText Custom Class.

3. Add an onOpen event to the “parent” query if it doesn’t already exist.

4. Add the MemoText calculated field to this `onOpen()` event as follows, substituting as appropriate, the red parts.

| `Function MyQuery_onOpen` `//` `f = new Field()` `f.fieldName = "LogEntryMemoText"` `f.mtLinkField = "LogEntryMT"` `f.mtTableName = "SuppliersLogMTF.dbf"` `f.mtLinkFieldPointer = this.rowset.fields["LogEntryMT"]` `f.beforeGetValue = class::mtBuildMemo` `f.onChange = class::mtOnChange` `this.rowset.fields.add( f )` `//`

5. Add any other MemoText fields in this rowset.

6. Insert a line to call the MemoText `Setup()` method after the last MemoText custom field.

| `this.parent.mtSetup(this.rowset, "SuppCustLogMTF.dbf")`

7. In the “with this rowset” section, add the following two lines of code:

| `canDelete = class::MTCANDELETE` `canSave = class::MTCANSAVE`

8. Open your form in the Form designer. You will be prompted that the MemoText table does not exist. Choose to automatically create a new MemoText table.

9. Drop an Editor control on the form and datalink it to your calculated MemoText field.

10. Create an `onOpen()` event for the editor control, switch to the Source editor, go to the instantiation code for the editor control, copy the `datalink` value and paste it into the `onOpen()` event. Add to it the following suffix to create the required custom property:

| `.VisualControlRef = this`

That’s it!

Back to index

### Converting to a Custom Form

Not everybody uses datamodules, and some people prefer the self-contained approach, setting up their data access within their form code. Having created a custom datamodule it is a remarkably easy job to convert this to a custom form. Only one line of code needs to be changed, from:

| `Class MemoTextDataModule of DATAMODULE custom`

to:

| `Class MemoTextBaseForm of FORM custom`

and save as using a`.cfm` extension. That really is all there is to it! Now you can create a new form based on this custom form, and all the MemoText functionality is sitting there waiting to be used. The `MemoText.cfm` and a sample test /demo form `MemoTextCFM.wfm` is provided with this article. It took less than five minutes in the source editor to take the custom datamodule version of this demo form, and re-jig it to work with the custom base form instead. This versatility and ease of re-use is one of the major strengths of dB2K and the dBL language.

The same principle could be used to produce a custom report, on which to base any report containing MemoText, and Bob’s self contained `URGlist.rep` could be produced in this way too. I have not done this, but if you need it you can produce the base report in under a minute.

Back to index

### Conclusion

This custom control, custom datamodule, and custom form show how easy it is, with dBL and dB2K to “roll your own” solution to a particular problem. It also shows that age-old techniques can be easily used within OOP. Although there is nothing particularly difficult involved in these methods, I hope that they will provide a set of tools, and another option for dBASE developers who use long-length text. I also hope that for the seasoned developer there might be a couple of techniques used here which are worth thinking about.

Which method is best? If you like the visual control concept of the custom control, then use it. If you are happy with the concept of moving this same functionality to a custom datamodule, then use it instead. If you don’t use datamodules, or prefer to keep forms completely self-contained, then use the custom form. There is probably a place for all approaches. This article is no more than a documentary of the development of this functionality, which started with the custom control, dealt with the need to produce reports with MemoText, and then moved the whole lot to a custom datamodule. The custom form approach was an afterthought, when I decided to add MemoText functionality to a pre-existing application which used self-contained forms. This was the quickest method in this instance, as there is less setting-up needed in comparison with the custom control. I do find, however, that with new applications, I seem to choose the custom datamodule approach.

You can download all of the files for this article from the top of this page, and are free to use them as you wish. I will be happy to receive any feedback by e-mail, or by posting in the dBASE Newsgroups. If you make use of this control, or any of this code, an e-mail to that effect would be nice, just to let me know that it has all been worthwhile!

Have Fun!

Acknowledgments

Thanks to Romain Strieff for his help in the dBASE Newsgroups with the syntax required to put multiple lines of code in a codeblock. Thanks to Dan Howard and Bob Rimmington for beta testing, and their comments and suggestions. Thanks to my wife Sarah for proof reading, and putting up with this being written on our flight from Glasgow to Denver. Finally, thanks to David L. Stone for the improvements he brought to this text.

Ronnie MacGregor started developing dBASE IV applications around 15 years ago to help administer his businesses in Scotland. Some of these applications are still in daily use, but are gradually being replaced with 32bit dB2K apps. Although with no formal programming training, and essentially a "hobby" programmer, some of his niche applications are in daily use by other businesses in Britain. He can be contacted at:

Addendum

December 2001:
Thanks to Dr Howard Mintzer for feedback in on using MemoText. Howard found that in his tests with 1,000,000 records, performance degraded beyond an acceptable level. The solution to this was to create a simple ascending index on the `Memo_ID` field in the MemoText table. No other change is required. This fix works on the basis that if a simple index exists on a field, then the BDE will use that index to advantage, even when that index is not explicitly in use. If I remember correctly, it was also Howard who commented that when batch appending large volumes of data to MemoText in a tight programming loop, there could be the very occasional duplication of `Memo_ID` value. I was unable to reproduce this problem, but it was solved by introducing a small time delay into the loop. There have been no reports of duplicated ID values in normal use.

February 2002:
Thanks to Peter Schneider for feedback on the `Generate_ID()` function. He offered a shorter alternative method using the date class :`itoh(new Date().getTime(),8) + str(random()*100,2,2,"0")`

August 2003:
Thanks to Bob Graham for feedback on the custom dataModule version. Bob modified this class to use a rowset instead of a table name, and reports that he has “a datamodule in which four different tables use the MemoText rowset to store their text, and it works just as it did using the table name”.

March 2005:
The latest versions of these controls are available from: www.dBASEdeveloper.co.uk