Problem Opening Query in VBA

[HiGu]

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

Private Sub cboLocate_AfterUpdate()
Dim rst As DAO.Recordset, db As Database
Dim strCriteria, SQL As String
   strCriteria = "[A_LOCATION]=" & cboLocate
   Set rst = Me.RecordsetClone
   'rst.FindFirst (strCriteria = "[A_LOCATE]=" & cboLocate)
   Set db = CurrentDb
   Set rst = db.OpenRecordset("qryMIMATRIX", dbOpenDynaset, dbReadOnly, dbOptimistic)
      If rst.NoMatch Then
      MsgBox "No entry found"
   Else
      Me.FilterOn = False
    Me.Filter = strCriteria
    Me.FilterOn = True
   End If
End Sub

The error points to the code:

Set rst = db.OpenRecordset("qryMIMATRIX", dbOpenDynaset, dbReadOnly, dbOptimistic)
 

If I simply use

Set rst = db.OpenRecordset("qryMIMATRIX")
 

I am asked to enter a parameter value.

qryMIMATRIX is a query.I also tried using

Sql="select * from qryMIMATRIX"
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?

[NeoPa]

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.

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.

[HiGu]

Here's the query:

TRANSFORM First(qryMIMATRIXSELECT!A_PLANDATE) AS [FirstOfvolgende inspectie]
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
FROM qryMIMATRIXSELECT
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
ORDER BY qryMIMATRIXSELECT.A_PLANDATE
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");
 

[NeoPa]

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?

[HiGu]

Tried this:

Set rst = db.QueryDefs("qryMIMATRIX").OpenRecordset(dbOpenSnapshot, , dbReadOnly)
 

and

  Set rst = db.OpenRecordset(SQL, dbOpenSnapshot, , dbReadOnly)

Again error 3001 saying invalid object.

[NeoPa]

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?