subform recordset

[alxw3 via AccessMonster.com]

my subform has a recordset. On each record selection (form OnCurrent) how to
get "field1" value from the first record and last the record.

 

[BruceM]

You could use DMax and DMin. How you do it depends on what determines the
first and last records. In general:

=DMax("[Field1]","[TableName]","[ID] = " & [ID]

Where ID is the name of the linking field.

If Field1 is the field that detemines first and last:

=Max([Field1])
=Min([Field1])

All of these expressions are written as the control source of an unbound
text box, but you could use them in a query or in VBA, with modified syntax.

 

[alxw3 via AccessMonster.com]

is this quickest way. How about using the subform recordset itself?.

You could use DMax and DMin. How you do it depends on what determines the
first and last records. In general:

=DMax("[Field1]","[TableName]","[ID] = " & [ID]

Where ID is the name of the linking field.

If Field1 is the field that detemines first and last:

=Max([Field1])
=Min([Field1])

All of these expressions are written as the control source of an unbound
text box, but you could use them in a query or in VBA, with modified syntax.

my subform has a recordset. On each record selection (form OnCurrent) how
to
get "field1" value from the first record and last the record.

 

[BruceM]

I was inaccurate in the first post. The DMax example assumes Field1 is the
field by which you are ordering the records, in which case you could just
use Max and Min. If Field1 is not the ordering field you may have to do
something such as use DLookup with DMax as part of the Where condition, but
nesting domain aggragate functions is not such a good idea from the
performance standpoint.

Better, probably, is to use a subquery. More information here:

http://allenbrowne.com/subquery-01.html

is this quickest way. How about using the subform recordset itself?.

You could use DMax and DMin. How you do it depends on what determines the
first and last records. In general:

=DMax("[Field1]","[TableName]","[ID] = " & [ID]

Where ID is the name of the linking field.

If Field1 is the field that detemines first and last:

=Max([Field1])
=Min([Field1])

All of these expressions are written as the control source of an unbound
text box, but you could use them in a query or in VBA, with modified
syntax.

my subform has a recordset. On each record selection (form OnCurrent)
how
to
get "field1" value from the first record and last the record.