find if field is AutoNumber type or not

[DougP]

I have this code, but the Long Integer and the AutoNumber are the same field type of “3”. Is there a way to differentiate between the two some other way?

Function FieldType(intType As Integer) As String

    Select Case intType
        Case 202
            FieldType = "Text"
        Case 203
            FieldType = "Memo"
        Case 2
            FieldType = "Integer"
        Case 3
            FieldType = "Long Integer"
        Case 6
            FieldType = "Currency"
        Case 4
            FieldType = "Single"
        Case 5
            FieldType = "Double"
        Case 7
            FieldType = "Date"
        Case 205
            FieldType = "Ole Object"
        Case 17
            FieldType = "Byte"
        Case 3
            FieldType = "AutoNumber"
        Case 11
            FieldType = "Yes/No"

    End Select

End Function

DougP, MCP, A+

 

[RoyVidar]

I don't know how you're getting the type, but if you could address for instance the field properties (in this sample retrieved thru ADOX), then you should be able to fetch the Autoincrement property:

[tt]dim oCat as adox.catalog
set oCat=new adox.catalog
oCat.activeconnection=currentproject.connection ' or you conn
msgbox oCat.tables("yourtable").columns("yourcolumn").properties("autoincrement")[/tt]

- would need a reference to ADOX

Roy-Vidar

 

[jimlee]

Doug, anyone!

How did you get 'intType' in the above code? Is there a way of finding out the Type by just looking at the fields on a form or do you have to look at the recordset?


jimlad

 

[RoyVidar]

A field in a recordset would expose the .Type property, but I don't think the thingies on forms (controls?) do:

[tt]debug.print rs.fields("somefield").type[/tt]

I think DougP has retrieved the type property from an ADO recordset/field.

Roy-Vidar

 

[jimlee]

Ah, just as I suspected, i'm trying to create a generic 'multi' filter function which I could use for any form with any amount of filters on without changing the function. Suppose i'll have to go look at the recordset. Nothings ever that simple is it!


jimlad

 

[VBslammer]

If the field type returns "Long Integer" you can then check to see if it's an autonumber:

Function IsAutoNumber(ByRef fld As DAO.Field) As Boolean
On Error GoTo ErrHandler

  IsAutoNumber = (fld.Attributes And dbAutoIncrField)

ExitHere:
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

VBSlammer

Unemployed in Houston, Texas

 

[VBslammer]

You'd probably want to be able to handle each of the different field types in the function, and since vb won't let you overload functions, you'll have to change the parameter to an object and check its type in the function:

Function IsAutoNumber(ByRef fld As Object) As Boolean
On Error GoTo ErrHandler

  If TypeOf fld Is ADODB.Field Then
    IsAutoNumber = (fld.Properties("ISAUTOINCREMENT") = True)
  ElseIf TypeOf fld Is DAO.Field Then
    IsAutoNumber = (fld.Attributes And dbAutoIncrField)
  Else
    Err.Raise vbObjectError + 100, "IsAutoNumber()", _
      "Unsupported Field Type argument: " & TypeName(fld)
  End If

ExitHere:
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

VBSlammer

Unemployed in Houston, Texas