asked on

Object List Module

I found the following question: https://www.experts-exchange.com/questions/20155738/Enumerate-list-all-objects-in-which-a-table-is-used.html?query=<span%20class=

Tried to use the code but I get a Run Time Error '13': Type mismatch.

When I debug I get the code line: Set rs = db.OpenRecordset("tblObjectSpecs", dbOpenDynaset)

I am using MS ASSESS 2002 on Win2k

Help!

dds110

Try replacing dbOpenDynaset with the number 2 or 3

Jim Horn

(Wild guess) In any code module, go to Tools menu:References, set a reference to 'Microsoft Data Access Objects Library' a.k.a. DAO, and run again.

tonydemarco

ASKER

dds110, - 2 = same 3= invalid argument

jimhorn, - References already set

Jim Horn

(Wild guess) Eyeball your references again and see if Microsoft ActiveX Data Objets, a.k.a. ADO is checked.

If it is, then you will want to differentiate your objects between DAO and ADO, as they both have a recordset object:

Dim db as DAO.Database
Set db = CurrentDb()

Dim rs as DAO.Recordset
Set rs = db.OpenRecordset (blah blah blah)

tonydemarco

ASKER

jimhorn,

Microsoft ActiveX Data Objects 2.1 Library - checked

Made changes to code - same error.

tonydemarco

ASKER

cont'd

Public Sub ListObjects()
Dim db As DAO.Database, qdf As QueryDef, qdf2 As QueryDef, tdf As TableDef, rpt As Report, frm As Form, ctr As Container
Dim doc As Document
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblObjectSpecs", dbOpenDynaset)

dds110

Try taking out the 2nd rs parameter alltogether and see what that gives ya

i.e.

Set rs = db.OpenRecordset("tblObjectSpecs")

jjafferr

try this:

Set db = CurrentDb() '<== Delete the parantheses
Set rs = db.OpenRecordset("tblObjectSpecs", dbOpenDynaset)

OR
Set db = CurrentDb() '<== Delete this line, and modify the next line
Set rs = CurrentDb.OpenRecordset("tblObjectSpecs", dbOpenDynaset)

jaffer

ASKER CERTIFIED SOLUTION

jjafferr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tonydemarco

ASKER

Thanks jjafferr,

That worked!

Can you tell me why the UsedbyName in the tblObjectSpecs looks like:

~sq_dprv_3BInvoiceJournal_rpt1~sq_dprv_3BStatement_subrpt

Where ~sq_d is the separator.

Can this be changed in the code to something more friendly?

jjafferr

Hi Tony

You are welcome, and
Thanks for the points and the grade.

Looking at my Table, I have the following, with my "assumed" interperetation:
~sq_f : Form
~sq_c : ComboBox/Listbox
~sq_d : I think, ComboBox/Listbox/SubForm/SubReport which's Row Source is the same Table as the Record Source of the Form/Report
~sq_r : Report

so this:
~sq_dprv_3BInvoiceJournal_rpt1~sq_dprv_3BStatement_subrpt
means:
the Form/Report prv_3BInvoiceJournal_rpt1 contains a ComboBox/Listbox/SubForm/SubReport called prv_3BStatement_subrpt which uses the same Row Source/Record Source of the main Form/Report.

Can this be captured from code and changed?
Yes, this way:

every place you find
rs!QueryName =
you will have to test it's result and assign the value accodingly, for example:
'rs!QueryName = rpt.RecordSource
Telda1=InStr(rpt.RecordSource, "~")
Telda2=InStr(2, rpt.RecordSource, "~")

if Telda1>0 and left( rpt.RecordSource ,5)="~sq_d" then
rs!QueryName = replace(rpt.RecordSource,"~sq_d", "This is a Data: ")
endif
if Telda1>0 and Telda2>0 then
...
endif

and so on

jaffer

tonydemarco

ASKER

Excellent!

Thanks

jjafferr

Hey Tony,
I would be interested to see what you come with, if you don't mind :o)

jaffer