This will find the data type of the actual field to which a control is
finally bound. Say for example that the form is bound to the following
query:
SELECT MYFIELD AS F1 FROM MYTABLE
If your form was based on that query, and a control was bound to field
F1 in that query, the following code would find the actual field
(MYFIELD), in the actual table (MYTABLE), and return the data type of
that.
The data types are returned as dbXXX constants; eg. 10 = dbText = a
text field.
Dim sTable As String, sField As String
Dim db As Database, fld As Field, o As Object
sTable = Me.RecordSource
sField = Me![txtName].ControlSource ' << uses control txtName for
example.
Set db = CurrentDb()
On Error Resume Next
Set o = db.TableDefs(sTable)
If Err.Number = 0 Then
' record source is a table.
On Error GoTo 0
Set fld = o.Fields(sField)
Else
' not a table - must be a query.
On Error GoTo 0
Set o = db.QueryDefs(sTable)
With o.Fields(sField)
sTable = .SourceTable
sField = .SourceField
End With
Set fld = db.TableDefs(sTable).Fields(sField)
End If
MsgBox "table [" & sTable & "] Field [" & sField & "] data type = " &
fld.Type
Set db = Nothing
HTH,
TC (MVP Access)
http://tc2.atspace.com