reading InputMask property of a field won't work (1 Viewer)

S

smig

Registered User.
Local time
Today, 11:09
Joined
Nov 25, 2009
Messages
2,209
I'm saving the structure of my tables into a single table, so I can use it later if I want to change table's and field's settings.

Trying to read the InputMask property of a field raise an error that the field doesn't support this kind of property.
This is also true if I limit the read for a Text fields :confused:

Code: 
.Fields("ColumnInputMask") = tdf.Fields(i).InputMask
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 09:09
Joined
Feb 19, 2013
Messages
16,604
I think you will find the property does not exist until you actually use it. Many properties work this way
 
S

smig

Registered User.
Local time
Today, 11:09
Joined
Nov 25, 2009
Messages
2,209
CJ_London said:
I think you will find the property does not exist until you actually use it. Many properties work this way
Click to expand...
Thanks
That's what I thought
Is there a way to check if it exist ? I don't want to append it to any Text Field I have
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 09:09
Joined
Feb 19, 2013
Messages
16,604
only by managing the error

on error resume next
.Fields("ColumnInputMask") = tdf.Fields(i).InputMask
on error go to 0

if you want to populate your table with something then additional code would be something like

if err<>0 then .Fields("ColumnInputMask") =""

but it seems to me your approach is not ideal. the implication from what you have provided is that you have a column for each property. I would be more inclined to have a table something like

ObjectID
propertyname
propertytype
propertyvalue

propertyvalue would need to be text and then converted as required by propertytype (integer, long, Boolean etc) when creating properties. This structure means you only store properties that exist and can be applied to any object - table, query, field, form, report, control

Have to question why you are doing this - there is the access documenter and if you are doing forms and reports, the saveastext function - see this link https://access-programmers.co.uk/forums/showthread.php?t=99179

and why not just keep a copy?
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:09
Joined
May 7, 2009
Messages
19,229
try to add this function to your collection of codes:
Code: 
Function FieldTypeName(fld As DAO.Field) As String
    'Purpose: Converts the numeric results of DAO Field.Type to text.
    Dim strReturn As String    'Name to return

    Select Case CLng(fld.type) 'fld.Type is Integer, but constants are Long.
        Case dbBoolean: strReturn = "Yes/No"            ' 1
        Case dbByte: strReturn = "Byte"                 ' 2
        Case dbInteger: strReturn = "Integer"           ' 3
        Case dbLong                                     ' 4
            If (fld.Attributes And dbAutoIncrField) = 0& Then
                strReturn = "Long Integer"
            Else
                strReturn = "AutoNumber"
            End If
        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 (no interface)
        Case dbText                                     '10
            If (fld.Attributes And dbFixedField) = 0& Then
                strReturn = "Text"
            Else
                strReturn = "Text (fixed width)"        '(no interface)
            End If
        Case dbLongBinary: strReturn = "OLE Object"     '11
        Case dbMemo                                     '12
            If (fld.Attributes And dbHyperlinkField) = 0& Then
                strReturn = "Memo"
            Else
                strReturn = "Hyperlink"
            End If
        Case dbGUID: strReturn = "GUID"                 '15

        'Attached tables only: cannot create these in JET.
        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 = "Float"               '21
        Case dbTime: strReturn = "Time"                 '22
        Case dbTimeStamp: strReturn = "Time Stamp"      '23

        'Constants for complex types don't work prior to Access 2007 and later.
        Case 101&: strReturn = "Attachment"         'dbAttachment
        Case 102&: strReturn = "Complex Byte"       'dbComplexByte
        Case 103&: strReturn = "Complex Integer"    'dbComplexInteger
        Case 104&: strReturn = "Complex Long"       'dbComplexLong
        Case 105&: strReturn = "Complex Single"     'dbComplexSingle
        Case 106&: strReturn = "Complex Double"     'dbComplexDouble
        Case 107&: strReturn = "Complex GUID"       'dbComplexGUID
        Case 108&: strReturn = "Complex Decimal"    'dbComplexDecimal
        Case 109&: strReturn = "Complex Text"       'dbComplexText
        Case Else: strReturn = "Field type " & fld.type & " unknown"
    End Select

    FieldTypeName = strReturn
End Function
now to use it (some fieldtype dont have this InputMask property) check each field.type:

Code: 
...
...
    sType = FieldTypeName(tdf.Fields(i))
    Select Case True
    ' ignore this fieldtypes
    Case InStr(sType, "Complex") <> 0
    Case InStr(sType, "Unknown") <> 0
    Case sType = "AutoNumber"
    Case sType = "Binary"
    Case sType = "OLE Object"
    Case sType = "Hyperlink"
    Case sType = "GUID"
    Case sType = "varBinary"
    Case sType = "Attachment"
    
    Case Else
        ' if there is no input mask property error will occur
        On Error Resume Next
            sInputMask = tdf.Fields(i).Properties("InputMask")
        If Err.Number <> 0 Then
            sInputMask = ""
        End If
        .Fields("ColumnInputMask") = sInputMask
        On Error GoTo 0
        
    End Select
...
...
 
S

smig

Registered User.
Local time
Today, 11:09
Joined
Nov 25, 2009
Messages
2,209
Thanks for your help :)
I hoped not to use #on error resume next... # but guess this is the easiest way to go.

I store all table's structure in a big table that include columns for FieldName, FieldOrdinalPosition, FieldType, FieldSize, FieldPK, FieldMask....

Why do I do that?
A good question :D
This is a long running application.
From time to time I find myself in need to change things in the BE tables.
I do it using this table + another table to add, rename, change or drop Tables and Columns.
This table is used to Change columns, simply by writing the new value of what I want to be changed, and it will be changed on next update of the app FE
This is working perfect. Only problem I had is reading the InputMask (yes, I already know some properties only exist after they are set)
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 09:09
Joined
Feb 19, 2013
Messages
16,604
so far as I know there is no other way.
 
You must log in or register to reply here.

Similar threads

MajP
DAO DataUpdatable not Expected or Not Understanding
2
Replies
21
Views
568
MajP
MajP
K
  • Solved
Solved List of methods of a class in intellisense
2
Replies
20
Views
924
KitaYama
K
W
Unlock locked form fields with VBA
Replies
6
Views
427
Edgar_
E
P
Solved Access vba Manage an Attachment field with vba
Replies
8
Views
410
Pat Hartman
P
C
filtering subreport from subform
Replies
4
Views
485
ClaraBarton
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom