Docmd.GoToRecord Bizzare Behaviour

[robdunfey]

The following code is placed behind a cmd btn that when clicked adds a record to a table named 'line'. It starts by inserting a line, the requerys the table, then sets the current record to the last one in the 'line' table, the it resets a couple of global variables that are used to keep track of everything, then it runs a sub that updates a few buttons depending on the value of the global variables.

THE PROBLEM: It works fine. Except SOMETIMES when adding the second record to the table, when it runs the line DoCmd.GoTo ,,acLast it sets the current record to the first record in the table, despite a second being added. I could understand this if it happed all the time, but it only happens sometimes.

ANY ADVICE/SUGGESTIONS MUCH APPRECIATED.


'Insert the next row to the line table
DoCmd.SetWarnings False
DoCmd.RunSQL ("insert into ENG_LINE ( [SITE_ID],[LINE_CNT_ID]) values ('" & Me![SITE_ID] & "'," & NoLines + 1 & &quot&quot
DoCmd.SetWarnings True

' Requry the table
Me.Requery

' Move to the last record
DoCmd.GoToRecord , , acLast

' Set the line variables
NoLines = NoLines + 1
CurrentLine = NoLines

' Update the button status
Call UpdateButtons
Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click