Those of us who have ever programmed FoxPro DOS applications, are marveled and at the same time overwhelmed with the possibilities of enhancement in the appearance, the performance and the different ways of doing the things that were difficult, or outright impossible to do in DOS, that we observe in Visual Fox Pro.
The concept of "object", which enables us to modify the appearance or the behaviour of a screen, control or basic routine, by just a few clicks of the mouse, or by the addition of a few lines of code in predetermined "boxes" of the object (i.e. method code responding to specific events), renders per se spectacular advantages, but also presents problems hitherto unknown or even unimagined.
We are used to programming in an up / down direction and from beginning to end, and when faced to the possibility of "jumping" from one object to another, from one form to another or from one control to another, we find it difficult to understand that our coding universe has been greatly reduced to the need to just put specific functionality for a certain behaviour in a predetermined place.
As an example, in DOS we had to use a big number of lines of code just to make a simple window, called by selecting an option from a radio button, move from one side to another on the screen, presenting a selection list with some data, with a browse inside and make it disappear from view, placing the chosen value in the correct position on our data entry screen.
However, with VFP this is possible with very little effort. We only have to concentrate on making the pick list code perform the desired functionality, but we do not have to worry on the appearance or handling of all the graphic elements. VFP does it for us with just a few clicks, and it does it very well indeed.
But for new VFP programmers, or even for those more experienced ones, there are some basic alternatives for doing certain things, using some of the VFP controls, which are not always apparent. Some controls are more "difficult" to use than others, or have functionalities that are not wholly understood by everybody.
And the worse is, when we try to create a reusable class to employ the "difficult" control, we are more baffled because we do not understand fully how the "black box" works. Then, our reaction is to discard the control altogether and thus, we loose something that could improve substantially the interface, our productivity and our cost / benefit relationship when we start a project.
A pick list using a grid
One of the "difficult" to use controls is the grid. It is considered difficult because it is a very complex container control. A grid contains columns, which in turn contain special labels (headers), textboxes and can contain other controls, such as labels, command buttons, check boxes, images, etc.
Likewise, each one of the controls contained in the grid's columns has its own methods and events that we must know.
In some specialized books, the authors discourage the use of grids because they consider them to be not very flexible, complicated, and burdensome in terms of resource consumption. They simply do not like them. Other authors promote the use of grids wherever possible, because they consider them to be the maximum expression of the old and dear BROWSE, and advise us to completely replace the browse with grids.
We prefer to apply a "salomonic" criterion and use grids or not, depending on the occasion and the specific need of the application or routine. And also depending on the particular preferences of the VFP programmer. The important thing is to know that grids exist, that they can be used and they are not as difficult as it seems.
In the following example, we shall present a grid to be used as a tool for the selection of records from a given table.
In order to make the example clearer and more understandable, we are first going to present a form developed for a particular application, with all its code geared to the specific needs of such application, but not altogether reusable.
In a further note we shall modify the example, presenting it as a more general, reusable class.
In the initial example we shall use a specific table, the customer table, to choose a given customer.
In the following note we shall present the same solution, but in the form of a reusable class, which could be used with any table.
Hands on
Let us create a form to choose a customer from a customer list, by using a grid.
The structure of the customer table is, as a minimum, as follows:
Account
C
4
Company
30
We create a form which will be used to show the customer list, and name it PICK_CUST
The form has the following properties:
Borderstyle
2-fixed dialog
Caption
Customer list
ControlBox
.f.
DataSession
2 Private (VERY IMPORTANT)
Height
391
KeyPreview
.t.
Left
0
ShowWindow
1- IN TOP-LEVEL FORM
Top
Width
435
WindowType
1 –modal
We add two new properties:
We put a grid and a edit box on the form with the following properties:
ColumnCount
2
DeleteMark
FontBold
200
25
Name
grdCustomers
ReadOnly
RecordSource
Customers
RecordSourceType
1- Alias
ScrollBars
2 – vertical
SplitBar
14
385
Grid’s column 1 properties:
Alignment
1 –middle right
ControlSource
Customers.account
clmAccount
Grid’s column 2 properties:
Customers.company
clmCompany
.t
278
Edit box’s properties:
Thisform.mess
DisabledBackColor
251,248,130 (soft yellow)
111
36
0 – none
258
362
In the form’s data environment we place the customers table. For purposes of this example, two indexes were created for the table: cust_num, indexing the data by account number, type character , and cust_comp, sorting records by company, also type regular.
Code for each applicable method on the form is shown below:
Activate event
ThisForm.grdCustomers.clmAccount.setfocus
Keypress event
LPARAMETERS nKeyCode, nShiftAltCtrl if nKeyCode = 1 && home key go top endif if nKeyCode = 6 && end key go bottom endif
Load event
This.mess = ‘Press enter or double-click on the customer number or company name of any customer to select it’ + chr(13) + chr(13) This.mess = this.mess + ‘Home / end keys = go top or bottom of list’ + chr(13) + chr(13) This.mess = this.mess + ‘Click on column headers to change sort order’ this.acct = space(4)
Unload event
return this.acct
The following code is for the grid’s events:
grdCustomers.clmAccount.header1.click
set order to cust_num this.parent.parent.refresh
grdCustomers.clmCompany.header1.click
set order to cust_comp this.parent.parent.refresh
grdCustomers.clmAccount.text1.DblClick
This.keypress(13)
grdCustomers.clmAccount.text1.KeyPress
LPARAMETERS nKeyCode, nShiftAltCtrl if nKeyCode = 13 && enter key thisform.acct = customers.account thisform.release endif
grdCustomers.clmCompany.text1.DblClick
This.Parent.Parent.clmAccount.Text1.keypress(13)
grdCustomers.clmCompany.text1.KeyPress
LPARAMETERS nKeyCode, nShiftAltCtrl if nKeyCode = 13 This.Parent.Parent.clmAccount.Text1.keypress(13) Endif
How does it work?
To test our picklist we create a new form.
Figure 1: The test form
On this form’s data environment, we place the CUSTOMERS table, drag the company field to the form’s surface, so we end up with a textbox with customers.company as the control source.
The DataSession property may remain as 1 – Default data session or 2 – Private data session, as the need may be.
We add a new property, kacct and initialize it in the form’s init event, like so:
this.kacct = space(4)
We add a command button, with the following code in the click event:
do form pick_cust to thisform.kacct select customers =seek(thisform.kacct,'customers','cust_num') thisform.refresh
When running the form, a click on the command button causes the picklist form to be shown.
In this form, we can navigate the grid either with the arrow keys or the mouse.
If we doubleclick or press the enter key on any row, the form is destroyed, but before doing that, it assigns the chose account number to the KACCT property.
The Unload Event then returns this code, which is captured by the KACCT property in the TEST form.
The button’s click event code uses this property to search in the table for the requested record, which is then shown in the textbox.
Figure 2: The pick list
Remarks:
We had set the KeyPreview property in the pick_cust form to .T. (true). This property captures the keypress event occurring on the form. In our case, when we press the HOME or END keys, the Keypress event code, on the form, is executed before the keypress event in the textboxes of the columns on the grid. The effect is a jump to either the first or last record.
When we press the enter key on any row on the grid, or when we double click on the row, the code on those events sends all the action to the keypress event on the column1 textbox on the grid.
As shown, the parameter setn to such event is chr(13), i.e. ENTER.
Because the keypress event of the textbox on column1 (name: clmAccount), captures the enter key keystroke, it executes the code, which does two things:
It assigns the customer account number taken from the table to the KACCT property, and closes the form, with a call to the RELEASE method.
The code in the UNLOAD event on the form, returns the value of the KACCT property, in this case the customer account number, which is then captured by the command button on the TEST form.
This code then searches the corresponding record in its customer table and refreshes the textbox.
On the other hand, the pick_cust form should be MODAL, so the TO clause can be used in the command button, otherwise it gives an error.
Return more than one value
In the above example we only need one value returned by this form, in our case the account number.
But if we needed more than one value, the above technique is no good. The UNLOAD EVENT can only return ONE VALUE, in this case, the KACCT property.
If we wanted to capture the account number and the company name, we would have to use another technique.
We add a new property to our picklist, called KOMPANY
Then we modify the keypress event code on the first column of the grid like so: grdCustomers.clmAccount.text1.KeyPress
LPARAMETERS nKeyCode, nShiftAltCtrl if nKeyCode = 13 && enter key thisform.acct = customers.account thisform.kompany = customers.company thisform.hide endif
We do not put any code on the unload event because no code is going to be returned.
In the command button calling this form, on the TEST form, we place the following code in the click event:
(we also need to create a new property on the TEST form, the kompany property)
DO FORM pick_cust NAME oCust LINKED Thisform.acct = oCust.acct Thisform.kompany = oCust.kompany oCust.release
We notice a couple of things here:
Why ?
If we close the form calling its release method, we get an error, saying oCust is not an object. This happens because the oCust object is destroyed before we can read its properties in the click method of the command button calling that form.
If we just hide it, calling its HIDE method, the oCust object remains in memory, we can read its properties and then we destroy it calling the object’s RELEASE method.
We must use the LINKED keyword to link the object with the form, so that when the object is destroyed, the form is destroyed as well.
In the above manner, it is possible to return an indefinite number of values from a called form.
Conclusion
As shown, the solution to the problem does not require too much sofistication. We have used a grid to present the information in an obvious and apparent manner: a field with the customer account number and another field with the company name.
We used the click event method of the header object inside each column, to sort records by both index keys.
We used the keypress and doubleclick event methods to make our selection, by the use of the enter key or a double click of the mouse.
Likewise, we used those methods to hide the pick list form from view.
And finally, we showed an example of a technique to return more than one value from a form.
Each one of these actions only required a few lines of code in some specific methods.
We stress the fact that the example presented here was designed on purpose with the limitation that it can only have one specific use, in this case the selection of a certain customer. The objective was to show an easy way to use a grid.
In a further example, we shall show the way to make a more general pick list, by the creation of a reusable class, which, by the acceptance of a number of parameters, could be used to choose records from any table.