Move to last record

[GHawkins]

I am supporting this database created by someone else, who is no longer with
the company, so I will try to explain my problem the best I can! I am by no
means a VBA expert, so even trying to understand everything she did is
somewhat confusing.

I am using Access 2003 with a SQL 2005 (newly upgraded) back end. New
transactions are added to the database using a "New Transactions" form. The
data entered here is put into the main form ("Transactions") that includes a
subform ("Transactions_Detail"). When I add a new transaction, I want the
newly added transaction to come up. Sometimes it does, but sometimes the
first transaction is active.

I have the following code:
---------------------------------------
Private Sub cmdSaveClose_Click()
Dim response As Integer
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim frmRst As Recordset

On Error GoTo SaveErr

If (Month(Acct_Period) <> Month(Now())) Or (Year(Acct_Period) <>
Year(Now())) Then
'Dim response As Integer
response = MsgBox("Warning: You entered a non-current month date in
the billing period field. Save anyway?", _
vbInformation + vbYesNo, "Possible Entry Error")
If response = vbNo Then
Me.Acct_Period.SetFocus
Exit Sub
End If
End If
If Me.Check_Amount = "" Or IsNull(Me.Check_Amount) _
Or Me.Acct_Period = "" Or Me.Category_ID = "" Or
IsNull(Me.Category_ID) Then
MsgBox "Please complete all required fields."
Exit Sub
End If

If InsertTrans Then
strSQL = "select trans_id from dbo.[transaction] " _
& "where check_amount = " & Me.Check_Amount & " " _
& "and acct_period = '" & Me.Acct_Period & "' " _
& "and category_id = '" & Me.Category_ID & "'"
Set con = New ADODB.Connection
Set con = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open strSQL, con
rst.MoveLast

Dim frm As Form
Set frm = Forms!frmtransaction
Set frmRst = frm.RecordsetClone
frmRst.Find ("trans_id = " & rst!Trans_ID)
On Error Resume Next
If Err.Number = 3021 Then
Do Until Err.Number = 0
Err.Clear
frm.Requery
Loop
Else
If Err.Number <> 0 Then
GoTo SaveErr
End If
End If
frm.Bookmark = frmRst.Bookmark
frm.frmTransactionSubform.Form.strDefaultSubCategory = ""
frm.frmTransactionSubform.Form.intDefaultSubCatDetail = 0
DoCmd.Close acForm, Me.Name

End If
SaveExit:
On Error Resume Next
Set frm = Nothing
rst.Close
Set rst = Nothing
DoCmd.Close acForm, Me.Name
Exit Sub
SaveErr:
MsgBox Err.Number & " " & Err.Description
Resume SaveExit

End Sub
-------------------------

When I step through the code, it appears that the last record will display
depending on where I put the stop. For example, if I put a stop on or
anywhere before the "frm.Rst.Find ("trans_id = " & rst!Trans_ID)" line, the
newly added record will be active when I go completely through the code.

However, if I put the stop anywhere after the above line, my frmRst.Bookmark
value gets lost. I don't know if this is just a coincidence, but I can enter
the exact same data into the "New Transactions" form time and time again (the
primary key being a generic autonumber) and where I begin to step through the
code determines if the first or last record appears when it's all said and
done. I can't figure out where the bookmark value is lost, assuming that's
what is causing my problem.

Thanks in advance for any help!