Hi,

I am trying to change the value of a field (well a few fields once I get it to work for one!) in a table.

I am trying to update the last record in a table, but cannot get any code to work in VBA for this and cannot make an update query select just the last record in the table to do it that way either!

The VBA I am using is:

Dim db As Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Inventory Transactions", dbOpenDynaset)

With rst
.MoveLast
.Edit
![UnitsOrdered] = (Me.UnitsOrdered - Me.Qty_Passed_Insp)
.Update
End With
rst.Close
db.Close

Units ordered doesn't change though!


For the update query I am trying the following criteria for the transactionID field:

=max( [Inventory Transactions]![TransactionID] )

But this returns the error message:

"Cannot have aggregate finction in WHERE clause ([Inventory Transactions].TransactionID = Max([Inventory Transactions]![TransactionID]))"

Any suggestions?

Thanks in advance!

It might actually be working. When testing for EOF after the MoveLast Command it returns a value of false. Any idea why MoveLast doesn't go to the last record?

Also, how can I find out exactly what record I am on?

Why are you doing something to the table when the information is allready on the screen???

Try doing simply:
Me.UnitsOrdered = (Me.UnitsOrdered - Me.Qty_Passed_Insp)

Instead of all that....

P.S. If you are going to be using "DAO." then use it proper like....
You should not only use it for Recordset, but also on Database

Regards

I am updating another record based on information on the screen for the current record.

It's a goods in table and am creating a record based on outstanding quantities of an order.

The calculation is working perfectly it seems, just not on the right record!

Movelast doesn't seem to be moving to the last record, but always the same one. No idea why though at present.

Ok. THink I've solved it now. If I index the table - There was no Primary key! then the moveLast works. Now I have to sort out the 60 odd duplicates of the field that needs to be the Primary key.

My predecessor was somewhat crap and I have wasted several days finding and fixing all his fuckups!!!!

Cheers for helping anyway :)

It probably dont need to be PK, just indexed or open the recordset with an order by clause

Set rst = db.OpenRecordset("Select * from [Inventory Transactions] Order by {your field] asc/desc", dbOpenDynaset)

Note: one should not use spaces and such in tablenames.

Regards

The point is that a table is an unordered recordset. You need, as you've realised, some way of imposing an order. This could either be a key or as namlian says an explicit sort. Whatever you do, ensure that the order you rely on is one that is sound in terms of your business process.

My predecessor was somewhat crap and I have wasted several days finding and fixing all his fuckups!!!! You aren't making it any better.
1. Storing calculated values violates 3rd normal form and leads to data anomolies.
2. If you just added the record, why don't you know the key value? As has already been pointed out, Last isn't always what you think it is. The only way to be absolutely certain of updating the correct record in a multi-user environment is to find the record you want to update by using its UNIQUE primary key.

It is all part of the same process. the record is created and then modified in the same event and only the one user can do this. I just needed to be able to modify values of the last created record which is issued a unique autonumber that I have now set to be the primary key.

I know about spaces in table names etc. but renaming existing tables causes more trouble than it's worth right now!

but renaming existing tables causes more trouble than it's worth right now! Then don't complain about your predecessor since you are applying the same attitude and techniques.

the record is created and then modified in the same event Why? It seems a little wasteful to insert a record and then in the same sub turn around and retrieve it to change it.

FYI - if you haven't moved off the current record, its autonumber is still available and you may actually be able to edit it without specifically retrieving it again. I wouldn't know since I would never do this. In any event, there is no reason to use .MoveLast which won't necessarially even return the record you expect.