1

I am trying to build a procedure in my Access project that will accept either a table or a form as part of the argument. If the object is a table then it should check the table's fields; if it's a form then it should it the form's controls.

I've tried using either variant or object variable types for the argument. But either way when I check the VarType() the passed variable is of type object, so I can't distinguish between them. (I'm currently passing a table as a TableDef object by the way.)

I would try overloading the function based on parameter type but apparently that isn't available in VBA (as far as I know from Function Overloading and UDF in Excel VBA. Any suggestions?

Community
  • 1
  • 1
MBY
  • 113
  • 1
  • 1
  • 6
  • 3
    *"either a table or a form as part of the argument"* How do you pass a table to your procedure? As a string for the table name? As a `TableDef` or recordset object? – HansUp Dec 18 '13 at 17:53
  • 1
    Try `TypeName()` instead – Tim Williams Dec 18 '13 at 17:55
  • You can look it up in MSysObjects and use an If/Then statement to run the correct procedure. Forms have a MSysObjects.Type=-32768, and Tables have a MSysObjects.Type=1, 4 or 6 (depending on if they're local or linked). – Johnny Bones Dec 18 '13 at 18:52

1 Answers1

2

To test for a certain class in VBA, use a combination of the TypeOf and Is operators:

Sub WishVBAHadOverloads(ByVal Obj As Object)
  If TypeOf Obj Is TableDef Then 
    Dim Def As TableDef
    Set Def = Obj
    ' work with Def... 
    Exit Sub
  End If
  If TypeOf Obj Is Form Then 
    Dim Frm As Form
    Set Frm = Obj
    ' work with Frm... 
    Exit Sub
  End If
  Err.Raise 999, "WishVBAHadOverloads", "Bad argument type - expected a TableDef or Form"
End Sub
Chris Rolliston
  • 4,788
  • 1
  • 16
  • 20
  • TypeOf ... Is does seem to work. Helpful and clever -- "WishVBAHadOverloads"! 8^) – MBY Dec 18 '13 at 19:50