Property not found causing error 1

[vba317]

I am using access 2003. I am reading properties from a table which is not a problem as long as a field is populated. The problem I am running into is if the field is not populated an error is caused and my routine ends. I have tried to use isnull, <> or "" with no luck. If I hover over dBase.TableDefs(lTbl).Fields(lFld).Properties("InputMask") I get property not found. Any help would be appreciated.

With goXL.ActiveSheet
                'Data Type property
                .Range("C" & lRow) = "Text"
                'Size property
                .Range("D" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Size
                'Format property
                .Range("E" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Properties("Format")
'                Input Mask property
                If dBase.TableDefs(lTbl).Fields(lFld).Properties("InputMask") = "" Then
                .Range("F" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Properties("InputMask")
                End If
                'Caption property
                .Range("G" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Properties("Caption")
                'Default Value property
                .Range("H" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).DefaultValue
                'Required property
                    If .Range("I" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Required = False Then
                       .Range("I" & lRow) = "Yes"
                Else
                        .Range("I" & lRow) = "No"
                    End If
                'Allow Zero Length property
                    If .Range("J" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).AllowZeroLength = False Then
                        .Range("J" & lRow) = "Yes"
                Else
                        .Range("J" & lRow) = "No"
                    End If

 

[PHV]

Replace this:
If dBase.TableDefs(lTbl).Fields(lFld).Properties("InputMask") = "" Then
.Range("F" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Properties("InputMask")
End If
with this:
On Error Resume Next
.Range("F" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Properties("InputMask")
On Error GoTo 0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

As I pointed out in thread705-1696279, that property won't exist if it has not been set.

You may need something like

Dim IM_Value As Variant
On Error Resume Next
IM_Value = dBase.TableDefs(lTbl).Fields(lFld).Properties("InputMask")
If Err.Number = 0 Then
    .Range("F" & lRow) = IM_Value
Else
    .Range("F" & lRow) = "Not Set"
    Err.Clear
End If
[red]Resume normal error trapping here.[/red]

 

[vba317]

Golom, Sorry for the oversight, I will be more careful in the future.

Tom

 

[Golom]

Don't worry about it.

When you're trying to wrap your head around the inner mysteries of DAO and SQL you WILL make mistakes. Just try not to make the same ones too often.

 

[vba317]

I reread both threads and I must say that I am still having a hard time understanding everything. I thought my code was working but alas it is not for the input mask. The current condition is no input mask in the table. I found out that originally when the code is looping through err.Number is 0. When the offending line is executed I get an error 3270 but the problem is in order to get the error I have to pass the line of code that I am interested in executing. I have come up with a solution, it's not pretty but it works. Is there a better way of doing this?

Tom

If Err.Number = 0 Then
                  Debug.Print Err.Number
                  .Range("E" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Properties("InputMask")
                  If Err.Number = 3270 Then
                  .Range("E" & lRow) = "not set"
                  End If
                  Err.Clear
                Else

 

[Golom]

Essentially the equivalent of the code I posted. Did you look at it?

The only difference is that I used another variable (IM_Value) to pick up the returned value. If there was no error then I used the value in IM_Value. If there was an error then I supplied "Not Set" as the value to use. There are other ways of doing this without using error handling (such as iterating through the properties collection) but they are more wordy and this works just as well.

Seems like you are getting a glimmer even if full guruness has not yet been achieved.