Access VBA object types

Question

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?

"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
– HansUp, Commented Dec 18, 2013 at 17:53
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
– Johnny Bones, Commented Dec 18, 2013 at 18:52

Chris Rolliston · Accepted Answer · 2013-12-18 19:14:18Z

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

answered Dec 18, 2013 at 19:14

Chris Rolliston

4,8181 gold badge19 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MBY Over a year ago

TypeOf ... Is does seem to work. Helpful and clever -- "WishVBAHadOverloads"! 8^)

Collectives™ on Stack Overflow

Access VBA object types

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related