I have had some requests, to use ENUM descriptions through VB. Unfortunately, due to the fact that they are binary objects, they do not come through correctly, using DAO or RDO (or ODBC). As a result, you must use ADO to get the objects. Even then, the enum description is not stored in the binary object. Thus, to get the enum description is rather complex. It involves the following;

a. determine the package combination of the current user
b. find the highest level VRC of that package combination
c. find the highest version of the table in that VRC level to find the domain used by the particular field (this is required, in case the table is customised and the domain of this field is changed)
d. find the highest version of the domain in that VRC level to find the description of the particular enum value (this is required, in case the domain is customised and the description has been changed)


Create a new project and ensure you have ADO activated for your project. To do this, from the menu bar, choose;
Project, References
Ensure 'Microsoft ActiveX Data Objects 2.5 Library' is selected
Add a module to your project and copy and paste the code from the attached text file 'ENUM_CD.TXT' into this module. You can then call this function with something like;
MsgBox FindItemType(<item code>)

If you want to find the enum description of a different table field, then simply plagerise this code and create your own function, similar to the FindItemType function (ensure that you call the 'PreChecksOkay' function the first time you run this, to set the global settings, such as user name, company, package combination, etc. Although, this code calls the PreChecksOkay function each time it runs, this function only needs to be called the first time this code is run. Therefore, you may wish to call this function in the Load event of your form and then not worry about calling it, when you get the enum descriptions).


NOTE: this example code assumes, that you have the tools tables and your company data tables, in the same database of your SQL server. If this is not the case, then you will need to create 2 connection objects. Also beware, this code will automatically connect to your SQL server, when activated, but will not disconnect. Ensure you disconnect (or close) the ADODB connection object either at the end of the 'FindItemType' function or when you close down your app. If you disconnect at the end of the FindItemType function, then ensure that you call the PreChecksOkay function each time, to re-establish the connection.

Tip...

If you are adding the enum as a field in a recordsource (eg: you have a db control and db grid based on the db control, then change the recordsource of the db control), you can use the 'CAST' and 'CASE' statements to display the enum descriptions on your grid. The disadvantage with this trick, is that you need to hard code the descriptions of your domains, which means, if you change your domain, you need to change your VB code.

To do this, use something like;

dbgrid.RecordSource = "SELECT [ttiitm001100].[t_item] 'Item Code', " & _
"[ttiitm001100].[t_dsca] 'Description', " & _
"CASE CAST([ttiitm001100].[t_kitm] AS INT)" & _
"WHEN 0 THEN 'Empty' " & _
"WHEN 1 THEN 'Purchased' " & _
"WHEN 2 THEN 'Manufactured' " & _
"WHEN 3 THEN 'Generic' " & _
"WHEN 4 THEN 'Cost' " & _
"WHEN 5 THEN 'Service' " & _
"WHEN 6 THEN 'Subcontracting' " & _
"END AS 'Item Type' " & _
"FROM [ttiitm001100]"
dbgrid.Refresh

Maybe you can help me on this. I got this ASP code but as you can see, I have problem with the enum field as well, tiitm001.kitm.

I havent explored recordsource all that well yet but maybe you have a quick fix. The code below works except for the enum field. How do I combine recordset and recordsource?

My output eg. objitm, objdsca etc goes into formatted html document.

The "asp sign here" means the symbol for ASP. Reason why I didnt use the actual sign is because Baanboard hides the code below as if it's actual ASP code.

--asp sign here---

Dim SQLString

SQLString = "SELECT Item.t_item,Item.t_dsca,Item.t_citg, " & _
"Item.t_stoc FROM ttiitm001500 As Item"

SQLString = SQLString & " where Item.t_item >=" & _
Chr(39) & Request.Form.Item("item.f") & Chr(39) & " and Item.t_item <=" & _
Chr(39) & Request.Form.Item("item.t") & Chr(39)

Set DatabaseConnection = Server.CreateObject("ADODB.Connection")

DatabaseConnection.Open "Provider=sqloledb;Data Source=servername;Initial Catalog=baandb;User Id=sa;Password=;"

Set UserRecordset = Server.CreateObject("ADODB.Recordset")

UserRecordset.Open SQLString, DatabaseConnection

Set objitem = UserRecordset("t_item")
Set objdsca = UserRecordset("t_dsca")
'Set objkitm = UserRecordset("t_kitm") ---this field is not working
Set objcitg = UserRecordset("t_citg")
Set objstoc = UserRecordset("t_stoc")


If UserRecordset.EOF Then

--asp sign here--

Originally posted by benito
The "asp sign here" means the symbol for ASP. Reason why I didnt use the actual sign is because Baanboard hides the code below as if it's actual ASP code.

--asp sign here---
Benito, what do you mean by 'hiding code?' Baanboard does not hide code, you merely have the option to reformat the code using the Enscript integration (see this thread (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=5452)) The formatting only happens when you use the right 'CODE' tags however the admins and moderators scan through the posts regularly and insert these tags to improve readability.

I wanted to write the asp symbol "<%" without the quotes and enclosed with "percent sign" and "greater than sign" . (Note: I have to spell it out so it shows). The codes in between disappears. I tried enclosing it with "code" tags but it still wouldn't show up.

How should I do it? Thanks.

When Patrick said that Baanboard does not hide code, he was correct - but he forgot to mention "except in Code&Utilities forum" :). In this forum HTML tags are legal. I am going to attempt your tags below:

&lt;%&gt;

And here is the code that I wrote to get that stuff:

&amp;lt;%&amp;gt;

May be Patrick will look into turning HTML off for this forum as well. We only use it in the Index thread to get tables.

Benito, Vamsi,

I forgot that. We will look into the 'enable HTML tag' again.

benito,

I am not familiar with ASP, but it looks similar to VB. If this is the case, then there shouldn't be to much of a problem, using the 'CAST' and 'CASE' statements in the 'SELECT' part of your SQLString. Since you are creating an ADO recordset, then this select should still work. NOTE: you are not actually selecting the kitm field in your example. But something like the following should still work (unless there is something in ASP that I'm not aware of, that doesn't allow interrogation of binary fields), eg:

SQLString = "SELECT Item.t_item,Item.t_dsca,Item.t_citg, " & _
"Item.t_stoc, " & _
"CASE CAST(Item.t_kitm AS INT)" & _
"WHEN 0 THEN 'Empty' " & _
"WHEN 1 THEN 'Purchased' " & _
"WHEN 2 THEN 'Manufactured' " & _
"WHEN 3 THEN 'Generic' " & _
"WHEN 4 THEN 'Cost' " & _
"WHEN 5 THEN 'Service' " & _
"WHEN 6 THEN 'Subcontracting' " & _
"END AS 't_kitm' " & _
"FROM ttiitm001500 As Item"

hope this helps...

hey mate!
it worked! you're brilliant. i'd never have thought of this solution. i attached the complete script. thanks a lot.