GoTo last record in Table rather than last record in Form using VBA

[wchelly]

With my form open, I want a button that when clicked will create a new record using VBA and save it in my table. Then I want it to GoTo the record (or last record in the table) in the current form.

The following code does all of the above, EXCEPT it goes to the last record of the form, rather than the last record of the table.

Here's my code.

Dim dbTransportationPlanning As DAO.Database
Dim rstCurrent_Shipments As DAO.Recordset
Dim Shipment_num As String
Set dbTransportationPlanning = CurrentDb
Set rstCurrent_Shipments = dbTransportationPlanning.OpenRecordset("Current_Shipments")
rstCurrent_Shipments.AddNew
rstCurrent_Shipments("Product").Value = "ONE"
rstCurrent_Shipments("TNProject").Value = "NEW"
rstCurrent_Shipments("Shipment_Num").Value = "NEW"
rstCurrent_Shipments("Departure_Date").Value = "1/1/1900"
rstCurrent_Shipments("Shipper").Value = "NEW."
rstCurrent_Shipments("Consignee").Value = "NEW"
DoCmd.SetWarnings False
DoCmd.GoToRecord , , acLast
DoCmd.OpenQuery ("Sharepoint_Append")
DoCmd.SetWarnings True

 

[John Big Booty]

Try;

Dim dbTransportationPlanning As DAO.Database
Dim rstCurrent_Shipments As DAO.Recordset
Dim Shipment_num As String
Set dbTransportationPlanning = CurrentDb
Set rstCurrent_Shipments = dbTransportationPlanning.OpenRecordset("Current_Sh ipments")
rstCurrent_Shipments.AddNew
rstCurrent_Shipments("Product").Value = "ONE"
rstCurrent_Shipments("TNProject").Value = "NEW"
rstCurrent_Shipments("Shipment_Num").Value = "NEW"
rstCurrent_Shipments("Departure_Date").Value = "1/1/1900"
rstCurrent_Shipments("Shipper").Value = "NEW."
rstCurrent_Shipments("Consignee").Value = "NEW"
[COLOR="Red"]rstCurrent_Shipments.Update
rstCurrent_Shipments.Bookmark = rstCurrent_Shipments.LastModified[/COLOR]
DoCmd.SetWarnings False
DoCmd.GoToRecord , , acLast
DoCmd.OpenQuery ("Sharepoint_Append")
DoCmd.SetWarnings True

 

[boblarson]

Tables do not store data in any particular order. So you need a query to apply a sort order.

As for getting the last record added there are a couple of options. I'm on the bus right now so it is hard to type up a good answer but you can set a DAO database object to add the record and then use @@identity to get the number and then use the FindFirst method of a recordset using a clone if the recordset.

It really depends.

 

[wchelly]

OK, I won't get back to this until next week so I'll try these both out then. Thanks!

 

[Solo712]

Tables do not store data in any particular order. So you need a query to apply a sort order.

As for getting the last record added there are a couple of options. I'm on the bus right now so it is hard to type up a good answer but you can set a DAO database object to add the record and then use @@identity to get the number and then use the FindFirst method of a recordset using a clone if the recordset.

It really depends.

Bob, isn't it the case that if you open the recordset as dynaset new records are appended to the end of table ?

Best,
Jiri

 

[boblarson]

Bob, isn't it the case that if you open the recordset as dynaset new records are appended to the end of table ?

Best,
Jiri

No, it doesn't mean that they are at the end of the table. They are IN the table but again, TABLES DO NOT STORE DATA IN ANY PARTICULAR ORDER. After compacting, it will order it by a primary key TO SOME EXTENT but again, it will not necessarily return any in any particular order unless you use a query with a sort order on the particular field you want it ordered by. In a recordset situation, it anything added to that recordset will be at the end of that recordset until it is recreated and then, after that, it may be different.

 

[Pat Hartman]

Naturally, there is a physically last record in a table in the human view of things if you could look at the bit strings written to the disk that represents your data, but as bob has already mentioned, there is no SQL command to retrieve this because being the physically last record of a table really has no meaning.

 

[Solo712]

No, it doesn't mean that they are at the end of the table. They are IN the table but again, TABLES DO NOT STORE DATA IN ANY PARTICULAR ORDER. After compacting, it will order it by a primary key TO SOME EXTENT but again, it will not necessarily return any in any particular order unless you use a query with a sort order on the particular field you want it ordered by. In a recordset situation, it anything added to that recordset will be at the end of that recordset until it is recreated and then, after that, it may be different.

Thanks, Bob. It really does not surprise me there is no specific order physically but I thought logically the records were ordered by the insertion sequence. Unfortunately, I am unable to find the reference at the moment that made me think that this was the case with Access. I'll let you comment if I find it.

Best,
Jiri

 

[Solo712]

Naturally, there is a physically last record in a table in the human view of things if you could look at the bit strings written to the disk that represents your data, but as bob has already mentioned, there is no SQL command to retrieve this because being the physically last record of a table really has no meaning.

I understand that, Pat. But I thought that there still was a "natural order" (or insertion order) that is deployed in Access (Jet version) that would return the records from the oldest to the newest if no ORDER BY or index was specified. Ie., if I specified "MoveLast" in a table-based DAO recordset (without index) i thought I would get the last inserted record into the table (not just the currently open rst).

