go to last record on a form not working

[PhilipMiddleton]

I have a question about some strange behavior in my access program.
Please note, I have not done any real programming in many years, so forgive me if I get some terminology wrong. My database was initially biult with Access 97. Migrated to 2000 and currently running on Access 2003. I have looked at migrating to the latest office but it looks like a rewrite. I simply dont have time for that, and apart from this small bug, it does exactly what I need it to do.
My issue is this.
I'm opening a form attached to a dataset (table). All my code works spot on except one bit. If I open the form with OpenArgs "NewCustomer" the form runs a "DoCmd.GotoRecord , , acLast" then reads and stores the customer number (Cust_ID / Key Field), then goes to a new record and fills in the new customer number + 1 (Code sample below).
Problem is, it does not work (now).
If I find an older copy of the code, it works as expected - for a while. Then it stops working properly and the "DoCmd.GotoRecord , , acLast" line only goes about 1/2 way through the recordset. Interestingly enough, always to the same record.
???


Code sample:


Private Sub Form_Open(Cancel As Integer)


Dim temp_ID As String 'Temp ID can be last Cust_ID or Current Cust_ID
temp_ID = ""


Select Case Me.OpenArgs
Case "NewCustomer": 'Its creating a new customer (New ID)
DoCmd.GoToRecord , , acLast
temp_ID = Me!Cust_ID
DoCmd.GoToRecord , , acNewRec 'Go to a new record.
Me!Cust_ID = Trim(Str(Val(temp_ID + 1))) ' <Trim and Str Probably unnessesary but Cust_ID is stored as a String
Case Else: 'for now, any other (or no openargs) just go to first record
DoCmd.GoToRecord , , acFirst
End Select
Me!Misc.SetFocus
End Sub


I've even tried changing the forms RecordSource" to
SELECT Clients.*
FROM Clients
ORDER BY Clients.Cust_ID;
But my understanding it that trying to order it here makes no difference.


Any ideas? Doing a compact and repair makes no difference. Once its screwed, its screwed.
I've gone back to the earliest version I currently have (thinking it may be a corruption in the FrontEnd) and re-coded the few things I've changed in the last couple of years, but eventually the problem still crops back up.
Happens on the 2 x PC's that use my Database (Both Windows 7 with Office 2003)

Addendum:
I only JUST finished writing this and discovered something that has fix my issue (for now).
I opened the table, manually created a new record (started typing in crap into the new record).
I then deleted the crap record I had just entered.
Bingo, now my GoTo Last Record command works perfectly again.
???
I still dont understand this but I'll take it as a win.
Any ideas guys? Anyone seen anything like this before?
Corrupted database?
My Database is a split database (Front end with tables linked to the Back end or records file)

[davegri]

Instead of

DoCmd.GoToRecord , , acLast
temp_ID = Me!Cust_ID

Try:

temp_ID = dMax("Clients","Cust_ID")

[PhilipMiddleton]

Thanks. I'll look at doing this now.