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.

 

[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

 

[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.

 

[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.

 

[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.

 

[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?