recordcount property of a query

[kobooky]

The following code gives me a runtime error '3001' Invalid argument.
Debuging points to the red line.
All I want is that rec will be equal to the record count of query "Qlegendorder"
What is the mistake with the code?
or
What shall write instead of the red line?

Dim strSQL, strIn As String
Dim i, rec As Integer


strSQL = "TRANSFORM Max(tgraph.Time) AS MaxOfTime SELECT tgraph.Numbers FROM Legend INNER JOIN tgraph ON Legend.[Legend name] = tgraph.Name GROUP BY tgraph.Numbers, tgraph.Time ORDER BY tgraph.Numbers, tgraph.Time DESC PIVOT Legend.Legend"

DoCmd.OpenQuery "Qlegendorder", acViewNormal, acEdit
DoCmd.GoToRecord acQuery, "Qlegendorder", acFirst
rec = CurrentDb.QueryDefs("Qlegendorder").OpenRecordset(Dynaset).RecordCount
For i = 1 To rec
strIn = strIn & " '" & Legend & "'"
DoCmd.GoToRecord acQuery, "Qlegendorder", acNext
Next

 

[dhookom]

kobooky,
What are you attempting to do? You can't specify only Dyanset in the OpenRecordset() (AFAIK).

Legend would have no value and should generate an error unless it is a control on the current form or is a public variable. If you hope to pull the value from a recordset, you need to create a recordset and specify it.

Also, do you realize that strSQL and i are being Dim'd as Variants? You would need to code something like:

Dim strSQL As String, strIn As String
Dim i As Integer, rec As Integer

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kobooky]

Thanks, dhookom
I know that specifying only dynaset in OpenRecordset is not enough but I'm strugling with the syntax for quite a while and what I attached to the message was only my last trial.
I can't get this line to work and all I want is for rec to hold the recorcount of the query Qlegendorder.
what shall be the code?
I'm probably not in the right course...

 

[Eupher]

kobooky,

Have a look at the DCount() function.

Ken S.