We have a variety of codes sets in a Code table which are used at various points for: invoice status, Yes/No, etc. We use Miriam Liskin's approach in her "Expert Solutions" to populate combo boxes for user review and selection. The value in underlying tables is an integer value representing the code.

We've recently developed a Quality module. The Quality issue type (shipping, installation, incorrect order, etc.) is something that will grow over time. There's no sequence implied between various issue types. It seemed useful to provide an alphabetical list for the user to select from rather than the sequence ordered by code number that we've used to this point.

In one instance, I successfully used the following technique on a combo box without a control source.

Here is the original Combo INIT code from Miriam:
The RowSourceType property is 0 -- which uses the AddListItem and AddItem methods to add values at runtime.

*-- acboiCodeListBox.Init()

LOCAL lcCursor, ;
lcOldAlias

lcOldAlias = ALIAS()
lcCursor = SYS(3)

SELECT codes.cDescription ;
FROM codes ;
WHERE UPPER(codes.cCodeType) = UPPER(this.CodeType) ;
ORDER BY codes.cCode ;
INTO CURSOR lcCursor

SCAN
this.AddListItem(cDescription, RECNO(), 1)
ENDSCAN

USE
IF !EMPTY(lcOldAlias)
SELECT (lcOldAlias)
ENDIF

I modified this as follows:

SELECT codes.cDescription, VAL (codes.cCode) AS nCode;
FROM codes ;
WHERE UPPER(codes.cCodeType) = UPPER(this.CodeType) ;
ORDER BY codes.cCode ;
INTO CURSOR lcCursor

SCAN
this.AddListItem(lcCursor.cDescription, lcCursor.nCode, 1)
ENDSCAN

If the Sorted property is .T., the list appears in sorted order. If Sorted is .F., the list appears in nCode order.

In any case, I can set default values and access chosen values using the ListItemID property, as follows:
ThisForm.iIssueType = ThisForm.AcbocliIssueType.ListItemID
This returns the desired nCode value which remains constant for a specific code (not the sequence in the list which changes depending on whether the list is sorted or not).

However, when I attempt to move this to a bound control, the mechanism above doesn't seem to map.

1. The format above doesn't provide a method to map the ListItemID to a control souce -- there is only one column to bind.

2. When I add the nCode as a 2nd column to the query, the AddListItem for that column doesn't work because a character value is required for the data type.

3. I can't use a character value for the 2nd column because the underlying tables use a numeric for the code number.

4. When I change RowSourceType to 3 -- SQL Statement, return 2 columns (cDescription, nCode), set the bound column to 2 and make other modifications (add a SortByCode property and change the ORDER on the query based on which columnn to sort) the query sorted by numeric code works fine. However, there doesn't seem to be any binding when the query is ordered by cDescription. The numeric value returned is rather the sequence in the query.


Any thoughts on how I can bind the nCode value to a table column?