Recognizing Field Types in MS Access - ΩJr. Software Articles and Products

Problem:

Imagine having a table, which sports a field named - complying to tradition - "POCntPers". What to do, if you need to build a search window, allowing the user to choose any field from any table, to enter a search criterium for that field, followed by an entirely automated search execution and results display? Well, you will run into 2 problems:

• 1. The field name "POCntPers" is not friendly (not descriptive, the user doesn't know what it means);
  
• 2. You don't know whether the field's data type is text or number, which means you don't know whether to quote the search criterium. Misquoting results in errors.

Quoting depends on the field data type. Querying text and memo fields require criterium quotes, number fields require none. Date and time fields sometimes require quotes, and sometimes hashes (#).

Solution:

The solution below provides a way to recognise the field data type.


In your tabledef, assign a description to those field you want to offer for your search screen. Leave the descriptions of the other fields empty.

Then create a search screen having the following properties:

• RowType = ValueList
  
• RowSource = ""
  
• Bound Column = 1
  
• Number of Columns = 3
  
• Limit to List = Yes
  
• Column Width = 0;3cm;0
  

After populating the list (we'll do that below), the field name will live in the screen's first column (invisibly), the friendly description in the 2nd column, and the field data type in the 3rd column (invisibly).


To divine the field type we use the following programming code:
'start counting columns at zero (0)
lngFieldType = cmbSearchFieldChoice.Column(2)


Then you need to recocgnise the data types by their numbers. Here we list a few well-known types:

• dbInt = 3 = Integer
  
• dbLong = 4 = Long Integer (Auto Number)
  
• dbDouble = 7 = Double Integer
  
• dbDate = 8 = Date / Time
  
• dbText = 10 = Text (max. 255 chars)
  
• dbMemo = 12 = Memo (max. 1 GB)
  

Additional data types you can look up using the Object Browser. You can find them in the DAO library, in the DataTypeEnum enumeration.


The last piece of the puzzle is provided by the function below, which populates the choices list with rows of field name, description, and data type.

Public Function strFieldNamesOfTable(strTableName) As String



Dim db As DAO.Database, tdf As DAO.TableDef, rst As DAO.Recordset

Dim fldT As DAO.Field, fldR As DAO.Field

Dim strResult As String



  Set db = Application.CurrentDb

  Set rst = db.OpenRecordset(strTableName, , dbOpenSnapshot)

  Set tdf = db.TableDefs(strTableName)

  

  On Error Resume Next

  For Each fldR In rst.Fields

    Set fldT = tdf.Fields(fldR.Name)

    strResult = strResult & fldT.Name & ";" & _

                fldT.Properties("Description") & _

                ";" & fldT.Type & ";"

  Next



  If (Err.Number = 3720) Or (Err.Number = 91) Then

    '3720 = property missing (no description)

    '91 = object not set (no such field in the tabledef)

    Err.Clear

  End If



  Set tdf = Nothing

  Set rst = Nothing

  Set db = Nothing



  strFieldNamesOfTable = strResult



End Function

And you can load that list into the search screen by doing this:

Private Sub Form_Load()

    Me.cmbSearchFieldChoice.RowSource = _

        strFieldNamesOfTable("tableName")

    'be sure to replace "tableName" with 

    'the desired, existing table name.

End Sub