Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

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!

0
tonydemarco
Asked:
tonydemarco
  • 5
  • 4
  • 2
  • +1
1 Solution
 
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now