I also know that this is sort of a legacy thing; Sybase and Oracle moved away from maintaining "natural order" some time back, in an effort to optimize disk sector searches. I really have no problem with it. I work with ordered data sets 99.99% of the time, so the natural order is not at all a hot topic. I am just curious.

Best,
Jiri

 

[Pat Hartman]

Access doesn't arbitrariarly sort records. It does insert them one after the other at least Jet did. I haven't read anything that goes into that level of detail for ACE. More sophisticated database engines use free space to fill in where deleted records were removed but Jet/ACE only recover the free space by a compact and repair. This issue is that if you can never predict with any certinty where physically a record might be, then there is no point in trying to retrieve records that way.

if I specified "MoveLast" in a table-based DAO recordset (without index) i thought I would get the last inserted record into the table (not just the currently open rst

You might get the last record inserted but you might get the last record updated. If a record was 100 bytes and you updated it so it is now 120 bytes, Access can't rewrite it where it was so it puts it at the end of its working area and will sort it back into place based on the table's PK when the database is next compacted.

"real" databases such as Sybase and Oracle have very sophisticated methods of reading/writing physical records. For example, they all keep freespace interspersed through out the tablespace so if they need to rewrite a record, they can use some of that freespace to regoganize a few local records and write the changed record plus several surrounding them back to the same place.

If you need to maintain entry order, you need to use a datestamp or a sequential autonumber.

 

[wchelly]

New records, in this database anyway, have an autonumber field, and a date/time stamp, so finding the last record shouldn't be difficult. How to use VBA to pull it up in an open form is my biggest hurdle. I havn't had a chance to try the above suggestions so I'll post back when I do. Thanks for ALL of the suggestions.

 

[wchelly]

Hrrmmm....after reading throught these thoughts and looking at what I have, I think what needs to happen is the following:

With "ADD NEW" button click on a form.
1/ New record generated in table.
2/ Close existing form.
3/ Open same form with query selecting the last record generated.

 

[Pat Hartman]

"Last" MUST have context. Do you mean the last record entered by a particular person? Do you mean the last record entered by a particular form? or to you mean last record entered by anyone? If you have an autonumber primary key, you can use DMax() to get the highest number autonumber and that will be the key of the record you want if you want the "last" record entered in the table. DLast() will actually get you a random record which may not be the most recently entered record since it relies on physical order of how Access retrieves a particular set of records which you cannot control and which could change from one execution to the next.

In summary - DLast() or any other last function has no use in a relational database since last changes with the situation.

 

[boblarson]

DLast() will actually get you a random record which may not be the most recently entered record since it relies on physical order of how Access retrieves a particular set of records which

Pat - that is just simply NOT true. It is LAST and FIRST which do the random selection. DFIRST and DLAST work quite well and I have tested them thoroughly. They will bring back the last record entered (or the first if DFirst) that was entered by any given criteria. So, if you want the actual last record entered by anyone you can use

=DLast("*", "TableNameHere")

Or you can filter based on any other criteria which exists in that table. The key is that it needs to have a field with data in it that you can filter on. So, if you store the person who created the record, you can retrieve the last record entered by that person.

It is a total misjustice to DFirst and DLast to state that they do not work. It is the LAST or FIRST as used in a QUERY that have the problem.

 

[Pat Hartman]

Bob.
Open the attached database and look at table1. What do you expect DLast("fieldID","table1") to return? 26? And yet it returns 4!

 

[boblarson]

How did you fill the table? I just added a record and it returns the correct value. I created a table3 and it works fine. Every test I have ever done has returned the correct value.

So you typed in the entries down to 26?

 

[boblarson]

I also deleted a record in the middle of the bunch, used an append query to add the autonumber value back in with some other text in the other field and it comes up just fine as the last one entered.

 

[wchelly]

I've recorded the person who created the record also, so it shouldn't be difficult to figure out the last record entered by a particular person.

 

[Pat Hartman]

When you modify a record and Access can't put the changed record back where the original record was, it puts it at the end of the tablespace and so it is out of order. I work with tables with lots of rows and so I see this fairly frequently. It also happens all the time when you are working with records not keyed by autonumbers. The modified record will get reordered back into key sequence if the table is compacted. The point is - just because DLast() works most of the time, it doesn't work all of the time and so cannot be relied on for this purpose. I don't understand what the reluctance to use DMax() is. DMax() ALWAYS returns the highest value PK if that is what you are using to determine the "last" record entered. If you need this functionality and you have a natural key rather than an autonumber, you can simulate it by adding a field that you populate with Now(). As long as you don't have really high insert rates, the Now() function should produce a unique value. You could add a unique index just to make sure but your code would have to trap the dup and reinsert without bothering the user because he won't have a clue what the problem is.

Logical records are grouped into physical records which are called "blocks" in the mainframe world. I'm having a senior moment and can't remember the PC term. But the point is that I/O is done at the physical record level. If you couldn't reproduce my error, you probably deleted and reinserted a row that fell into the final "block" in the tablespace. Try the same thing with an earlier record or in a bigger table and you will get the same results. You might have to force the record length to increase to force Access to place the record at the end of the tablespace rather than back where it got it.

 

[Galaxiom]

Logical records are grouped into physical records which are called "blocks" in the mainframe world. I'm having a senior moment and can't remember the PC term.

Would you be referring to Pages?