recordset clone

[Sirocco]

Can the dmax function be used with a recordset clone? I tried and it didn't
work - I'd rather know whether or not it can be done before I persevere.
Thanks in advance.

 

[Marshall Barton]

Can the dmax function be used with a recordset clone? I tried and it didn't
work - I'd rather know whether or not it can be done before I persevere.

No. The Domain Aggregate functions only operate on a table
or saved query.

 

[Sirocco]

Is there an equivalent technique that would work on a recordset clone? I
want to search for a max value in the set of records appearing in a
subform - since I already have the set of records I want to search, in the
subform, it seems there would be a way to search through this rather than
the entire underlying table.

Thanks in advance.

 

[Dirk Goldgar]

Is there an equivalent technique that would work on a recordset
clone? I want to search for a max value in the set of records
appearing in a subform - since I already have the set of records I
want to search, in the subform, it seems there would be a way to
search through this rather than the entire underlying table.

As far as I know, you'd have to loop through the records in the
recordset, like this:

Dim varMaxVal As Variant

With Me.RecordsetClone

If .RecordCount > 0 Then

.MoveFirst
varMaxVal = !MyField
.MoveNext

Do Until .EOF
If !MyField > varMaxVal Then
varMaxVal = !MyField
End If
.MoveNext
Loop

End If

End With

 

[Marshall Barton]

Is there an equivalent technique that would work on a recordset clone? I
want to search for a max value in the set of records appearing in a
subform - since I already have the set of records I want to search, in the
subform, it seems there would be a way to search through this rather than
the entire underlying table.

No. Either do the calculation yourself (possibly very slow)
along the lines of Dirk's reply, or open another recordset
based on a query that does the work for you.
--
Marsh
MVP [MS Access]

 

[Sirocco]

Open another recordset? And use the Dmax function with this "other"
recordset?

 

[Marshall Barton]

No. As I said earlier, DMax only works on table/query.

You've never explained what the recordset is based on, but
if it's a table or named query, then you could use DMax on
that:
DMax("field", "table")

Or what I was suggesting:

Set Rsmax = db.OpenRecordset("SELECT Max(field) FROM table")
maxval = rsmax!Field(0).Value
rsMax.Close : Set rsmax = Nothing