Can you tell the datatype of a control

[SJM]

Howdy,
I have a form with a number of different text box controls on it displaying
data for a relevant record that the user has selected. I want to run a
filter/search function that runs off the control that the user has last
clicked on. I have already built it and it works well in most circumstances
but not 100%. For it to be bullet proof I need to be able to determine the
data type of the field the user has clicked on. While I can look at the
first character to see if it is alpha or numeric, this does not work in all
cases, especially when dealing with say product codes for instance. Is there
a way using VBA to determine what the data type of a form control is?

[Allen Browne]

Look at the ControlSource to get the name of the field it is bound to.

Then examine the Type of the field in the RecordsetClone of the form.
You'll need error checking, but this is the basic idea:
strField = Form.ActiveControl.ControlSource
Debug.Print Me.RecordsetClone.Fields(strField).Type

The function below converts the DataTypeEnum value to English:

Function FieldTypeName(n As Long) As String
'Purpose: Converts the numeric results of DAO fieldtype to text.
'Note: fld.Type is Integer, but the constants are Long.
Dim strReturn As String 'Name to return

Select Case n
Case dbBoolean: strReturn = "Yes/No" '1
Case dbByte: strReturn = "Byte" '2
Case dbInteger: strReturn = "Integer" '3
Case dbLong: strReturn = "Long Integer" '4
Case dbCurrency: strReturn = "Currency" '5
Case dbSingle: strReturn = "Single" '6
Case dbDouble: strReturn = "Double" '7
Case dbDate: strReturn = "Date/Time" '8
Case dbBinary: strReturn = "Binary" '9
Case dbText: strReturn = "Text" '10
Case dbLongBinary: strReturn = "OLE Object" '11
Case dbMemo: strReturn = "Memo" '12
Case dbGUID: strReturn = "GUID" '15
Case dbBigInt: strReturn = "Big Integer" '16
Case dbVarBinary: strReturn = "VarBinary" '17
Case dbChar: strReturn = "Char" '18
Case dbNumeric: strReturn = "Numeric" '19
Case dbDecimal: strReturn = "Decimal" '20
Case dbFloat: strReturn = dbFloat '21
Case dbTime: strReturn = "Time" '22
Case dbTimeStamp: strReturn = "Time Stamp" '23
Case Else: strReturn = "Field type " & n & "unknown"
End Select

FieldTypeName = strReturn
End Function
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SJM" <ak*****@ozemail.com.au> wrote in message
news:ZR****************@nnrp1.ozemail.com.au...

Howdy,
I have a form with a number of different text box controls on it
displaying
data for a relevant record that the user has selected. I want to run a
filter/search function that runs off the control that the user has last
clicked on. I have already built it and it works well in most
circumstances
but not 100%. For it to be bullet proof I need to be able to determine the
data type of the field the user has clicked on. While I can look at the
first character to see if it is alpha or numeric, this does not work in
all
cases, especially when dealing with say product codes for instance. Is
there
a way using VBA to determine what the data type of a form control is?

[SJM]

Thanks Allen for speedy response
Wont test it out till tomorrow but it looks like it will solve my dilemma
Neat!