By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,232 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

Problem Opening Query in VBA

P: 99
I need to filter a form using the selection made by user from a dropdown.I have used the following code which throws an error: invalid argument
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboLocate_AfterUpdate()
  2. Dim rst As DAO.Recordset, db As Database
  3. Dim strCriteria, SQL As String
  4.    strCriteria = "[A_LOCATION]=" & cboLocate
  5.    Set rst = Me.RecordsetClone
  6.    'rst.FindFirst (strCriteria = "[A_LOCATE]=" & cboLocate)
  7.    Set db = CurrentDb
  8.    Set rst = db.OpenRecordset("qryMIMATRIX", dbOpenDynaset, dbReadOnly, dbOptimistic)
  9.       If rst.NoMatch Then
  10.       MsgBox "No entry found"
  11.    Else
  12.       Me.FilterOn = False
  13.     Me.Filter = strCriteria
  14.     Me.FilterOn = True
  15.    End If
  16. End Sub
The error points to the code:
Expand|Select|Wrap|Line Numbers
  1. Set rst = db.OpenRecordset("qryMIMATRIX", dbOpenDynaset, dbReadOnly, dbOptimistic)
  2.  
If I simply use
Expand|Select|Wrap|Line Numbers
  1. Set rst = db.OpenRecordset("qryMIMATRIX")
  2.  
I am asked to enter a parameter value.

qryMIMATRIX is a query.I also tried using
Expand|Select|Wrap|Line Numbers
  1. Sql="select * from qryMIMATRIX"
  2. Set rst=db.OpenRecordset(SQL)
If I do not use db.OpenRecordset() then no filtering happens.
I would like to know how I can make this work?
Mar 16 '12 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,492
Line #8 appears perfectly correct as far as I can see. It may well be easier to use the other format to open a query (Below), but your VBA here seems fine.
Expand|Select|Wrap|Line Numbers
  1. Set rst = db.QueryDefs("qryMIMATRIX").OpenRecordset(Type:=, Options:=, LockEdit:=)
As your VBA seems ok, I would look at the query itself and see if that runs when opened normally.
Mar 16 '12 #2

P: 99
Here's the query:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM First(qryMIMATRIXSELECT!A_PLANDATE) AS [FirstOfvolgende inspectie]
  2. SELECT qryMIMATRIXSELECT.A_ID, qryMIMATRIXSELECT.A_PLANDATE, qryMIMATRIXSELECT.A_LOCATION, qryMIMATRIXSELECT.A_JOBNO, IIf(IsNull(qryMIMATRIXSELECT!A_PROJECTID),qryMIMATRIXSELECT!A_EQUIPDESCR,"**13M** " & qryMIMATRIXSELECT!A_EQUIPDESCR) AS exprA_EQUIPDESCR, qryMIMATRIXSELECT.A_NENGROUP, qryMIMATRIXSELECT.A_PRIORITY, qryMIMATRIXSELECT.A_MENO, qryMIMATRIXSELECT.A_DAYO
  3. FROM qryMIMATRIXSELECT
  4. GROUP BY qryMIMATRIXSELECT.A_ID, qryMIMATRIXSELECT.A_PLANDATE, qryMIMATRIXSELECT.A_LOCATION, qryMIMATRIXSELECT.A_JOBNO, IIf(IsNull(qryMIMATRIXSELECT!A_PROJECTID),qryMIMATRIXSELECT!A_EQUIPDESCR,"**13M** " & qryMIMATRIXSELECT!A_EQUIPDESCR), qryMIMATRIXSELECT.A_NENGROUP, qryMIMATRIXSELECT.A_PRIORITY, qryMIMATRIXSELECT.A_MENO, qryMIMATRIXSELECT.A_DAYO
  5. ORDER BY qryMIMATRIXSELECT.A_PLANDATE
  6. PIVOT qryMIMATRIXSELECT.periodes In ("0","2","4","6","8","10","12","14","16","18","20","22","24","26","28","30","32","34","36","38","40","42","44","46","48","50","52");
  7.  
Mar 17 '12 #3

NeoPa
Expert Mod 15k+
P: 31,492
I'm confused. Why would you post the SQL of the query? I can't test it for you (nor would I if I could - That's your responsibility).

I've already checked the VBA and I see nothing wrong with it. The fact that the query is a Cross-Tab is important information that would have been a good idea to include in the question, but doesn't change the understanding that the problem is with the query.

Actually, one thought occurs to me :
Although I'm pretty sure Help has nothing to say on the matter, the parameters dbReadOnly and dbOptimistic certainly make little sense together. They may be disallowed without this being documented so I would try passing parameters that make sense. Try leaving the Options parameter unset and use dbReadOnly for LockEdit instead. Also, if it's read/only, why are you using a Dynaset instead of a SnapShot?
Mar 17 '12 #4

P: 99
Tried this:
Expand|Select|Wrap|Line Numbers
  1. Set rst = db.QueryDefs("qryMIMATRIX").OpenRecordset(dbOpenSnapshot, , dbReadOnly)
  2.  
and
Expand|Select|Wrap|Line Numbers
  1.   Set rst = db.OpenRecordset(SQL, dbOpenSnapshot, , dbReadOnly)
Again error 3001 saying invalid object.
Mar 19 '12 #5

NeoPa
Expert Mod 15k+
P: 31,492
NeoPa:
As your VBA seems ok, I would look at the query itself and see if that runs when opened normally.
It doesn't seem to be a VBA problem then.

What did you find when you tested the query manually as suggested in post #2?
Mar 19 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.