Form to display highest ID records

[mounty76]

Hi All,

I have a button that opens another form, I want it to display the record with the highest ID (Autonumber). This worked fine using DoCmd.GoToRecord , , acLast However I have changed the sort in the qry the form gets its data from, as such now the ID's are not in numerical order so the above just shows the last record in the query and not the newest (highest ID).

I've tried to use MAX(ID) and acGoTo but am struggling. Any ideas....I'm sure this is simple but I just can't figure it out

Cheers

 

[GPGeorge]

One of the problems with using AutoNumbers for anything other than surrogate Primary Keys is that there is no guarantee they will be sequential with no gaps, nor that the highest value for an AutoNumber will necessarily be the most recent in the sense you may want it here. In a normal situation, the values of AutoNumbers do increase as new records are entered, but again, that is not always guaranteed.

Back to that in a bit.

Last() does indeed return the record at the end of a current recordset, regardless of how that recordset is sorted. If the sorting is on any other field, such as a date (Hm, dates with times might be a good indication of the "Last" record entered....) or a person's name, or the name of a product, then whatever sorting does to the sequence of AutoNumbers can put any one of several different values for it in "the last" position. Not useful in most cases where we're looking for the most recent, or largest or smallest or initial value of something.

So, back the values of AutoNumbers, in addition to the problem just described, it is possible to enter records out of sequence in a data entry operation. To take a really basic example. A data entry person receives a stack of Orders (and let's say the organization is still receiving orders on paper) to add. The stack is probably more or less in the sequence in which the organization received them, but again, no guarantees. If the data entry person starts with the order on the top of the stack and works to the bottom, chases are pretty good that the dates on the orders will NOT be sequential, so an order for the 1st of December might be entered into the relational database application after several orders for the 10th of December. And, assuming the AutoNumbers do increase as normally they do, the "Last" or largest value for that ID is now for an earlier order.

If you are basing the process in which you look for that value on the assumption that the newest order will have the highest ID, you're going to make a mistake in this situation.

A long winded explanation as to why it's typically safer to decide which real world value determines the proper sort order for a given process. And that is not the AutoNumber, generally speaking.

Max() would be alternative you probably want instead of Last(). We have no clue as to why you are struggling with it, though. Perhaps explain in behavioral terms what does happen when you use Max().

And finally, all of the above is based on the assumption that we're talking about interface interactions with data. If you are in VBA and trying to obtain a Primary Key value for the most recently added record in a table, that's a different kind of problem, and would be addressed differently.

 

[mounty76]

The qry sorts firstly by department and then secondly by a job number (this is auto generated by concatenating two fields together (a prefix and the autoID). This is good for how I want to present the data in another form, however in another form I want it to open to just show the last record entered with has the highest ID. I guess I could create another query that sorts the data using the ID but just wondered if there was VBA I could use to find this record using the DoCmd.GoToRecord function

 

[Pat Hartman]

If you want the highest autonumber value, which should be the most recent record entered, although not necessarily the logical last record, then:
1. Use DMax() to find the ID you are looking for
2. Use the WHERE argument of the OpenForm method and supply the value you found with the DMax() as the ID you are looking for.

It is NOT necessary or desirable to use the GoToRecord method. Best to just open the form to the record you want to view. You may also need a search form if you want to review old records. That lets you use the database engine to reduce the records you retrieve. When you have only a few hundred or a few thousand rows in the table, it doesn't matter much how you access them. Once the recordset gets larger, you need to think about efficient retrieval of data and limiting your retrieval to the record you want to edit rather than all records. Most applications never need to upsize to SQL Server but those that do, need logic changes when you have built them using inefficient methods that only work well with local databases.

 

[mounty76]

Thanks Pat, that makes sense. This DB will only have a few thousand rows at the very most, more likely less than one thousand so OpenForm method should work well

 

[Pat Hartman]

You're welcome