1

I would like to pass the type of MS Access objects to a function. From an earlier question I know that you can test for TableDef, form, QueryDef, etc. types using TypeOf... is. But I do not know what the types of the results are and how to pass them to a new function that doesn't have access to the original object.

I'd like to be able to pass something like CStr(TypeOf(object)) but TypeOf doesn't seem to be able to be called outside of the TypeOf... is statement.

I guess I could create a helper function that tests for object types I'm interested in and returns a string or enum. But if someone knows how to pass the object type, that would be a lot simpler!

Community
  • 1
  • 1
MBY
  • 113
  • 1
  • 1
  • 6

2 Answers2

2

I'm unsure exactly what you want, but wonder whether TypeName() might be useful.

Here are a few examples from an Immediate window session:

? TypeName(CurrentDb.TableDefs("tblFoo"))
TableDef

? TypeName(CurrentDb.QueryDefs("qryFoo"))
QueryDef

DoCmd.OpenForm "Form3"
? TypeName(Forms!Form3)
Form_Form3

? TypeName(Forms!Form3!cboUserId)
ComboBox
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • That does seem promising. But it does seem like the returned string for a form is strange, like form_. I guess I could truncate that? Also, TypeName returns logical responses from code, but from the immediate window it keeps returning "empty" for any of the DAO objects I use. And it doesn't seem to be able to test any form that isn't currently open? – MBY Mar 07 '14 at 17:28
  • Right, I wondered whether `Form_Form3` would be a concern. You would face a similar issue with a report object: `Report_rptFoo`. Perhaps you could do `If TypeName(object) Like "Form_*" Then` Or create a custom function which uses `TypeOf` to first check whether the object is a form or report, and otherwise use `TypeName`. – HansUp Mar 07 '14 at 17:35
  • I don't get "empty" for a recordset which is currently open. So I don't understand that one. However, I also should disclose this is new territory for me. – HansUp Mar 07 '14 at 17:39
  • I puzzled by the issue of identifying a form as a form when it's not open. In that case do you have the form name as a string value and you want to determine that string represents the name of a form? – HansUp Mar 07 '14 at 18:06
  • Yes, exactly. I want to be able to retrieve information from a form without necessarily opening it. – MBY Mar 07 '14 at 18:48
  • When you give Access a string, it has no way to know that string contains the name of a form. Check whether it exists in `CurrentProject.AllForms` if you want to know whether it is the name of a form. Check `CurrentProject.AllReports` to see whether it is the name of a report. And so forth ... – HansUp Mar 07 '14 at 18:51
  • I have a function to check if an object exists in the DB by name. It loops over the TableDefs, QueryDefs, Forms, and Reports and compares the parameter name to the name of each object. But I need the function to receive the type of the object so it knows which collection to check (you can have a table, form, etc. with the same name as long as the type is different). – MBY Mar 07 '14 at 18:58
  • Sorry, but I suspect I'm missing something which may be obvious to you and everyone else. When you find your target name in the forms collection, you know that target name is a form. So use that same collection loop/searching strategy to determine the object type of the target name. Where is the problem? – HansUp Mar 07 '14 at 19:02
  • " When you find your target name in the forms collection, you know that target name is a form." Not really. As I alluded to, you can have a table, a form, a query, and a report that are all named "Hans." So you know that there _is_ a form with that name if you find it in that collection, but there may _also_ be another object type with the same name. So I want to be able to be explicit. – MBY Mar 07 '14 at 19:10
  • I tried to invite you to a chat but the system says you don't have enough rep. So I will just withdraw from this because it's now clear to me that I don't have enough of a clue to actually help you. Good luck. – HansUp Mar 07 '14 at 19:14
0

Apparently there is already an enum for Access object types: the acObjectType enumeration, described on MSDN. This is used, for example, in the application.sethiddenattribute method. Unfortunately I only found this after creating my own enum for a different procedure I was writing.

MBY
  • 113
  • 1
  • 1
  • 6