DoCmd Openquery qryMaxOfEvtInputNumber doesn't recognise the query

  • Thread starter Thread starter Hugh self taught
  • Start date Start date
H

Hugh self taught

I have the following query which works perfectly

SELECT T.EvtInput_Idx, T.EvtNumber, T.EvtStructID
FROM tblEvtInput AS T
WHERE (((T.EvtInput_Idx) In (SELECT TOP 1 [EvtInput_Idx] FROM tblEvtInput
WHERE EvtNumber=T.EvtNumber AND Forms!frmEvtInput!EvtNumber = T.EvtNumber
ORDER BY [EvtInput_Idx] DESC)))

When I call it from an "On Change" event

DoCmd.OpenQuery qryMaxOfEvtInputNumber
Forms!frmEvtInput!EvtStructID = T.EvtStructID

I get runtime error 2496
"The action or method requires a Query Name argument"

Anyone got any ideas why?
 
Hugh self taught said:
I have the following query which works perfectly

SELECT T.EvtInput_Idx, T.EvtNumber, T.EvtStructID
FROM tblEvtInput AS T
WHERE (((T.EvtInput_Idx) In (SELECT TOP 1 [EvtInput_Idx] FROM tblEvtInput
WHERE EvtNumber=T.EvtNumber AND Forms!frmEvtInput!EvtNumber = T.EvtNumber
ORDER BY [EvtInput_Idx] DESC)))

When I call it from an "On Change" event

DoCmd.OpenQuery qryMaxOfEvtInputNumber
Forms!frmEvtInput!EvtStructID = T.EvtStructID

I get runtime error 2496
"The action or method requires a Query Name argument"

Anyone got any ideas why?
Click to expand...

The name of your query needs to be surrounded by quotes:

DoCmd.OpenQuery "qryMaxOfEvtInputNumber"
 
Hugh self taught said:
I have the following query which works perfectly

SELECT T.EvtInput_Idx, T.EvtNumber, T.EvtStructID
FROM tblEvtInput AS T
WHERE (((T.EvtInput_Idx) In (SELECT TOP 1 [EvtInput_Idx] FROM tblEvtInput
WHERE EvtNumber=T.EvtNumber AND Forms!frmEvtInput!EvtNumber = T.EvtNumber
ORDER BY [EvtInput_Idx] DESC)))

When I call it from an "On Change" event

DoCmd.OpenQuery qryMaxOfEvtInputNumber
Forms!frmEvtInput!EvtStructID = T.EvtStructID

I get runtime error 2496
"The action or method requires a Query Name argument"

Anyone got any ideas why?
Click to expand...


You would need to enclose the name of the query in quotes. But you can't
extract data from a query that way; DoCmd.OpenQuery will open the query as
a datasheet (unless it's an action query), and you won't be able to retrieve
the data values from it that way.

For this purpose, you can use DLookup to execute the query and retrieve a
specific named field value:

Forms!frmEvtInput!EvtStructID = _
DLookup("EvtStructID", "qryMaxOfEvtInputNumber")

If you want access to the other fields from the query at the same time, you
would do better to open a recordset on it, but if you just want one field,
DLookup is the simplest way to go.
 
You guys are sooooo clever .... Thanks

I implemented the Dlookup & viola!! Everything works

Dirk Goldgar said:
Hugh self taught said:
I have the following query which works perfectly

SELECT T.EvtInput_Idx, T.EvtNumber, T.EvtStructID
FROM tblEvtInput AS T
WHERE (((T.EvtInput_Idx) In (SELECT TOP 1 [EvtInput_Idx] FROM tblEvtInput
WHERE EvtNumber=T.EvtNumber AND Forms!frmEvtInput!EvtNumber = T.EvtNumber
ORDER BY [EvtInput_Idx] DESC)))

When I call it from an "On Change" event

DoCmd.OpenQuery qryMaxOfEvtInputNumber
Forms!frmEvtInput!EvtStructID = T.EvtStructID

I get runtime error 2496
"The action or method requires a Query Name argument"

Anyone got any ideas why?
Click to expand...


You would need to enclose the name of the query in quotes. But you can't
extract data from a query that way; DoCmd.OpenQuery will open the query as
a datasheet (unless it's an action query), and you won't be able to retrieve
the data values from it that way.

For this purpose, you can use DLookup to execute the query and retrieve a
specific named field value:

Forms!frmEvtInput!EvtStructID = _
DLookup("EvtStructID", "qryMaxOfEvtInputNumber")

If you want access to the other fields from the query at the same time, you
would do better to open a recordset on it, but if you just want one field,
DLookup is the simplest way to go.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top