Object List Module


I found the following question: http://www.experts-exchange.com/Databases/MS_Access/Q_20155738.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!

LVL 9
tonydemarcoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dds110Commented:
Try replacing dbOpenDynaset with the number 2 or 3
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(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.
0
tonydemarcoAuthor Commented:

dds110, - 2 = same  3= invalid argument

jimhorn, - References already set
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(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)
0
tonydemarcoAuthor Commented:
jimhorn,

Microsoft ActiveX Data Objects 2.1 Library - checked

Made changes to code - same error.
0
tonydemarcoAuthor Commented:
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)
0
dds110Commented:
Try taking out the 2nd rs parameter alltogether and see what that gives ya

i.e.

Set rs = db.OpenRecordset("tblObjectSpecs")
0
jjafferrCommented:

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
0
jjafferrCommented:

The problem is with your Dim, you didn't DAO all the line, so here is the correction, which worked for me:

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


jaffer
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tonydemarcoAuthor Commented:
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?
0
jjafferrCommented:
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
0
tonydemarcoAuthor Commented:
Excellent!

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

jaffer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.