Selecting record in sub-form

[Alex]

Hi

Is there an easy way to select the last record entered into a table - the
record has an autonumber ID field if that helps.

Thanks

Alex

 

[Marshall Barton]

Is there an easy way to select the last record entered into a table - the
record has an autonumber ID field if that helps.

Not without a field that can be used to determine "last".
Since an autonumber primary key only guarentees uniqueness,
not any kind of sequence (they might be random numbers or,
in some cases, even text strings). you can not reliably use
that to do what you ask.

Usually, "last" means the most recent date/time, so your
table should have a field with the date/time that the record
was created. This is trivial to do by setting the field's
DefaultValue property to Now(). Now your form's record
source can be a query that sorts the records on the creation
date field and the last record in the form's recordset will
be the last record created.

 

[Alex]

Marsh

Thanks for that. I do have a date field which is automatically filled in
when the record is created and with the date in Short Date format.

If I set the default value of this field to Now(), wont that also add time
to the field. If 10 minutes after creating the record I want to run the
routine which tries to select the record where [Date Field] = now() surely
it wont select the record will it ( or am I missing something ((very
probably!)))

Alex

 

[Marshall Barton]

Thanks for that. I do have a date field which is automatically filled in
when the record is created and with the date in Short Date format.

If I set the default value of this field to Now(), wont that also add time
to the field. If 10 minutes after creating the record I want to run the
routine which tries to select the record where [Date Field] = now() surely
it wont select the record will it ( or am I missing something ((very
probably!)))

Not without a field that can be used to determine "last".
Since an autonumber primary key only guarentees uniqueness,
not any kind of sequence (they might be random numbers or,
in some cases, even text strings). you can not reliably use
that to do what you ask.

Usually, "last" means the most recent date/time, so your
table should have a field with the date/time that the record
was created. This is trivial to do by setting the field's
DefaultValue property to Now(). Now your form's record
source can be a query that sorts the records on the creation
date field and the last record in the form's recordset will
be the last record created.

 

[Alex]

Marsh - did you post a response, cannot see any new text

Alex

Thanks for that. I do have a date field which is automatically filled in
when the record is created and with the date in Short Date format.

If I set the default value of this field to Now(), wont that also add time
to the field. If 10 minutes after creating the record I want to run the
routine which tries to select the record where [Date Field] = now() surely
it wont select the record will it ( or am I missing something ((very
probably!)))

Alex wrote:
Is there an easy way to select the last record entered into a table - the
record has an autonumber ID field if that helps.

"Marshall Barton" wrote
Not without a field that can be used to determine "last".
Since an autonumber primary key only guarentees uniqueness,
not any kind of sequence (they might be random numbers or,
in some cases, even text strings). you can not reliably use
that to do what you ask.

Usually, "last" means the most recent date/time, so your
table should have a field with the date/time that the record
was created. This is trivial to do by setting the field's
DefaultValue property to Now(). Now your form's record
source can be a query that sorts the records on the creation
date field and the last record in the form's recordset will
be the last record created.

 

[Marshall Barton]

Thanks for that. I do have a date field which is automatically filled in
when the record is created and with the date in Short Date format.

If I set the default value of this field to Now(), wont that also add time
to the field. If 10 minutes after creating the record I want to run the
routine which tries to select the record where [Date Field] = now() surely
it wont select the record will it ( or am I missing something ((very
probably!)))

Yes, Now() includes the time, which you need in order to
distinguish two items created on the same day.

No, you can't search for a field = Now() because, as you
say, Now() is always the current clock, not what it was at
some time in the past.

To find the latest record you can look for the record that
has the Max date or use a query like:

SELECT TOP 1 *
FROM table
ORDER BY datetimefield DESC

I think we now agree on what "last" means, but you never did
say what you mean by "select" the last record so I don't
know else you may need.
--
Marsh
MVP [MS Access]